Here's some code that will catch nearly all invalid names. In VBA, set a
reference to "TypeLib Info". Then run the procedure CreateList to create the
list of VBA keywords, and then call IsValidProcName to see if a specific
string is a valid proc name.
Sub CreateList()
Dim FName As String
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim TLIMemInfo As TLI.MemberInfo
Dim TLITypeInfo As TLI.TypeInfo
Dim N As Long
Dim R As Long
Dim RR As Range
FName = ThisWorkbook.VBProject.References("VBA").FullPath
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile(Filename:=FName)
With TLITypeLibInfo.TypeInfos
For N = 1 To .Count
On Error Resume Next
For Each TLIMemInfo In .Item(N).Members
R = R + 1
Worksheets("Sheet1").Cells(R, 1) = TLIMemInfo.Name
Next
On Error GoTo 0
Next N
End With
With Worksheets("Sheet1")
Set RR = .Range(.Cells(1, 1), .Cells(R, 1))
End With
ThisWorkbook.Names.Add Name:="KeyWords", RefersTo:=RR
End Sub
Function IsValidProcName(ProcName As String) As Boolean
Dim V As Variant
If ProcName Like "[A-Za-z]*" Then
If InStr(1, ProcName, Chr(32), vbBinaryCompare) = 0 Then
V = Application.Match(ProcName, Range("KeyWords"), 0)
If IsError(V) = False Then
IsValidProcName = False
Else
IsValidProcName = True
End If
Else
IsValidProcName = False
End If
Else
IsValidProcName = False
End If
End Function
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)