Compact VBA Validator for Excel Names

Question

Update

After examining the NameRulesUnicode64k.xlsm spreadsheet in excel-names, it appears that my NameIsValid() is in close agreement. When examining whether or not a single character is OK as a valid name, there are only 2 discrepancies across 65,535 characters:

Chr-Code	Char	Other Case	OK	...	NameIsValid()
8217	’		FALSE	...	TRUE
12288			TRUE	...	FALSE

While neither I nor the spreadsheet can vouch for names of greater length, I suspect this convergence will hold true in those cases too.

Question

1. Should I hard code these two exceptions into NameIsValid(), among the ElseIf filters that precede the Application.Evaluate()?

2. Also, how might I improve the performance of NameIsValid(), given that it invokes Application.Evaluate()? Currently, it is even slower at scale than excel-names, which is already slow enough.

---

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! This would essentially outsource the validation to Excel itself! 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 and 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)")
        
        ' Check if the declaration erred due to invalid nomenclature.
        If IsError(eval) Then
            NameIsValid = False
        Else
            NameIsValid = True
        End If
    End If
End Function

Questions

1. Am I missing any subtle (or obvious) edge cases in my design? It is risky and rarely best practice to Evaluate() a literal String as code. Furthermore, 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?

' ...

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

Answer 1

The name parameter should be passed ByVal, and I would have named the function IsValidName to move "is" at the beginning, which makes callers read more naturally like "if IsValidName(candidate) then" while keeping with the well-established convention of starting method names with a verb.

If name = Empty is inducing an implicit type conversion where the Empty literal gets converted into an empty string; changing the condition to If name = vbNullString would eliminate this implicit conversion. Empty is a confusing concept in Classic VB: it's actually a type, and only a Variant/Empty will ever be strictly equal to the vbEmpty special value; similar to dealing with the Variant/Error data type, normally you would use the IsEmpty function to determine whether a Variant is wrapping a value or not.

The VBA.InStr checks could preemptively invalidate the colon (:) and square bracket ([, ]) characters, and I'm not sure I like the implicit expression conversions and bitwise-Or happening there: the If statement wants a Boolean expression, but this one is being converted into one, by virtue of 0 being equivalent to False and any non-zero value equating to True; the problem is that InStr returns an index / a position within the provided string, which then gets caught into bitwise-Or operations with the other InStr results: If InStr(...) <> 0 Or InStr(...) <> 0 ... is still going to involve bitwise-Or operations (all logical operators are actually bitwise!), but then the math is easier to follow because it's all down to True (-1) and False (0) values.

The Application.Evaluate hack with the LET function is clever, but note that it won't work in earlier versions of Excel that don't support it (LET released pretty recently, in Microsoft/Office 365). The evaluation happens in the context of the active worksheet, so it's probably safer (to some extent - no cell refs are involved here so it matters very little) to force it to always evaluate in the context of the same worksheet by using Worksheet.Evaluate instead (e.g. Sheet1.Evaluate(...).

Why not leverage the Workbook.Names collection/API to make a validator that can work in all (?) versions of Excel? If a given name already exists in that collection, then it's necessarily a valid name (or are we only validating new names?), and if the string value can be used to define a Name without throwing error 1004, then it's necessarily valid as well:

Public Function IsValidName(ByVal Value As String, Optional ByVal NewNameOnly As Boolean = False) As Boolean
    If IsExistingName(Value) Then
        IsValidName = Not NewNameOnly
    Else
        Dim ValidName As Excel.Name
        If TryDefineName(Value, outName:=ValidName) Then
            ValidName.Delete
            IsValidName = True
        End If
    End If
End Function
    
Private Function IsExistingName(ByVal Value As String) As Boolean
    On Error Resume Next 
    Dim Existing As Excel.Name
    Set Existing = ThisWorkbook.Names(Value)
    On Error GoTo 0
    IsExistingName = Not Existing Is Nothing
End Function 

Private Function TryDefineName(ByVal Value As String, ByRef outName As Excel.Name) As Boolean
    On Error Resume Next
    Set outName = ThisWorkbook.Names.Add(Value, Sheet1.Range("A1"))
    TryDefineName = Err.Number = 0
    On Error GoTo 0
End Function

Caveat: I haven't tested any of this, so maybe I've missed something, and maybe involving an actual Name object is adding more overhead than an Evaluate call might, but the code seems to feel more concise and self-explanatory that way.

As for your last question, I believe code should be making as few assumptions as possible, so if Evaluate returns a #NAME? error given an invalid name then IMHO that's what the code should be saying.

That said instead of assigning mutually exclusive Boolean literals in the two conditional branches, you could revert the condition and assign the return value to the result of the expression, so instead of this:

If IsError(eval) Then
    NameIsValid = False
Else
    NameIsValid = True
End If

You could simply do this:

