Application.OnTime Specifying both workbook and arguments

[m1chael]

Hi,

I'm looking to run Application.OnTime and specify both the workbook and some arguments. I can get them to work on their own, but not together.

For example, the following code works if "MacroToBeCalled" subroutine is in the same workbook where I set Application.OnTime.

  Application.OnTime Now(), "'MacroToBeCalled ""String To Send""'"

Also, the following code works if "MacroToBeCalled" subroutine is in the workbook called "Test File.xlsm", which is different to where I'm running this line of code.

  Application.OnTime Now(), "'Test File.xlsm'!MacroToBeCalled"

However, I can't find a way of sending parameters. I'm looking for something like the following, although this does not work.

  Application.OnTime Now(), "''Test File.xlsm'!MacroToBeCalled ""String To Send""'"

What is the format of the string I need to provide for this to work, if it is possible at all?

If you need more detail, I have two workbooks, say one is called A and one is called B. Both A and B contain a subroutine called MacroToBeCalled. I am using a button in workbook A, which uses Application.Run to run a macro in workbook B called SetOnTimeToRunMacro. What I want is for SetOnTimeToRunMacro subroutine in workbook B to call Application.OnTime to run the MacroToBeCalled in workbook B. However, when the Ontime is actually trigged, it's running MacroToBeCalled in workbook A instead of B, even though the Ontime is set in workbook B. If I remove the parameter and specify the workbook, then it works in workbook B.

 

[m1chael]

Cross posted here: https://stackoverflow.com/questions...th-workbook-and-macro-name-and-send-arguments

 

[m1chael]

I've discovered the answer to my own question. Will post here in case anyone else has the same issue:

The correct format to use is:

<code>Application.OnTime Now(), "'Test File.xlsm'!'MacroToBeCalled ""String To Send""'"

</code>The part before the ! mark, and the part after, should have single quotes.