How do you call a procedure that has parameters on the Application.onTime function?

Question

How do I call a procedure on the Application.onTime function when the procedure being called has a parameter?

Sub popUpReminder(msg As String)
    MsgBox (msg)
End Sub

Sub schedulePopUp()
    Application.OnTime Now + TimeValue("00:00:05"), "popUpReminder " & """" & "Hello" & """"
End Sub

Answer 1

Passing a Method With Parameters as a String

• Enclose the whole procedure with parameters inside single quotes/Chr(39)
• Add space after parameter
• Wrap String values inside double quotes/Chr(35)
• Wrap Dates with hashes/Chr(35)

↑These Rules↑ work for both Application.OnTime, Application.Run or Control.OnAction.

You could do all the above every time or you could just write a subroutine to do it for you. Or you could just copy and paste the one I wrote:

Sub Test()
    Application.OnTime Now + TimeValue("00:00:01"), WrapMacro("popUpReminders ", 1, 2, "3", "Tin", "Man", Date)
    Application.Run WrapMacro("popUpReminders", 1, 2, "3", "Tin", "Man", Date)
    ActiveSheet.Buttons.Add(0, 0, 72, 72).OnAction = WrapMacro("popUpReminders ", 1, 2, "3", "Tin", "Man", Date)
End Sub

Function WrapMacro(ProcName As String, ParamArray Args() As Variant) As String
    Dim n As Long
    For n = 0 To UBound(Args)
        If TypeName(Args(n)) = "String" Then          'Surround with Double Quotes
            Args(n) = Chr$(34) & Args(n) & Chr$(34)
        ElseIf TypeName(Args(n)) = "Date" Then        'Surround with Hashes
            Args(n) = Chr$(35) & Args(n) & Chr$(35)
        End If
    Next

    WrapMacro = Chr$(39) & ProcName & Space(1) & Join(Args, ",") & Chr$(39)
End Function

Sub popUpReminders(ParamArray Args() As Variant)
    MsgBox Join(Args, vbNewLine)
End Sub

Answer 2

I hate to say this but you need even more quotes :-) However, there's no real need to split out the internal quotes into separate strings, you can just use:

Application.OnTime Now + TimeValue("00:00:05"), "'popUpReminder ""Hello""'"

Perhaps a more viewable example would be a function that takes a single integer. That would be:

Application.OnTime Now + TimeValue("00:00:05"), "'checkAnswer 42'"

You can see there that the entire statement is quoted.

Answer 3

You need a single quote on either side and a slight reformatting:

Sub popUpReminder(msg As String)
    MsgBox (msg)
End Sub

Sub schedulePopUp()
    Application.OnTime Now + TimeValue("00:00:05"), "'popUpReminder(""Hello"")'"
End Sub

Answer 4

Another variation for repeating events

Sub Test()
For i = 5 To 12
Application.OnTime TimeValue(Format(i, "00") & ":00:00"), "'DoCopy " & i & "'"
Next
End Sub