Unique sheet name


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