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

Thank you.

Sprinks

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

A start is he

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

--
Regards,
Tom Ogilvy


"Sprinks" wrote:

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

Thank you.

Sprinks

Tom,

Thanks for your response.

"Tom Ogilvy" wrote:

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

A start is he

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

--
Regards,
Tom Ogilvy


"Sprinks" wrote:

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

Thank you.

Sprinks

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).CodeModul e
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


"Sprinks" a écrit dans le message de
news: ...
Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

Sprinks

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

"papou" wrote:

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).CodeModul e
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


"Sprinks" a écrit dans le message de
news: ...
Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

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)

"Sprinks" wrote in message
...
Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

Sprinks

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

"Chip Pearson" wrote:

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)

"Sprinks" wrote in message
...
Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

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)

"Sprinks" wrote in message
...
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

"Chip Pearson" wrote:

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)

"Sprinks" wrote in message
...
Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

Sprinks

Chip,

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

Sprinks

"Chip Pearson" wrote:

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)

"Sprinks" wrote in message
...
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

"Chip Pearson" wrote:

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)

"Sprinks" wrote in message
...
Can anyone tell me how to test if a given string is a valid macro name?

Thank you.

Sprinks