Excel XP, Win XP
I have a number of macros, say Able, Baker, Charlie, etc.
I have a variable named MacroToRun as string.
I have code that sets the value of MacroToRun as, say, "Charley".
I want to run the macro whose name is in the variable MacroToRun.
How do I call that macro?
Thanks for your time. Otto

Sub Charley()
MsgBox "Charley"
End Sub
Sub Tester1()
MacrotoRun = "Charley"
Application.Run MacrotoRun

End Sub

--
Regards,
Tom Ogilvy






"Otto Moehrbach" wrote in message
...
Excel XP, Win XP
I have a number of macros, say Able, Baker, Charlie, etc.
I have a variable named MacroToRun as string.
I have code that sets the value of MacroToRun as, say, "Charley".
I want to run the macro whose name is in the variable MacroToRun.
How do I call that macro?
Thanks for your time. Otto

To add to what Tom said (since it is an issue that caused me some grief
the first time I used Application.run) if your macros reside in a Sheet
module as opposed to a general code module then it seems to be the case
that you need to qualify the macro names - even if you are calling them
from the same sheet. For example, in Sheet1 the following works fine:

Sub Greet(who As String)
MsgBox "Hello " & who
End Sub

Sub CallIt()
Application.Run "Sheet1.Greet", "World"
End Sub

But replacing "Sheet1.Greet" by "Greet" yields "Run time error '1004':
application or object defined error" (or words to that effect).

HTH

-John Coleman

Otto Moehrbach wrote:
Excel XP, Win XP
I have a number of macros, say Able, Baker, Charlie, etc.
I have a variable named MacroToRun as string.
I have code that sets the value of MacroToRun as, say, "Charley".
I want to run the macro whose name is in the variable MacroToRun.
How do I call that macro?
Thanks for your time. Otto

Thanks Tom. I knew it had to be something simple. Otto
"Tom Ogilvy" wrote in message
...
Sub Charley()
MsgBox "Charley"
End Sub
Sub Tester1()
MacrotoRun = "Charley"
Application.Run MacrotoRun

End Sub

--
Regards,
Tom Ogilvy






"Otto Moehrbach" wrote in message
...
Excel XP, Win XP
I have a number of macros, say Able, Baker, Charlie, etc.
I have a variable named MacroToRun as string.
I have code that sets the value of MacroToRun as, say, "Charley".
I want to run the macro whose name is in the variable MacroToRun.
How do I call that macro?
Thanks for your time. Otto

John
Correct me if I'm wrong with this, but all macros in a sheet macro are
event macros. That raises the question in my mind "Why would I want to run
an event macro at any time other than at the occurrence of the event?
Apparently, you wanted to do so at one time so maybe all sheet macros are
not event macros? Otto
"John Coleman" wrote in message
oups.com...
To add to what Tom said (since it is an issue that caused me some grief
the first time I used Application.run) if your macros reside in a Sheet
module as opposed to a general code module then it seems to be the case
that you need to qualify the macro names - even if you are calling them
from the same sheet. For example, in Sheet1 the following works fine:

Sub Greet(who As String)
MsgBox "Hello " & who
End Sub

Sub CallIt()
Application.Run "Sheet1.Greet", "World"
End Sub

But replacing "Sheet1.Greet" by "Greet" yields "Run time error '1004':
application or object defined error" (or words to that effect).

HTH

-John Coleman

Otto Moehrbach wrote:
Excel XP, Win XP
I have a number of macros, say Able, Baker, Charlie, etc.
I have a variable named MacroToRun as string.
I have code that sets the value of MacroToRun as, say, "Charley".
I want to run the macro whose name is in the variable MacroToRun.
How do I call that macro?
Thanks for your time. Otto

Otto,
General subs and functions can be in a sheet macro module. Often
event handlers are easier to understand if you modularize and place
some of the logic in separate subs or functions - and if they are only
to be used in Sheet1 it makes some sense to leave these subs and
functions in the Sheet1 module (though there would be nothing wrong
with placing them in a general code module). Also - I tend to use event
handlers, especially button click events, as little more than
dispathers which call an appropriate sub. In this context
Application.Run could have a useful role inside of a sheet module. You
are thus technically wrong about sheet macros but you are right in the
sense that sheet macros are intended primarily for even handlers.

-John Coleman

Otto Moehrbach wrote:
John
Correct me if I'm wrong with this, but all macros in a sheet macro are
event macros. That raises the question in my mind "Why would I want to run
an event macro at any time other than at the occurrence of the event?
Apparently, you wanted to do so at one time so maybe all sheet macros are
not event macros? Otto
"John Coleman" wrote in message
oups.com...
To add to what Tom said (since it is an issue that caused me some grief
the first time I used Application.run) if your macros reside in a Sheet
module as opposed to a general code module then it seems to be the case
that you need to qualify the macro names - even if you are calling them
from the same sheet. For example, in Sheet1 the following works fine:

Sub Greet(who As String)
MsgBox "Hello " & who
End Sub

Sub CallIt()
Application.Run "Sheet1.Greet", "World"
End Sub

But replacing "Sheet1.Greet" by "Greet" yields "Run time error '1004':
application or object defined error" (or words to that effect).

HTH

-John Coleman

Otto Moehrbach wrote:
Excel XP, Win XP
I have a number of macros, say Able, Baker, Charlie, etc.
I have a variable named MacroToRun as string.
I have code that sets the value of MacroToRun as, say, "Charley".
I want to run the macro whose name is in the variable MacroToRun.
How do I call that macro?
Thanks for your time. Otto

Thanks John. I've learned something again. There is no end to this
learning thing. Otto
"John Coleman" wrote in message
ups.com...
Otto,
General subs and functions can be in a sheet macro module. Often
event handlers are easier to understand if you modularize and place
some of the logic in separate subs or functions - and if they are only
to be used in Sheet1 it makes some sense to leave these subs and
functions in the Sheet1 module (though there would be nothing wrong
with placing them in a general code module). Also - I tend to use event
handlers, especially button click events, as little more than
dispathers which call an appropriate sub. In this context
Application.Run could have a useful role inside of a sheet module. You
are thus technically wrong about sheet macros but you are right in the
sense that sheet macros are intended primarily for even handlers.

-John Coleman

Otto Moehrbach wrote:
John
Correct me if I'm wrong with this, but all macros in a sheet macro
are
event macros. That raises the question in my mind "Why would I want to
run
an event macro at any time other than at the occurrence of the event?
Apparently, you wanted to do so at one time so maybe all sheet macros are
not event macros? Otto
"John Coleman" wrote in message
oups.com...
To add to what Tom said (since it is an issue that caused me some grief
the first time I used Application.run) if your macros reside in a Sheet
module as opposed to a general code module then it seems to be the case
that you need to qualify the macro names - even if you are calling them
from the same sheet. For example, in Sheet1 the following works fine:

Sub Greet(who As String)
MsgBox "Hello " & who
End Sub

Sub CallIt()
Application.Run "Sheet1.Greet", "World"
End Sub

But replacing "Sheet1.Greet" by "Greet" yields "Run time error '1004':
application or object defined error" (or words to that effect).

HTH

-John Coleman

Otto Moehrbach wrote:
Excel XP, Win XP
I have a number of macros, say Able, Baker, Charlie, etc.
I have a variable named MacroToRun as string.
I have code that sets the value of MacroToRun as, say, "Charley".
I want to run the macro whose name is in the variable MacroToRun.
How do I call that macro?
Thanks for your time. Otto