Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Multiple Variable Arguments to Application.OnTime

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...