How to test if a sheet name is valid

Sometimes you need to automatically add sheets to a workbook. This will cause problems if the name used for the worksheet does not comply to Excel's rules for valid sheet names. A string can be used for naming a sheet if:

1. The number of characters is not greater than 31 (and not 0)
2. It does not contain illegal characters: : \ / ? * [ or ]

Below shows code that implements the test IsValidSheetName. It is included in Code VBA module modSheetProcedures.bas in folder \Documents\VBA Code\Excel\Worksheet .

Private Const ciMaxLenSheetName As Integer = 31

Private Function SheetNameIllegalCharacters() As Variant
    SheetNameIllegalCharacters = Array("/", "\", "[", "]", "*", "?", ":")
End Function

Public Function IsValidSheetName(strSheetName As String) As Boolean
    IsValidSheetName = False
    If Len(strSheetName) = 0 Then Exit Function
    If Len(strSheetName) > ciMaxLenSheetName Then Exit Function

    Dim varSheetNameIllegalCharacters As Variant: varSheetNameIllegalCharacters = SheetNameIllegalCharacters
    
    Dim i As Integer
    For i = LBound(varSheetNameIllegalCharacters) To UBound(varSheetNameIllegalCharacters)
        If InStr(strSheetName, (varSheetNameIllegalCharacters(i))) > 0 Then Exit Function
    Next i

    IsValidSheetName = True
End Function