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
'**********************************************************************************************************
' Name: legalSheetName
' Author: mielk | 2012-03-26
'
' Comment: Function checks if a given name follows the rules for naming Excel worksheets.
' Legal Excel sheet name:
' * cannot be empty,
' * must be at most 31 characters long
' * must not contain any of the illegal characters: : ? / \ * [ ]
' If the given name doesn't meet these requirements it is being adjusted and this
' adjusted value is being returned by the function.
'
' Parameters:
' name The name to be checked if it is a legal Excel worksheet name.
'
' Returns:
' String The given name after being adjusted to be a proper Excel sheet name.
' * If the original name itself is a legal Excel sheet name it is returned without
' any changes.
' * If the original name is empty, underline character is returned (_).
' * If the original name is longer than 31 characters, it is cut off.
' * All the illegal characters : ? / \ * [ ] are removed from the original name.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-03-26 mielk Function created.
'**********************************************************************************************************
Public Function legalSheetName(name As String) As String
Const METHOD_NAME As String = "legalSheetName"
'------------------------------------------------------------------------------------------------------
Const ILLEGAL_CHARS As String = ":?/\*[]"
Dim intChar As Integer
Dim strChar As String
Dim strIllegalChars As String
'------------------------------------------------------------------------------------------------------
'Function iterates through all characters in the original name and removes the illegal ones. --------|
For intChar = 1 To VBA.Len(name) '|
strChar = VBA.Mid$(name, intChar, 1) '|
'|
'--------------------------------------------------------------------------------------------| '|
If VBA.InStr(1, strIllegalChars, strChar) = 0 Then '| '|
legalSheetName = legalSheetName & strChar '| '|
End If '| '|
'--------------------------------------------------------------------------------------------| '|
'|
Next intChar '---------------------------------------------------------------------------------------|
'Checks the length of the sheet name after all the illegal characters have been removed. ------------|
'If it is too long, the excessive characters are cut off. '|
Select Case Len(legalSheetName) '|
Case Is > 31 '|
legalSheetName = Left$(legalSheetName, 31) '|
Case 0 '|
legalSheetName = "_" '|
End Select '-----------------------------------------------------------------------------------------|
End Function