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)

• Mast: Comments are not for extended discussion; this conversation has been moved to chat.
• Greg: Hi Mathieu! After testing your IsValidName() unsuccessfully, I realize there might be a fatal flaw. When a function is called from a cell, I think Excel forbids that function — or anything it invokes — to modify anything like ThisWorkbook.Names that is outside the calling cell. So when I call Sub Test(): a = IsValidName("xyz"): MsgBox "a = " & a: End Sub from the VBA editor, your IsValidName() works as expected: a = true. But when I enter = IsValidName("xyz") into cell A1 and elsewhere, it returns FALSE—when TryDefineName() catches the error forbidding modification?
• Mathieu Guindon: Oooh I hadn't realized the function was a UDF! If you can change the paradigm a bit, it can safely be invoked in a loop from a macro that traverses interesting cells (or in a Change handler, runs when the interesting cell's value is changed)
• Mathieu Guindon: You can't do that, because none of the objects in Excel's object model exist in a void; they're all part of an object graph that has an Excel.Application instance at its top, and functions like Names.Add or Worksheets.Add and even Workbooks.Open are essentially factory methods that allow you to spawn a Name, Worksheet, or Workbook object; same for a Range: it's always going to point to a Parent worksheet and a top-level Application object.
• Greg: @MathieuGuindon I might have a workaround here! I call it "psuedoinvocation". It's somewhat unconventional, and almost certainly inefficient to the extreme, but it should make your .Names approach accessible to UDFs.

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.

• Greg: Wow, thank you for this in-depth examination, and also for the comparison in performance! Having learned that VBA has no short-circuiting, I always planned on breaking out the VBA.InStr(...) block into separate statements. Likewise, I always planned on defining a constant NAME_MAX_LENGTH. However, the Like operator is definitely news to me, and since my parser iterates heavily, I need every bit of speed I can get. As for the @Description tag, I love it! An R programmer by nurture, I find this welcomely familiar to @description.
• Mathieu Guindon: This is a great review that deserves more votes! Thanks for testing things out (and wow, a whole 30% slower... in my defense it was a phone post haha!), and for bringing up Rubberduck! @Greg having written a good chunk of it I'm obviously biased, but there's a non-zero chance that Rubberduck changes your life as a VBA dev - enjoy!
• Greg: Thanks @MathieuGuindon, I'll definitely look into it! Every time I get a neat idea (like a hyperlink to trigger a #FileBrowser()) in Excel, it always requires a component (like a formula parser) that needs developing...and then that component requires a further component (like IsValidName()) that needs developing...and so on.
• Greg: @Greedo I might have a workaround here! I call it "psuedoinvocation". It's somewhat unconventional, and almost certainly inefficient in the extreme, but it should make the .Names approach accessible to UDFs.

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. $C2ドル) 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

• Cristian Buse: @Greg Apologies about the E12.E12! I must have copied the wrong text. I updated that section and added 9 examples where your function fails. For $C2ドル I was only saying that the equivalent R1C1-style reference (i.e. R2C3) is failing and simply provided the A1-style reference of $C2ドル but did not say that $C2ドル is failing or not. Regex would be much slower than the .Names.Add approach so definitely not something I would use. Moreover, I don't think you can find a regex that would solve this better than my solution (too many cases to cover - too much guessing).
• Greg: So I'm thrilled that you pinpointed a function like Application.ConvertFormula(), because — assuming I am correctly interpreting your code — it seems to be a function that outsources the heavy lifting to Excel's own syntax validation, underneath the hood. The added benefit of extensibility is huge, since the dictionary of appropriate names and formats might change in the future, and while excel-names might scramble to keep up, the internal functions like Application.ConvertFormula() should remain up-to-date without your/my maintenance.
• Cristian Buse: @Greg Indeed my goal was to outsource the validation to Excel itself. Good catches on the first 2 points: (1) It seems a name cannot start with _xl. Will update the answer. (2) My solution fails for 254 and 255 size but works well under 253 including. Will think about how to approach this. (3) It's not as fast as your solution but then again it's about 20x-30x faster than .Names.Add approach. Would you consider =(SUM(A1+B1)+UDF1(3))*UDF2(5) as an operational group? May I ask why are you parsing formulas?
• Cristian Buse: @Greg I added section Edit #1 to cover the edge cases you found. Thanks!
• Greg: @ChristianBuse Unfortunately, when I try it at the scale of something like NameRulesUnicode64k.xlsm, Excel simply goes into a coma. Oddly enough, it does still appear to be calculating — the program is not labelled by my Mac as (not responding) — but whether I try to Calculate Sheet or merely Save, it has not changed for the past several hours.

Answer 4

We are looking for answers that provide insightful observations about the code in the question. Answers that consist of independent solutions with no justification do not constitute a code review, and may be removed.

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

• Toby Speight: You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer.
• Greg: Hi @TobySpeight! This is technically a self-answer to my own question, which hybridizes prior ideas and code above, where the improvements were detailed and the original code was reviewed in depth. For the moment, may it serve as a placeholder and a node for further discussion with the parties above?
• Toby Speight: Thanks for posting this code. It's a good idea to summarise which changes you made, and why - a self-answer ought to review the code, just like any other answer.