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:
While neither I nor the spreadsheet can vouch for names of greater length, I suspect this convergence will hold true in those cases too.
Should I hard code these two exceptions into
NameIsValid(), among the
ElseIffilters that precede the
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.
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.
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.
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
- names with outer whitespace, which is invalid yet could go unnoticed in a call; and
- names with "injection characters" like
), which could alter the call when spliced.
' 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
- Am I missing any subtle (or obvious) edge cases in my design? It is risky and rarely best practice to
Stringas code. Furthermore, I am wary of assuming that I have innovated a (somewhat trivial) solution that escaped the meticulous author of excel-names.
- Should I distinguish granularly between error types, and only
namefor 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 ' ...