Multiple Variable Arguments to Application.OnTime

solemn picture solemn · Jul 15, 2015 · Viewed 22k times · Source

I am working on a data acquisition frontend for Excel 2010. I can't figure out the syntax for passing multiple local variable arguments to Application.OnTime.

http://markrowlinson.co.uk/articles.php?id=10 provides a good example for a single variable as an argument, but the explanation on how to extrapolate this to multiple variables is not clear to me.

Does anyone have a concise explanation of the correct nesting of " and ' characters when passing multiple local variable arguments?

Edit: Code example would be like this: Application.OnTime Now + TimeSerial(0, 0, 5), "'runScheduledReport """ & iArg1 & "","" & iArg2 & "" "" & iArg3 & "" ""'". I understand that we're using the double quote as an escape character within the string, but can't really figure out the ordering of the strings being passed.

Answer

Holger Leichsenring picture Holger Leichsenring · Jul 16, 2015

you have to consider the following constraints:

  • the macro you want to call has to reside in a module. When you want to call it from another workbook it has to be public.
  • you cannot use brackets for calling the macro like you would do with a function or a sub with parameters most probably. When using brackets Excel will complain that macro doesn't exist
  • I didn't try a function, but anyway there is nobody who can work with the return value, so define your method as a sub.
  • you have to use aposthophs to encapsulate the macro name
  • you have to use quotes to encapsulate string and date values , either with chr$(34) (reminds me to old times) or just double the quotes
  • you can pass over integers without quotes, I didn't try Doubles
  • separate arguments by a comma
  • the order of the arguments must match the order of the arguments in your method

Find attached the code:

Option Explicit

Sub Test()
    Dim strTest1 As String
    Dim strTest2 As String

    strTest1 = "This is test1"
    strTest2 = "This is test2"

    Application.OnTime Now + TimeSerial(0, 0, 1), "'CallMeOnTime """ & strTest1 & """,""" & strTest2 & "'"
    Application.OnTime Now + TimeSerial(0, 0, 1), "'CallMeOnTime " & Chr$(34) & "Test" & Chr$(34) & "," & Chr$(34) & "Test" & Chr$(34) & "'"
    Application.OnTime Now + TimeSerial(0, 0, 1), "'CallMeOnTime2'"
End Sub
Public Sub CallMeOnTime(strTest1 As String, strTest2 As String)
    MsgBox ("test1: " & strTest1 & " test2:" & strTest2)
End Sub

Public Sub CallMeOnTime2()
    MsgBox ("CallMeOnTime2")
End Sub