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 trowing 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)
