VBA: Using `Application.Ontime` to force code execution

Question

This is a followup on this question/answer/comment...

I'm trying to force code execution of my first procedure (a procedure without input variables) by using Application.Ontime to start a second procedure (a procedure with input variables). Since Application.Ontime can only "call" a macro/procedure which doesn't allow for input variables I must use a global variable to pass on the input parameter to the second macro/procedure called by Application.Ontime (see the frist, working example). It works but I rather don't work with a global variable.

I thought I'm smart and I use a "pseudo-macro in-between" which only exists to forces the code from the first procedure to get executed before the second procedure starts. But it doesn't work: the code execution isn't completed by the time the second procedure gets started. Is the problem that I use an empty pseudo-macro? I tried different commands in the pseudo macro, but it didn't make any differences. Any ideas? (see second, not working example)

Frist example, which works (code gets executed before the macro):

Public gloInputVariable as Variant

Sub procedureWithoutInputparameters

    'Do something in this procedure
     inputVariable = "something"

    'force code execution
        'workaround with global variable instead of inputparameter (macros don't have input parameters)
        gloInputVariable = "something"
        'start the next procedure as a macro (basicaly a procedure without input paramters)
        Application.OnTime Now + TimeSerial(0, 0, 1), "WorkaroundMacroInsteadOfProcedureWithInputParameters"

    End Sub

Sub WorkaroundMacroInsteadOfProcedureWithInputParameters()
    'get the input parameter through a global variable
    variable1 = gloInputVariable
    'do something
    End Sub

Second example, which doesn't work (code from the first procedure is not completely executed before the second procedure starts):

Sub procedureWithoutInputparameters

    'Do something in this procedure
     inputVariable = "something"

    'force code execution through pseudomacro (this doesn't work)
        'force execution through macro
        Application.Ontime Now + TimeSerial(0, 0, 1), "Pseudomacro"
        'run next second procedure procedure
        procedureWithInputparamters (inputVariable)

    End Sub

Sub PseudoMacro()
    'do nothing, pseudo-macro is only here to force code execution before starting the second procedure with input parameters
    End Sub

Sub procedureWithInputparamters(strSomeInputString as String)
    'get the input parameter "directly"
    variable1 = strSomeInputString 
    'do something        
    End Sub

It seems like you need to call procedureWithInputparamters inside the PseudoMacro. — DecimalTurn, Sep 18 '19 at 15:54
I realize that you won't be able to pass your local variable that way but VBA needs to stop running for a brief instant in order to let the OS or the Excel Application complete want you want it to do. In the example you gave, VBA doesn't stop before running the `procedureWithInputparamters` and that's why it doesn't work. — DecimalTurn, Sep 18 '19 at 16:25

DecimalTurn · Accepted Answer · 2019-09-19T15:48:21.077

As discussed in your previous question, Application.OnTime is used to schedule a procedure to run in the future, so we can use it at our advantage to let the OS or Excel update something that can't be updated while VBA is running.

Note that it's the fact that our second procedure runs after the OS/Excel has regained control that make sure this works. You can see it visually in the diagram below where the red section is when VBA is not running:

In your example, you call procedureWithInputparamters (your second procedure) from the first macro which means there is no interruption of VBA before that second macro runs. This means the OS can't update even if you ran a Application.OnTime command.

_{*Image not to scale}

That's because it's not the fact that you ran Application.OnTime that matters, what matters is that VBA stops running for a brief instant before your second procedure runs. Which doesn't happen in that second case using the PseudoMacro.

An alternative solution

If you need to pass arguments to the procedure you are calling with Application.OnTime, it seems like this question has the answer you're looking for.

As suggested by Holger Leichsenring's answer, you can pass a string as the argument of the function by adding an apostrophes at the beginning and the end of the value you pass; having a space after the name of your procedure; and surrounding your string argument by quotation marks ( Chr$(34) ).

E.g.

Application.OnTime Now + TimeSerial(0, 0, 1), "'procedureWithInputparamters " & Chr$(34) & inputVariable & Chr$(34) & "'"

Thanks for the explanation, I figured as much. In both examples, the first procedure ends after the second one. So something must force the code to get executed in the first example (or in your words, control is given back to the OS / VBA stops running). Do you know exactly why this is the case - what exactly goes on "internally"? — Albin, Sep 18 '19 at 20:59
I wonder if it's the fact that there is additional code after the Application.OnTime and before the `end`-command of the first procedure in the second example. It should be simple enough to test it. — Albin, Sep 18 '19 at 21:00
I'm not sure of exactly what goes on internally or under the hood, but my guess is that the fact that your VBA code is accessing the clipboard could prevent other applications or processes to access it until the complete VBA stack is executed in order to avoid conflicts. — DecimalTurn, Sep 18 '19 at 21:21

VBA: Using `Application.Ontime` to force code execution

1 Answers1

An alternative solution