Return to Answer

'@Description("Returns True if candidate is a valid name for an Excel named reference, False if not, and an error if the check fails")
Public Function NameIsValid(name As String) As Variant
    '...
    
    If IsError(eval) Then
        ' Granularly distinguish between specific errors.
        If ( _
            eval = CVErr(xlErrName) Or _
            eval = CVErr(xlErrValue) _
        ) Then
            NameIsValid = False
        Else
            NameIsValid = eval 'return the unknown error
        End If
    Else
        NameIsValid = True
    End If
End Function

'@Description("Returns True if candidate is a valid name for an Excel named reference, False if not, and an error if the check fails")
Public Function NameIsValid(name As String) As Variant
    NameIsValid = False
    '...
    
    If IsError(eval) Then
        ' Granularly distinguish between specific errors.
        If ( _
            eval = CVErr(xlErrName) Or _
            eval = CVErr(xlErrValue) _
        ) Then
            NameIsValid = False
        Else
            NameIsValid = eval 'return the unknown error
        End If
    Else
        NameIsValid = True
    End If
End Function

Note False is not the default value of a variant function so must be specified explicitly.

I wouldn't be too wary about a novel solution; LET was introduced in the last couple of years, that workbook was last updated in 2017.

I wouldn't be too wary about a novel solution; LET was introduced in the last couple of years, that workbook was last updated in 2017. Also I was interested in that security concern; indeed IIUC it is possible to execute cmd.exe from Evaluate using direct data exchange, or send sensitive data to an attacker's web server with the HYPERLINK function or something similar. However in the context of the LET function, Excel if expecting a name not a formula so shouldn't evaluate things and I think you've managed to exclude the "injection characters" otherwise of course ",0,0) & MALICIOUSCODE() & CONCAT(0" would trip things up. I think leave an explicit comment about the security concern is important to avoid this protection being removed at a later date.

This comment is unnecessary as we can see what happens on the next line - at least qualify too long as

to explain why you perform the check, or better yet get rid of 255 and use a named constant:

No comment required. You want to eliminate comments that explain what the code does where possible because if your code changes the comment becomes out of sync.

    ' Invalidate names with external whitespace (or double spaces internally),
    ' which are invalid in names and yet could mesh syntactically with
    ' formulaic calls to LET() in Excel.

can both be syntactically simplified with the Like operator:

although I'm not sure what that would do for speed. The InStr functions are definitely your slow point (if not the evaluate call); not only does checking require iterating over every character until the correct one is found, VBA does not short circuit operators so always performs all 4 checks even if the first one fails. Split onto 4 lines if performance is key. In any language worth its salt the Like operator should be fast and compiled - but as Matt says VBA's string operators and functions are notoriously slow.

This comment is unnecessary as we can see what happens on the next line - at least qualify what too long means, e.g.

to explain why you perform the check. Or better yet get rid of 255 and use a named constant:

No comment required. Generally, you want to eliminate comments that explain what the code does where possible because if your code changes the comment becomes out of sync.

' Invalidate names with external whitespace (or double spaces internally),
' which are invalid in names and yet could mesh syntactically with
    ' formulaic calls to LET() in Excel.

These can both be syntactically simplified with the Like operator:

although I'm not sure what that would do for speed (see below). The InStr functions are definitely your slow point (as long as the evaluate branch is never reached); not only does checking require iterating over every character until the correct one is found, VBA does not short circuit operators so always performs all 4 checks even if the first one fails. Split onto 4 lines if performance is key. In any language worth its salt the Like operator should be fast and compiled - but as Matt says VBA's string operators and functions are notoriously slow.