Since function uniqueSheetName uses some non-built VBA functions, they also must be included in your code for the function to work properly.
Otherwise the following error will occur: Compile error: Sub or Function not defined.
Required functions are listed below. You can get to each function's source code by clicking its name:
When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
'**********************************************************************************************************
' Name: uniqueSheetName
' Author: mielk | 2013-04-16
'
' Comment: Function checks if the given worksheet name would be unique in the specified
' Excel workbook. If not, the name is transformed to be unique by appending
' the number.
'
' Parameters:
' wkb Excel workbook for which the uniqueness of the specified worksheet name is
' being checked.
' name The name to be checked.
'
' Returns:
' String If there is no worksheet with such a name in the specified Excel workbook,
' the original value of [name] parameter is returned, unless it is not legal
' Excel sheet name (in such case it is also transformed to be legal sheet name by
' function legalSheetName()).
'
' If there is already a file with such a name in the given Excel workbook, the number
' in brackets is appended to the original name. If the name is too long afterward,
' the proper part of its original value is being cut.
'
' Example:
' -----------------------------------------------------------------------------------
' Let's assume we want to check if the worksheet name [data] would be unique in the
' specified Excel workbook.
' * If there is no such worksheet in this Excel file, the original name [data] will
' be returned without any modifications.
' * If worksheet [data] already exists in this Excel file, the function will return
' the original name with the number appended - data (1).
' * If such worksheet also already exists, the next number is appended and the
' function returns data (2) etc.
'
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-04-16 mielk Function created.
'**********************************************************************************************************
Public Function uniqueSheetName(wkb As Excel.Workbook, name As String) As String
Const METHOD_NAME As String = "uniqueSheetName"
Const MAX_LENGTH As Integer = 31
'------------------------------------------------------------------------------------------------------
Dim wks As Excel.Worksheet
Dim strTempName As String
Dim intIterator As Integer
Dim intCharsCounter As Integer
'------------------------------------------------------------------------------------------------------
'First check if the given name is legal sheet name. -------------------------------------------------|
strTempName = legalSheetName(name) '|
uniqueSheetName = strTempName '|
'----------------------------------------------------------------------------------------------------|
'Check if the book to be checked is not closed. -----------------------------------------------------|
If Not isBookValid(wkb) Then GoTo ObjectDisposedException '|
'----------------------------------------------------------------------------------------------------|
'Function tries to find a worksheet with such name in the specified Excel file. If this operation ---|
'raised an error, it means there is no such worksheet in the given file and the original name can '|
'be returned without any number being appended to it. '|
On Error GoTo UniqueName '|
Set wks = wkb.Worksheets(strTempName) '|
On Error GoTo 0 '|
'----------------------------------------------------------------------------------------------------|
'If the worksheet with such a name has been found, the original name has to be modified. ------------|
If Not wks Is Nothing Then '|
'|
'Repeat those operations as long as worksheet with the given name exists. -------------------| '|
Do '| '|
intIterator = intIterator + 1 '| '|
uniqueSheetName = strTempName & " (" & intIterator & ")" '| '|
'| '|
'Check if the name with the number appended is not too long -------------------------| '| '|
'(the maximum length is defined by constant MAX_LENGTH). '| '| '|
intCharsCounter = VBA.Len(uniqueSheetName) '| '| '|
If intCharsCounter > MAX_LENGTH Then '| '| '|
uniqueSheetName = VBA.Left$(strTempName, _
VBA.Len(strTempName) - intCharsCounter + MAX_LENGTH) & _
" (" & intIterator & ")" '| '| '|
End If '| '| '|
'------------------------------------------------------------------------------------| '| '|
'| '|
'| '|
'Function checks if the modified sheet name is unique in the given Excel workbook. --| '| '|
'If it is not, the name is modified again and the next number is appended instead '| '| '|
'of the current one, i.e. data (2) instead of data (1). '| '| '|
'This procedure is being repeated as long as the name is unique in the given '| '| '|
'Excel file. '| '| '|
On Error GoTo UniqueName '| '| '|
Set wks = wkb.Worksheets(uniqueSheetName) '| '| '|
On Error GoTo 0 '| '| '|
'------------------------------------------------------------------------------------| '| '|
'| '|
Loop Until wks Is Nothing '| '|
'--------------------------------------------------------------------------------------------| '|
'|
End If '|
'----------------------------------------------------------------------------------------------------|
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
ObjectDisposedException:
'(...)
'Put your own error handling here for a case if the given workbook has been closed and it is
'impossible to check its worksheets' names).
GoTo ExitPoint
'----------------------------------------------------------------------------------------------------------
UniqueName:
'The worksheet name is unique and can be returned.
End Function