Passing arguments to OnTime and OnAction macros
You are hopefully familiar with the syntax for calling macros using Application.OnTime or object.OnAction. But just in case you need a reminder, here's the syntax for OnTime, from the VBA help files:
expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
And here's what it could look like in practice, if you wanted to schedule a macro to run 2 seconds from now:
Application.OnTime Now + TimeValue("00:00:02"), "MyMacro"
(I'll use OnTime in all the examples below, but all the principles apply equally well to OnAction.)
Passing arguments
What if you want to pass arguments to MyMacro? You cannot just use "MyMacro(42)" like you would do when calling your macro in the normal course of your code - that will not work.
The correct syntax is not complicated, but it is far from intuitive, with several layers of single and double quotes. For example:
'MyMacro takes a Single numeric argument:
Application.OnTime Now + TimeValue("00:00:02"), "'MyMacro 42'"
First of all, the whole procedure argument is enclosed in double quotes, just like in our first example with no arguments.
Next, inside that, is a set of single quotes. They're there for no clear reason that I can see; that's just the way it works. Inside those single quotes is your macro name, followed by the arguments. That's all there is to it!
Several arguments
If there are several arguments, they should be separated by commas, like this:
'MyMacro takes two numeric arguments:
Application.OnTime Now + TimeValue("00:00:02"), "'MyMacro 42, 13'"
String arguments
If the arguments are strings, they need double quotes around them, as all strings do. But since we're already inside a string, the double quotes need to be doubled:
'MyMacro takes one String argument:
Application.OnTime Now + TimeValue("00:00:02"), "'MyMacro ""Hello!""'"
What about variables?
Calling a macro with hard-coded parameters is rarely useful... if you wanted to do that, then you could just as well hard-code the numbers inside MyMacro itself, and save yourself all this trouble.
In reality it's more likely that you'll want to get the argument from a variable. What you then need is to build the string piece by piece - and make sure you don't forget those double double quotes, or the space after the name of the procedure:
'MyMacro takes one String argument:
strText = "Hello!"
Application.OnTime Now + TimeValue("00:00:02"), "'MyMacro """ & strText & """'"
Finally, you may want MyMacro to get its parameters from a public variable, so the parameter values aren't fixed now, but only when the macro actually runs. In that case you don't need those double double quotes. Note that the variable needs to be public, or MyMacro won't be able to find it.
'MyMacro takes one String argument:
g_strText = "Hello!"
Application.OnTime Now + TimeValue("00:00:02"), "'MyMacro g_strText'" 'comment
