FWIW...
As usual, the only documentation is of intent and limitations. In this
case, that is substantial. The actual code is miniscule {bg}
Option Explicit
'Need a reference to MS VBScript Regular Expressions 5.5
Private Sub InitRE(ByRef aRE As RegExp)
If aRE Is Nothing Then Set aRE = New RegExp
aRE.Global = True
aRE.IgnoreCase = True
End Sub
Function ValidCellReference(aStr As String, ByRef aRE As RegExp)
'This function is limited in its capability; it only _
distinguishes between an XL name and a possible cell reference _
that has already met the test for a sytantically valid name. _
Hence, aStr is presumed to have no $ or [ or ] or -
'Also see comment for FoundName function
Const ValidCellRCPattern As String = "^(R\d+)?(C\d+)?$"
'should actually use a variable and use the _
international codes for R and C
Const ValidCellA1Pattern = "^([a-h][a-z]*|i[a-v]|[i-z])\d*"
'Note this pattern only works as long as MS doesn't increase _
# columns.
'A 'trick' that seems to work is to convert the string from _
xlR1C1 to xlA1. _
If it is not a valid reference, the result is the string itself. _
If the string is a valid A1 reference, the result is the string _
itself but *in* single quotes. _
Otherwise XL converts it into a valid A1 reference. _
Now, all one needs to do is check for the first condition
'WARNING: This method / trick has only been confirmed empirically _
======= and that too only on XL2003
ValidCellReference = Not ( _
Application.ConvertFormula(aStr, xlR1C1, xlA1) = aStr)
'hopefully, ConvertFormula is aware of international _
conventions
End Function
Function FoundName(aStr As String)
'See XL help for the definition of a valid name; it basically _
starts with a _ or a letter and includes any combination of _
_ or letter or digit or period. _
However, it cannot be a cell reference in either A1 or R1C1 _
reference style. The first part is easy (see the _
ValidNameSyntaxPattern constant. The 2nd part, at first, appears _
more difficult because even after we parse the string into its _
components we need to check if a number, if present at the _
expected position(s) is between 1 and, as appropriate, the max. _
number of rows/columns allowed. However, a trick allows great _
simplification; see the comment in ValidCellReference
Static aRE As RegExp
Const ValidNameSyntaxPattern As String = _
"^[_a-zA-Z][_a-zA-Z0-9.]*$"
InitRE aRE
aRE.Pattern = ValidNameSyntaxPattern
If aRE.Test(aStr) Then
FoundName = Not ValidCellReference(aStr, aRE)
Else
FoundName = False
End If
End Function
Sub testit()
MsgBox FoundName("__9aN.909ame")
MsgBox FoundName("090abc")
MsgBox FoundName("r[-1]c") & "," & FoundName("r1c1") _
& "," & FoundName("r65536c1") & "," & FoundName("r65537c1")
MsgBox FoundName("rc") & "," & FoundName("iv65536") _
& "," & FoundName("iv65537")
End Sub
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
Here is a cleaned up version:
Const sINV1 = "[.0-9=3F^~T?¯?·?[?]*"
Const sINVn = "*[!0-9A-z.\_f=3F=3F=3F=3F=3F=3F=3Fªµ" & _
"ºÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕ" & _
"ÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíî" & _
"ïðñòóôõöøùúûüýþ=3F=3F^~T?¯?·?[?]*"
Function NameIsInvalid(ByVal _
sFull As String) As Boolean
Dim sText$, bRes As Boolean
If InStrB(sFull, "!") Then
sText = fnSplice(sFull)(1)
Else
sText = sFull
End If
If Not sText Like sINV1 Then
If Not sText Like sINVn Then
bRes = True
End If
End If
If bRes Then
If Not UCase$(sText) Like "*[!0-9RC" & _
Application.International( _
xlUpperCaseRowLetter) & _
Application.International( _
xlUpperCaseColumnLetter) & "]*" Then
If CStr(Application.ConvertFormula(sText, _
xlR1C1, , xlRelative)) <> sText Then
bRes = False
GoTo theExit
End If
End If
If sText Like "[A-z]*#" Then
If IsNumeric(MidB$(sText, 3)) Or _
IsNumeric(MidB$(sText, 5)) Then
If IsObject(Evaluate(sText)) Then
bRes = False
GoTo theExit
End If
End If
End If
End If
theExit:
NameIsInvalid = Not bRes
End Function
Function fnSplice(sName As String) As String()
Dim s(0 To 1) As String, i%
i = InStrRev(sName, "!")
If i = 0 Then
s(1) = sName
Else
s(0) = Left$(sName, i - 1)
s(1) = Mid$(sName, i + 1)
End If
fnSplice = s
End Function
