How to call a macro with a flexible name

[Guest]

Hi,
I have 10 differents macro that create tables.
ex: Sub CreateT1_1(iSheetPos as integer), CreateT1_2(iSheetPos as integer),
etc.
Is there a way to say:
sMacroName = "CreateT1_1"
Call sMacroName(iSheetPos)
....
Application.Run works but it close my form at the end of the execution.
Also, is there a way to test if the Macro Exist?
I tried:
On Error Resume Next
Application.Run sMacroName, iSheetPos
Msgbox(err.number) ' This show always 0 even if sMacroName doesn't exist!
Thanks!

 

[Bob Phillips]

Hi,
I have 10 differents macro that create tables.
ex: Sub CreateT1_1(iSheetPos as integer), CreateT1_2(iSheetPos as integer),
etc.
Is there a way to say:
sMacroName = "CreateT1_1"
Call sMacroName(iSheetPos)
...
Application.Run works but it close my form at the end of the execution.

How? Show the code.

Also, is there a way to test if the Macro Exist?
I tried:
On Error Resume Next
Application.Run sMacroName, iSheetPos
Msgbox(err.number) ' This show always 0 even if sMacroName doesn't exist!

If you made it a function that returns 1 , you could set a variable to 0,
and then run and test it

myVar = 0
On Error Resume Next
myVar = Application.Run(sMacroName, iSheetPos)
On Error Goto 0
If myVar = 0 Then
...

 

[Guest]

Thanks !!
This works very well.
Alex

How? Show the code.




If you made it a function that returns 1 , you could set a variable to 0,
and then run and test it

myVar = 0
On Error Resume Next
myVar = Application.Run(sMacroName, iSheetPos)
On Error Goto 0
If myVar = 0 Then
...