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


xlf-invalid-name-dialog
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

    Stop

End Sub
'