NameIsValid = Not IsError(eval)

Answer 2

As Matt has provided an alternative solution I'll focus more on improving your current approach (well that was covered too but there are some extra things)

---

' Check if a name is valid: it may be "declared" in Excel using LET().
Public Function NameIsValid(name As String) As Boolean

Using Let is an implementation detail (as Matt demonstrated with an alternative implementation), and not something the caller really needs to worry about. Instead focus on input/output. You could use a RubberDuck description annotation here which means, once synced, this comment magically turns into a description in the VBA object explorer.

 '@Description("Returns True if candidate is a valid name for an Excel named reference, False if not or the check fails")

---

    ' Invalidate names that are empty or too long.
    If name = Empty Or VBA.Len(name) > 255 Then

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

'Excel names have a maximum length

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

Const MAX_NAME_LENGTH As Long = 255
If name = vbNullString Or VBA.Len(name) > MAX_NAME_LENGTH Then
    NameIsValid = False

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.

---

While we're here, NameIsValid = False is actually unnecessary because False is the default value of a boolean function. I would prefer to write Exit Function here, which has the side effect of allowing you to reduce the nesting of your code:

If name = Empty Or VBA.Len(name) > 255 Then Exit Function
If ( _
    name = "C" Or name = "c" Or _
    name = "R" Or name = "r" _
) Then Exit Function
    
If name <> Application.WorksheetFunction.Clean(VBA.Trim(name)) Then Exit Function
If ( _
    VBA.InStr(1, name, "(") Or _
    VBA.InStr(1, name, ",") Or _
    VBA.InStr(1, name, ";") Or _
    VBA.InStr(1, name, ")") _
) Then Exit Function

' Get the result of formulaically declaring a name with LET() in Excel.
Dim eval As Variant
eval = Application.Evaluate("= LET(" & name & ", 0, 0)")

' Check if the declaration erred due to invalid nomenclature.
NameIsValid = IsError(eval)

---

' 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.

This is a great comment, explains a non-obvious bit of code.

---

name = "C" Or name = "c" Or _
name = "R" Or name = "r"

VBA.InStr(1, name, "(") Or _
VBA.InStr(1, name, ",") Or _
VBA.InStr(1, name, ";") Or _
VBA.InStr(1, name, ")") _

These can both be syntactically simplified with the Like operator:

If name Like "[cCrR]" Then Exit Function 'catches single character reserved names
If name Like "*[(,;)]*" Then Exit Function 'catches at least one occurence of invalid character

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.

Update: I just checked, swapping to Like is ~25% faster than with InStr. However the call to Evaluate swallows about 80% of the execution time so changes to the earlier stage aren't too impactful (i.e that's 25% improvement if the name is invalid, but only 25*20 = 5% improvement if the name is potentially valid and has to be evaluated).

FWIW, Matt's approach is about 30x slower (worst case no cache hits) - however the point is very important that for < 1000 checks (on my CPU) it's still under a second so really performance only matters for bigger numbers

