xlf | xlfValidateSheetName function

xlfValidateSheetName - about the function

Description: returns a Boolean TRUE if name does not contain any invalid characters

Syntax: xlfValidateSheetName(Name)

Arguments: Name (required): the Name as a String

Fig 1: Invalid name dialog - invalid characters - : \ / ? [ or ]

Importing a csv or text file with an invalid name can raise an error when the worksheet is added (see figure 1). This module develops a ancillary procedure to validate the expected name before a new worksheet is added.

xlfValidateSheetName - the VBA code

Code 1: Function returns a string value from the addition of two binary numbers
Function xlfValidateSheetName(Name As String) As Boolean
Dim InValid As Variant
Dim Tmp As Variant
Dim i As Long

' You typed an invalid name for a sheet of chart. Make sure that:
'  The name that you typed does not exceed 31 characters
'  The name does not contain any of the following characters: : \ / ? [ or ]
'  You did not leave the name blank

    InValid = Array(":", "\", "/", "?", "*", "[", "]")

    xlfValidateSheetName = True

    If Len(Name) = 0 Or Len(Name) > 31 Then
        xlfValidateSheetName = False
        Exit Function
    End If

    For i = LBound(InValid) To UBound(InValid)
        Tmp = InStr(Name, InValid(i))   ' identify invalid characters in Name string
        If Tmp > 0 Then
            xlfValidateSheetName = False
            Exit For
        End If
    Next i

End Function

Code 2: Sub TestxlfValidateSheetName procedure
Sub TestxlfValidateSheetName()

Dim TestName As String
Dim Ans1 As Boolean, Ans2 As Boolean

    TestName = "xlf"
        Ans1 = xlfValidateSheetName(TestName)  ' returns True
    TestName = "xlf/"
        Ans2 = xlfValidateSheetName(TestName)  ' returns False


End Sub