# Update #

After examining the [`NameRulesUnicode64k.xlsm`][1] spreadsheet in [**excel-names**][2], 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 ##

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

-----

# Background #

Pursuant to [this question][3] of mine, I recently did research into a name validator in [VBA][4] for (function) names in Excel.  I came across the [**excel-names**][2] 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][2]:

>  - `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()`][5], 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()`][6], I took pains to flag any `name` that could subvert this approach:
 - valid names [like `R`][7], 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][8], 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
    ' ...
```


  [1]: https://github.com/MartinTrummer/excel-names/blob/master/NameRulesUnicode64k.xlsm
  [2]: https://github.com/MartinTrummer/excel-names
  [3]: https://stackoverflow.com/q/73157111
  [4]: https://codereview.stackexchange.com/tags/vba/info
  [5]: https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999
  [6]: https://docs.microsoft.com/en-us/office/vba/api/excel.application.evaluate
  [7]: https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e#__toc384648761
  [8]: https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/cell-error-values