Background

Pursuant to this question of mine, I recently did research into a name validator in VBA for (function) names in Excel. I came across the excel-names project, an impressive undertaking whose VBA modules check validity via brute force, by iterating over the exhaustive set of all legal characters.

Unfortunately, excel-names cannot guarantee perfect validity here:

• Names_IsValidName(sNameToTest As String) As Boolean

  Check if the name is valid:

  • true: Excel name is probably valid

  • false: Excel name is for sure not valid:

Furthermore, while excel-names is probably as compact as possible, its exhaustive nature makes for a hefty dependency that I'd rather avoid.

Idea

Then I was struck with an idea: I could leverage LET(), which can in formulae declare temporary variables under valid names! If the declaration succeeds, the name is valid; if it errors, then the name is invalid.

Before calling LET() via Application.Evaluate(), I took pains to flag any name that could subvert this approach:

• valid names like R, which are already reserved yet are permitted by LET();
• names with outer whitespace, which is invalid yet could go unnoticed in a call; and
• names with "injection characters" like ) and ,, which could alter the call when spliced.

Solution

' Check if a name is valid: it may be "declared" in Excel using LET().
Public Function NameIsValid(name As String) As Boolean
    ' Invalidate names that are empty or too long.
    If name = Empty Or VBA.Len(name) > 255 Then
        NameIsValid = False
    ' Invalidate reserved names: "R" and "C".
    ElseIf ( _
        name = "C" Or name = "c" Or _
        name = "R" Or name = "r" _
    ) Then
        NameIsValid = False
    ' Invalidate names with external whitespace (or double spaces internally),
    ' which are invalid in names yet could mesh syntactically with formulaic
    ' calls to LET() in Excel.
    ElseIf name <> Application.WorksheetFunction.Clean(VBA.Trim(name)) Then
        NameIsValid = False
    ' Invalidate names with injection characters, which are invalid in names
    ' and also disrupt formulaic calls to LET() in Excel.
    ElseIf ( _
        VBA.InStr(1, name, "(") Or _
        VBA.InStr(1, name, ",") Or _
        VBA.InStr(1, name, ";") Or _
        VBA.InStr(1, name, ")") _
    ) Then
        NameIsValid = False
    ' If we pass the above checks, we can safely splice the name into a
    ' formulaic declaration with LET() in Excel.
    Else
        ' Get the result of formulaically declaring a name with LET() in Excel.
        Dim eval As Variant
        eval = Application.Evaluate("= LET(" & name & ", 0, 0)")
        
        ' ' For testing purposes.
        ' Debug.Print eval
        
        ' Check if the declaration erred due to invalid nomenclature.
        If IsError(eval) Then
            NameIsValid = False
            
            ' ' Granularly distinguish between specific errors.
            ' If ( _
            '     eval = CVErr(xlErrName) Or _
            '     eval = CVErr(xlErrValue) _
            ' ) Then
            '     NameIsValid = False
            ' Else
            '     NameIsValid = True
            ' End If
        Else
            NameIsValid = True
        End If
    End If
End Function

Questions

1. Am I missing any subtle (or obvious) edge cases in my design? I am wary of assuming that I have innovated a (somewhat trivial) solution that escaped the meticulous author of excel-names.
2. Should I distinguish granularly between error types, and only invalidate the name for specific reasons?

' ...

' Granularly distinguish between specific errors.
If ( _
    eval = CVErr(xlErrName) Or _
    eval = CVErr(xlErrValue) _
) Then
    NameIsValid = False
Else
    NameIsValid = True
End If

' ...