Testing for Valid Macro Name
 

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

Thank you.

Sprinks

Testing for Valid Macro Name
 

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

Testing for Valid Macro Name
 

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

Testing for Valid Macro Name
 

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

Testing for Valid Macro Name
 

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

Testing for Valid Macro Name
 

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

Testing for Valid Macro Name
 

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

Testing for Valid Macro Name
 

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

Testing for Valid Macro Name
 

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