The approach in that attached xlsm appears to be about 5-10x faster than your code so perhaps that approach is just better for performance (although a complete mess in terms of amount of code that it hardly seems worth it, Matt's version is IMO easiest to follow and guaranteed to be correct).

---

Questions

1. Am I missing any subtle (or obvious) edge cases in my design? It is risky and rarely best practice to Evaluate() a literal String as code. Furthermore, I am wary of assuming that I have innovated a (somewhat trivial) solution that escaped the meticulous author of excel-names.

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.

2. Should I distinguish granularly between error types, and only invalidate the name for specific reasons?

Yes IMO you should only surpress errors you expect, other errors you rethrow to the user (in case this is consumed by VBA) or return as an error if this is a UDF:

'@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.

Answer 3

Missed issue - cell references

Cell references are not allowed as names for obvious reasons.

r and c are not reserved names. When dealing with R1C1 Reference style notation, these are actual range references that are relative to the active cell. For example if the Active cell is C7 then r represents the 7:7 range and c represents the C:C range.

The current Let approach completely misses the R1C1 notation. For example your method returns True for R2C3 (i.e. $C$2) but in fact that is not allowed as a name. However, _R2C3 is a valid name.

The A1 reference style is a big problem as well. The following are all valid names but both your solution and the Excel-Names repo return False:

• rA12.
• E12.
• E12.A
• E12.1
• E12.r

The following names are invalid but your solution returns True (first one is wrong in the Excel-Names as well):

• RC
• R1C1
• R1C1A
• R2c3

Let function

As mentioned by @MathieuGuindon, this is not a solution that would be compatible with all Excel versions. Combined with the fact that it completely misses the R1C1 and A1 notations, this is not a reliable approach.

Workbook.Names approach

As mentioned by @MathieuGuindon, using the Names collection is very useful for checking if a name already exists, hence is valid.

Moreover, it is the only "no-guessing" solution to check if a name is valid by temporarily adding to the collection of names and then checking if it was successful.

However, using .Names.Add to check if a name is valid does not work if you want to call your function from an actual cell (i.e. as a User Defined Function). It's also slow as pointed out by @Greedo.

What I would definitely keep from Mathieu's solution is checking if the name exists. By using the explicit call (i.e. .Names.Item(...)) instead of the implicit default member of the collection (i.e. .Names(...)), we could actually write a more compact version:

Public Function IsName(ByRef nameToCheck As String) As Boolean
    On Error Resume Next
    ThisWorkbook.Names.Item nameToCheck
    IsName = (Err.Number = 0)
    On Error GoTo 0
End Function

We could re-write it so that it accepts a book parameter for more flexibility but I won't do that here.

Other answers

I am not going to cover the rest of your code as that's been done already by both @MathieuGuindon and @Greedo. Plus I am not going to use anything else from it except the 255 length check.

I still need to mention that IsNameValid is a much better name as pointed by Mathieu and the extra optional parameter that he uses is something I will just replicate.

Solution

We can use Application.ConvertFormula to convert the name from R1C1 to A1 and viceversa. Cell references are quickly identified using this method as the resulting string is different. For example Application.ConvertFormula("E12", xlA1, xlR1C1) returns R[1]C[-1].

However, there are a few things to consider:

• numbers convert both ways to the same string so we must first check is the provided name is numeric
• extra logic is needed for the dot character. E12.E12 is not valid while E12.E is valid. I brute forced through all the characters range and the dot is the only character that needs this special attention
• after validating the 2 steps above, there are still a few forbidden characters that we must check for. I brute forced these as well and they are only 26. The InStr approach seems to be faster than Like for 26 characters. I tested this with various names with length 1 to 255

Below is the solution I came up with. I tested this against the .Names.Add approach and it passes all the tests (that I came up with).

Option Explicit

Public Function IsNameValid(ByVal nameToCheck As String _
                          , Optional ByVal newNameOnly As Boolean = False) As Boolean
    Const maxSize As Long = 255
    Dim size As Long: size = Len(nameToCheck)
    '
    If size = 0 Or size > maxSize Then Exit Function
    If IsName(nameToCheck) Then
        IsNameValid = Not newNameOnly
        Exit Function
    End If
    If IsNumeric(nameToCheck) Then Exit Function
    '
    Dim a As Variant: a = Application.ConvertFormula(nameToCheck, xlR1C1, xlA1)
    Dim r As Variant: r = Application.ConvertFormula(nameToCheck, xlA1, xlR1C1)
    '
    If IsError(a) And IsError(r) Then Exit Function
    If Not IsConversionValid(nameToCheck, a) Then Exit Function
    If Not IsConversionValid(nameToCheck, r) Then Exit Function
    '
    Static invalidChars(0 To 25) As String
    Static isSet As Boolean
    Dim i As Long
    '
    If Not isSet Then
        Dim v As Variant
        For Each v In Array(-144, 0, 32, 33, 38, 42, 43, 44, 45, 47, 60, 61, 62, 94 _
                          , 12293, 12337, 12338, 12339, 12340, 12341, 12347, 12445 _
                          , 12446, 12540, 12541, 12542)
            invalidChars(i) = ChrW$(v)
            i = i + 1
        Next v
        isSet = True
    End If
    '
    For i = LBound(invalidChars) To UBound(invalidChars)
        If InStr(1, nameToCheck, invalidChars(i)) > 0 Then Exit Function
    Next i
    '
    IsNameValid = True
End Function

Public Function IsName(ByRef nameToCheck As String) As Boolean
    On Error Resume Next
    ThisWorkbook.Names.Item nameToCheck
    IsName = (Err.Number = 0)
    On Error GoTo 0
End Function

Private Function IsConversionValid(ByRef nameToCheck As String _
                                 , ByRef convertedName As Variant) As Boolean
    If IsError(convertedName) Then GoTo Validate
    If StrComp(nameToCheck, convertedName, vbTextCompare) = 0 Then GoTo Validate
    If InStr(1, nameToCheck, ".") = 0 Then Exit Function
    '
    Dim arrParts() As String: arrParts = Split(nameToCheck, ".")
    Dim arrConv() As String: arrConv = Split(convertedName, ".")
    Dim i As Long
    '
    For i = LBound(arrParts) To UBound(arrParts)
        If Len(arrParts(i)) = Len(arrConv(i)) Then GoTo Validate
    Next i
Exit Function
Validate:
    IsConversionValid = True
End Function

Edit #1

Based on the discussion in the comments section, I've added 2 changes:

1. Names that start with _xl are not valid so now the function returns False
2. The Application.ConvertFormula fails for names of 254 and 255 length even though the documentation clearly mentions:

There is a 255 character limit for the formula.

Revised code:

Option Explicit

Public Function IsNameValid(ByVal nameToCheck As String _
                          , Optional ByVal newNameOnly As Boolean = False) As Boolean
    Const maxSize As Long = 255
    Dim size As Long: size = Len(nameToCheck)
    '
    If size = 0 Or size > maxSize Then Exit Function
    If IsName(nameToCheck) Then
        IsNameValid = Not newNameOnly
        Exit Function
    End If
    If IsNumeric(nameToCheck) Then Exit Function
    If StrComp(Left$(nameToCheck, 3), "_xl", vbTextCompare) = 0 Then Exit Function
    '
    If size > 253 Then '254 and 255
        If Not IsNameValid(Left$(nameToCheck, 253)) Then Exit Function
        IsNameValid = IsNameValid(Right$(nameToCheck, 253))
        Exit Function
    End If
    '
    Dim a As Variant: a = Application.ConvertFormula(nameToCheck, xlR1C1, xlA1)
    Dim r As Variant: r = Application.ConvertFormula(nameToCheck, xlA1, xlR1C1)
    '
    If IsError(a) And IsError(r) Then Exit Function
    If Not IsConversionValid(nameToCheck, a) Then Exit Function
    If Not IsConversionValid(nameToCheck, r) Then Exit Function
    '
    Static invalidChars(0 To 25) As String
    Static isSet As Boolean
    Dim i As Long
    '
    If Not isSet Then
        Dim v As Variant
        For Each v In Array(-144, 0, 32, 33, 38, 42, 43, 44, 45, 47, 60, 61, 62, 94 _
                          , 12293, 12337, 12338, 12339, 12340, 12341, 12347, 12445 _
                          , 12446, 12540, 12541, 12542)
            invalidChars(i) = ChrW$(v)
            i = i + 1
        Next v
        isSet = True
    End If
    '
    For i = LBound(invalidChars) To UBound(invalidChars)
        If InStr(1, nameToCheck, invalidChars(i)) > 0 Then Exit Function
    Next i
    '
    IsNameValid = True
End Function

Public Function IsName(ByRef nameToCheck As String) As Boolean
    On Error Resume Next
    ThisWorkbook.Names.Item nameToCheck
    IsName = (Err.Number = 0)
    On Error GoTo 0
End Function

Private Function IsConversionValid(ByRef nameToCheck As String _
                                 , ByRef convertedName As Variant) As Boolean
    If IsError(convertedName) Then GoTo Validate
    If StrComp(nameToCheck, convertedName, vbTextCompare) = 0 Then GoTo Validate
    If InStr(1, nameToCheck, ".") = 0 Then Exit Function
    '
    Dim arrParts() As String: arrParts = Split(nameToCheck, ".")
    Dim arrConv() As String: arrConv = Split(convertedName, ".")
    Dim i As Long
    '
    For i = LBound(arrParts) To UBound(arrParts)
        If StrComp(arrParts(i), arrConv(i), vbTextCompare) = 0 Then GoTo Validate
    Next i
Exit Function
Validate:
    IsConversionValid = True
End Function

Answer 4

I've come up with a potential solution, which leverages something I call "psuedoinvocation".

' #######################
' ## Modular Variables ##
' #######################

' ## (Psuedo)parameters for Try_Name().

' Name to try defining.
Private tryName As String

' Range on which to define the name.
Private tryRange As Range



' ####################
' ## Name Functions ##
' ####################

' Check if a name is syntactically valid.
Public Function Is_Valid_Name( _
    ByVal name As String, _
    Optional ByVal newly As Boolean = False _
) As Boolean
    ' An existing name is necessarily valid (unless otherwise specified).
    If Is_Name(name) Then
        Is_Valid_Name = Not newly
        
    ' A new name must be tested for validity.
    Else
        ' Set the modular variables as (psuedo)arguments to Try_Name().
        tryName = name
        Set tryRange = Application.Caller
        
        ' Call Try_Name() via (psuedo)invocation.
        Is_Valid_Name = Application.Evaluate("Try_Name()")
    End If
End Function


' Check if a name already exists in this workbook.
Private Function Is_Name(ByVal name As String) As Boolean
    On Error Resume Next
    
    ThisWorkbook.Names.Item name
    IsName = (Err.Number = 0)
    
    On Error GoTo 0
End Function


' Try to define a new name in this workbook.
Private Function Try_Name() As Boolean
    On Error GoTo Fail
    ' MsgBox "Trying to define name ('" & tryName & "') on range (" & tryRange.Address & ")."
    
    ' Attempt to define the name, using the modular (psuedo)parameters.
    Set try = ThisWorkbook.Names.Add(tryName, tryRange)
    
    ' Undo any successful attempt.
    try.Delete
    
    ' Report success.
    Try_Name = True
    Exit Function

Fail:
    Try_Name = False
End Function