Testing for Valid Macro Name

[Guest]

Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

Sprinks

 

[Guest]

You would have to write code to read all the information in the modules and
parse out the macro names.

A start is here:

http://www.cpearson.com/excel/vbe.htm

Or you might try something like this:

Sub AAA()
s = "MyMacro"
On Error Resume Next
Application.Run s
If Err.Number <> 0 Then
MsgBox s & " is not a macro name or there is an error in the macro"
Else
MsgBox "OK"
End If
On Error goto 0

End Sub

 

[papou]

Hello Sprinks
Here's one way I use (please apologise for its beeing in french but you will
easily find translation I am sure)
HTH
Cordially
Pascal

Sub Recherche_Macro()
'Ajouter une référence à
'Microsoft VisualBasic For Application Extensibility 5.3 (Menu
Outils|Références de l'éditeur VBA)

If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Le classeur actif ne doit pas contenir la macro Recherche_Macro"
_
, vbInformation + vbOKOnly, "Arrêt"
Exit Sub
End If
Dim Saisie, LeNom As String
Saisie = InputBox("Saisir le nom de la macro cherchée" & vbLf & "en
respectant les majuscules / minuscules")
If Saisie = "" Then Exit Sub
LeNom = "Sub " & Saisie
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim RechercheLaMacro
Dim i As Integer, y As Integer
For i = 1 To ActiveWorkbook.VBProject.VBComponents.Count
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents(i).CodeModule
With VBCodeMod
For y = 1 To .CountOfLines
StartLine = .CountOfDeclarationLines + 1
RechercheLaMacro = .Find(LeNom, StartLine, 1, .CountOfLines, -1,
True, True, False)
Next
End With
Next
If RechercheLaMacro = True Then
GoTo trouve
Else
GoTo pastrouve
End If

Exit Sub
trouve:
MsgBox "La macro " & Saisie & " est bien dans le classeur " &
ActiveWorkbook.Name
Exit Sub
pastrouve:
MsgBox "La macro " & Saisie & " n'est pas dans le classeur " &
ActiveWorkbook.Name
Exit Sub
End Sub

 

[Chip Pearson]

Try a function like


Function IsValidProcName(ProcName As String) As String
' reference to "Microsoft VBScript Regular Expressions 5.5
Dim Pattern As String
Dim RExp As VBScript_RegExp_55.RegExp

Set RExp = New VBScript_RegExp_55.RegExp
Pattern = "^[A-Za-z]+[A-Za-z0-9_]*$"
RExp.Pattern = Pattern

IsValidProcName = RExp.Test(ProcName)
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Chip,

Thank you for your response.

I'm afraid I don't understand your code, but in any case, it returns True
whether the string is a valid name or not.

??

Sprinks

Try a function like


Function IsValidProcName(ProcName As String) As String
' reference to "Microsoft VBScript Regular Expressions 5.5
Dim Pattern As String
Dim RExp As VBScript_RegExp_55.RegExp

Set RExp = New VBScript_RegExp_55.RegExp
Pattern = "^[A-Za-z]+[A-Za-z0-9_]*$"
RExp.Pattern = Pattern

IsValidProcName = RExp.Test(ProcName)
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

Sprinks

 

[Guest]

Tom,

Thanks for your response.

You would have to write code to read all the information in the modules and
parse out the macro names.

A start is here:

http://www.cpearson.com/excel/vbe.htm

Or you might try something like this:

Sub AAA()
s = "MyMacro"
On Error Resume Next
Application.Run s
If Err.Number <> 0 Then
MsgBox s & " is not a macro name or there is an error in the macro"
Else
MsgBox "OK"
End If
On Error goto 0

End Sub

 

[Chip Pearson]

I'm afraid I don't understand your code, but in any case, it returns True

whether the string is a valid name or not.

I thought you meant a syntactically valid procedure name, not necessarily
the name of an existing procedure. My mistake. The code I posted only tests
whether the procedure name is allowable by VBA syntax, not whether the
procedure actually exists.

Regular Expressions are a method of testing whether a given text value
matches a pattern. The pattern I used,

^[A-Za-z]+[A-Za-z0-9_]*$

tests for a beginning of string (^), followed by one or more charcters in
the range A-Z (upper or lower case), followed be zero or more characters in
the range A-Z a-z 0-9 or an underscore, followed by and end of string ($).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Chip,

Thank you for your response.

I'm afraid I don't understand your code, but in any case, it returns True
whether the string is a valid name or not.

??

Sprinks

Try a function like


Function IsValidProcName(ProcName As String) As String
' reference to "Microsoft VBScript Regular Expressions 5.5
Dim Pattern As String
Dim RExp As VBScript_RegExp_55.RegExp

Set RExp = New VBScript_RegExp_55.RegExp
Pattern = "^[A-Za-z]+[A-Za-z0-9_]*$"
RExp.Pattern = Pattern

IsValidProcName = RExp.Test(ProcName)
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

Sprinks

 

[Guest]

Chip,

Thank you for your explanation. I will store your post for when it might be
applicable--that could be a powerful tool.

Sprinks

I'm afraid I don't understand your code, but in any case, it returns True
whether the string is a valid name or not.

I thought you meant a syntactically valid procedure name, not necessarily
the name of an existing procedure. My mistake. The code I posted only tests
whether the procedure name is allowable by VBA syntax, not whether the
procedure actually exists.

Regular Expressions are a method of testing whether a given text value
matches a pattern. The pattern I used,

^[A-Za-z]+[A-Za-z0-9_]*$

tests for a beginning of string (^), followed by one or more charcters in
the range A-Z (upper or lower case), followed be zero or more characters in
the range A-Z a-z 0-9 or an underscore, followed by and end of string ($).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Chip,

Thank you for your response.

I'm afraid I don't understand your code, but in any case, it returns True
whether the string is a valid name or not.

??

Sprinks

Try a function like


Function IsValidProcName(ProcName As String) As String
' reference to "Microsoft VBScript Regular Expressions 5.5
Dim Pattern As String
Dim RExp As VBScript_RegExp_55.RegExp

Set RExp = New VBScript_RegExp_55.RegExp
Pattern = "^[A-Za-z]+[A-Za-z0-9_]*$"
RExp.Pattern = Pattern

IsValidProcName = RExp.Test(ProcName)
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

Sprinks

 

[Guest]

Papou,

Parce que on parle francais ici, je n'avais pas change le Sub. La saveur est
plus delicieuse en francais. Mais, malheuresement, il marche trop
lentement pour mon objet.

En fin, j'ai decide de traiter l'erreur qui se presente avec un "Resume
Next" declaration. Simple!

Merci encore.

Sprinks