Multiple Variable Arguments to Application.OnTime

T

3

11

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.

Translocate answered 15/7, 2015 at 19:57 Comment(3)

That linked page seems pretty comprehensive - why not add your exact use case and existing code to your question, so there's something concrete to work with? – Foxworth 15/7, 2015 at 20:7

Basically, your macro needs to be a string, so if you have multiple arguments, you surround the whole thing in single quotes. If you need to use full quotes in your arguments, just use 2 instead of 1. "'MyMacro ""StringParameter"",""Other String Parameter""'" – Yuma 15/7, 2015 at 20:54

Beginning = "' End = '" Everything else just think of separately. Double quotes = Single quotes. – Yuma 15/7, 2015 at 20:56

W

12

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

Wifely answered 16/7, 2015 at 21:11 Comment(2)

Thanks (+) for fully working example! But you wrote "When you want to call it from another workbook it has to be public" - not ony so! If you want to set the parameters the called function has to be public even in the same module (I'd spent 2 hours to undersand that) And next... The text Now + TimeSerial(0, 0, **1**) is somewhat redundantly. I know - all write this. But enough is Now + TimeSerial(0, 0, **0**). Moreover - this example works for me even with such: Application.OnTime Now, "'CallMeOnTime """ & strTest1 & """,""" & strTest2 & "'" – Tetrabranchiate 15/1, 2018 at 13:4

Thank You @Holger. Oddly your method does not work, if the option Schedule:=false is set. Excel throws an error in such cases. – Arber 29/3, 2022 at 9:21

D

4

Just wanted to add an additional example which I found helpful, with reference to this post on MrExcel.

Application.OnTime with multiple arguments of different types (String and Integer)

Dim testName As String
Dim counter As Integer

...

' String then Integer argument
Application.OnTime Now + TimeValue("00:00:02"), "'TestSub """ & testName & """, " & counter & " '"

' Integer then String argument
Application.OnTime Now + TimeValue("00:00:02"), "'SubTest " & counter & ", """ & testName & """ '"

The only difference from this answer is the inclusion of the counter Integer, which is handled slightly differently from a String.

Devout answered 4/7, 2017 at 10:19 Comment(2)

would be nice if you can state the difference to Holger's answer more explicitly. Then it would be worth a +1 – Welloff 19/9, 2019 at 12:50

The inclusion of an Integer in the string argument is a minor addition to Holger's answer. As it's handled slightly differently, I thought it was worth showing explicitly. – Devout 25/9, 2019 at 9:57

W

2

Huhlo,

I have fought with the tricky syntax for arguments to Application.OnTime ( or Application.Run, which is similar) every time I have needed it. I have often come here, as well as arrived a few times at the other links referenced here. As often they almost, but did not quite, got me there.

So I have spent some time making myself some worked examples to reference in the future, and I have also convinced myself finally that I understand what is going on.

So I am sharing my solutions , and finally I think I can have a stab at answering the original question regarding ..concisely explaining / justifying the syntax.. ..

I am deliberately giving very full explicit code lines for two reasons:-

_ 1. Its easy to simplify it to the more usual shortened version if you only need that, but going the other way , from the more common simplified form to the full explicit form, should you need that, is quite hard.

_ 2.Showing the full explicit code line syntax helps with my attempt at explain the syntax, and so is needed in answering the question fully.

The full explicit syntax would be needed , for example , to ensure the corrects file were opened, when we want to trigger a macro in a closed workbook. ( In such a case, the closed workbook would be opened. The VBA Application.OnTime code line will do this opening, provided it has the full explicit form )

I am using 2 example files. If you want to try my demos, then the first should be opened , the second can be closed or open , but the second should be in the same folder. ( The reason why it needs to be in the same folder is just for simplified demonstration, - I have organised that demonstration macros will look for the closed workbook in the same folder. In the practice, the closed workbook can be anywhere if you replace exactly this bit , ( including the first " ) , with the full path and file name of the closed workbook

" & ThisWorkbook.Path & "\" & "UverFile.xls

So you would replace that last bit with something of the form:

C:\Elston\Desktop\MyFolder\UverFile.xls

A complete code line would then have a form something like this:

Application.OnTime Now(), "'C:\Elston\Desktop\MyFolder\UverFile.xls" & "'"      &      "!'Modul1.MacroUndermacroInUverFile 465, ""25""'"

.

Open workbook - MainFile.xls : https://app.box.com/s/prqhroiqcb0qccewz5si0h5kslsw5i5h

Module “Modul1” in MainFile.xls

Option Explicit
' Public variable code section
Private Pbic_Arg1 As String
Public Pbic_Arg2 As Double




Sub MainMacro()    '    https://mcmap.net/q/982365/-multiple-variable-arguments-to-application-ontime/31464597       http://markrowlinson.co.uk/articles.php?id=10
Rem 1
                                                                                                                                                                                                                          Debug.Print "Rem 1" & vbCr & vbLf & "This workbook module, single arrgument"
' This workbook module, single argument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.UnderMainMacro 465'": Debug.Print "!'Modul1.UnderMainMacro 465'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.UnderMainMacro ""465""'": Debug.Print "!'Modul1.UnderMainMacro ""465""'"
 Application.OnTime Now(), "'Modul1.UnderMainMacro  465'" '  --- more usual simplified form. In this case I nned the extra  Modul1.  because Sub UnderMainMacro( ) is private
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "UverFile module, single argument"
' UverFile module, single argument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"           &          "!'Modul1.MacroInUverFile 465'": Debug.Print "!'Modul1.MacroInUverFile 465'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"          &          "!'Modul1.MacroInUverFile ""465""'": Debug.Print "!'Modul1.MacroInUverFile ""465""'"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "Thisworkbook module, multiple arguments"
' Thisworkbook module, multiple arguments
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.UnderUnderMainMacro 465, 25'": Debug.Print "!'Modul1.UnderUnderMainMacro 465, 25'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.UnderUnderMainMacro 465, ""25""'": Debug.Print "!'Modul1.UnderUnderMainMacro 465, ""25""' "
 Application.OnTime Now(), "'UnderUnderMainMacro 465,  25 '" '  --- more usual simplified form. I don't even need the extra  Modul1.  because it is not private
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "UverFile module, multiple argument"
' UverFile module, multiple argument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"          &          "!'Modul1.MacroUnderMacroInUverFile 465, 25'": Debug.Print "!'Modul1.MacroUnderMacroInUverFile 465, 25'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"          &          "!'Modul1.MacroUndermacroInUverFile 465, ""25""'": Debug.Print "!'Modul1.MacroUndermacroInUverFile 465, ""25""'"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "mess about with argument positions"
' mess about with argument positions
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.UnderUnderMainMacro      465   ,     ""25""          '": Debug.Print "!'Modul1.UnderUnderMainMacro      465   ,     ""25""          '"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "This workbook first worksheet code module, single arrgument"
' This workbook first worksheet code module, single arrgument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &           "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWsCodeModule 465'": Debug.Print "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWcCodeModule 465'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWsCodeModule ""465""'": Debug.Print "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWcCodeModule ""465""'"
                                                                  Debug.Print vbCr & vbLf & "UverFile  first worksheet code module, single arrgument"
' UverFile  first worksheet code module, single arrgument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"          &          "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModule 465'": Debug.Print "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModule 465'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"          &          "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModule ""465""'": Debug.Print "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModule ""465""'"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "This workbook first worksheet code module, multiple arguments"
' This workbook first worksheet code module, multiple arguments
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWsCodeModuleMultipleArguments 465      ,  ""25""         '": Debug.Print "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWcCodeModuleMultipleArguments 465      ,  ""25""         '"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWsCodeModuleMultipleArguments      ""465""   ,   25    '": Debug.Print "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWcCodeModuleMultipleArguments      ""465""   ,   25    '"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "UverFile  first worksheet code module, Multiple  arrgument"
' UverFile  first worksheet code module, Multiple  arrgument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"          &          "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModuleMultipleArguments   465   ,    ""25""       '": Debug.Print "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModuleMultipleArguments   465   ,    ""25""       '"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"          &          "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModuleMultipleArguments ""465""   ,    ""25""  '": Debug.Print "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModuleMultipleArguments ""465""   ,    ""25""  '"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "Doubles do not have to be in quotes either  ' This workbook module, double argument arrgument"
' Doubles do not have to be in quotes either  ' This workbook module, double argument arrgument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"           &          "!'Modul1.DoubleCheck 465.5   , ""25.4""    '": Debug.Print "!'Modul1.DoubleCheck 465.5   , ""25.4""    '"

Rem 2 Variables
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "Rem 2 Variables" & vbCr & vbLf & "'2a)  ""Pseudo""  variables use"
'2a) "Pseudo" variables use
Dim Arg1_str465 As String, Arg2_Dbl25 As Double
 Let Arg1_str465 = "465.42": Let Arg2_Dbl25 = 25.4
 ' Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.DoubleCheck  Arg1_str465   ,   Arg2_Dbl25    '": Debug.Print "!'Modul1.DoubleCheck  Arg1_str465   ,   Arg2Db_l25    '"  ' This code line will not work, that is to say it will not find the varables and take  0  values when VBA later runs the Scheduled macro,  Sub DoubleCheck( )
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.DoubleCheck   """ & Arg1_str465 & """   ,   """ & Arg2_Dbl25 & """    '": Debug.Print "!'Modul1.DoubleCheck  """ & Arg1_str465 & """  ,   """ & Arg2_Dbl25 & """  '"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"           &           "!'Modul1.DoubleCheck   """ & Arg1_str465 & """   ,   " & Arg2_Dbl25 & "    '": Debug.Print "!'Modul1.DoubleCheck  """ & Arg1_str465 & """  ,   " & Arg2_Dbl25 & "  '"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "'2b) Real varable use"
'2b) Real varable use
 Let Modul1.Pbic_Arg1 = "465.42": Let Pbic_Arg2 = 25.4
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.DoubleCheck   Modul1.Pbic_Arg1     ,   Pbic_Arg2    '": Debug.Print "!'Modul1.DoubleCheck  Modul1.Pbic_Arg1  ,   Pbic_Arg2  '"

 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.DoubleCheck Modul1.Pbic_Arg1, Pbic_Arg2'"
                                                                                                                                 ''      Debug.Print Pbic_Arg2 '' This gives 999.99 in  Debug F8  mode , 25.4 in  normal  run

Rem 3 ByRef check
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.ByRefCheck'"
 Application.OnTime Now() + TimeValue("00:00:00"), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.ByRefCheck'"
 Application.OnTime Now() + TimeValue("00:00:01"), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          &          "!'Modul1.ByRefCheck'"
End Sub
Private Sub UnderMainMacro(ByVal Nmbr As Long)
 MsgBox prompt:="Arg1 is   " & Nmbr
End Sub
Sub UnderUnderMainMacro(ByVal Nmbr As Long, ByVal NuverNmbr As Long)
 MsgBox prompt:="Arg1 is  " & Nmbr & ", Arg2 is  " & NuverNmbr
End Sub
Sub DoubleCheck(ByVal DblNmr1 As Double, ByRef DblNmr2 As Double) ' provided the signature line is declared appropriately, all number argument types dont have to be in  ""
 MsgBox prompt:="Arg1 is  " & DblNmr1 & ", Arg2 is  " & DblNmr2
 Let DblNmr2 = 999.99
End Sub

(That above is the main module from which all macros are run)

Worksheets Class module of first worksheet “Tabelle1” in MainFile.xls

Option Explicit
Sub InLisWbFirstWsCodeModule(ByRef Nmbr As Long)
 MsgBox prompt:="Arg1 is   " & Nmbr
 Let Nmbr = 999
End Sub
Sub InLisWbFirstWsCodeModuleMultipleArguments(ByVal Nmbr As Long, ByVal NuverNmbr As Long)
 MsgBox prompt:="Arg1 is  " & Nmbr & ", Arg2 is  " & NuverNmbr
End Sub

.

. .

Closed workbook - UverFile.xls : https://app.box.com/s/u7r2jw79m8ou70otn7xcxced2qkot4w4

Module “Modul1” in UverFile.xls

Option Explicit
Private Sub MacroInUverFile(ByVal Nmbr As Long)
 MsgBox prompt:="Arg1 is   " & Nmbr
End Sub
Sub MacroUnderMacroInUverFile(ByVal Nmbr As Long, ByVal NuverNmbr As Long)
 MsgBox prompt:="Arg1 is  " & Nmbr & ", Arg2 is  " & NuverNmbr
End Sub

Worksheets Class module of first worksheet “Tabelle1” in UverFile.xls

Option Explicit
Sub InUverFileFirstWsCodeModule(ByVal Nmbr As Long)
 MsgBox prompt:="Arg1 is   " & Nmbr
End Sub
Sub InUverFileFirstWsCodeModuleMultipleArguments(ByVal Nmbr As Long, ByVal NuverNmbr As Long)
 MsgBox prompt:="Arg1 is  " & Nmbr & ", Arg2 is  " & NuverNmbr
End Sub

.

I have tried to give a good spread of working examples, which I found useful to then use as a template to modify to exactly my needs.

Here is the explanation to how things work , which makes the syntax more understandable:

First the nested ' This is generally how VBA handles making any spaces be taken as literal spaces, ( rather than , for example, mistaking them as separating arguments). You will see that in the codes, as I have posted here in the forum post, I have done some exaggerated spaces in all code lines which helps to split up the two main parts of the code

the LHS , which in a simplified / shortened use would be typically be left out

and

the RHS , most of which is always needed . (Most likely xou may only see the macro name and the arguments . The extra module code name allows you to use macros in any modules , ( regardless of if they are Private or Public )

Just to make that clear, I have some exagerated spaces in the code windows above either side of one of the &s , so pseudo I have

 "---------LHS-------------"       &         "---------RHS------------------"

or like, pseudo

    "String bit containg full path and file name what you mostly don't use"            &          "String bit containing the macro name and the arguments like you more typically see"

Those exaggerated extra spaces either side of that & will vanish if you copy and paste that code into the VB editor code window. If, however, I were to add spaces within the path string on the LHS, such as changing a file name from UverFile.xls to Uver File.xls , then as perhaps expected, the spaces will not change when posting into the VB Editor code window. This is because the enclosing ' ' is doing its job of ensuring that all is taken as literally as it is given.

On the RHS we need also that the information is taken exactly as we give it. My geuss is that, this string part needs to be stored into a buffer from when it is then retrieved and pseudo physically put in later by VBA when the scheduled macro is run. This is why I can add some rogue spaces, as I have done in the code section named ' mess about with argument positions , and in a few places in code lines after. This modification is also not changed when you post into the VB Code window. This helps us to understand the nested " "

the nested "" in the variable arguments bit.

This is much less difficult then a lot of literature suggests.

The only time you really need those enclosing quote pair is if you are giving string values in the argument. That is generally the case in VBA code lines, the enclosing quote aroiund a string indicating that a string is being given. ( Since you are already inside a string, then each of the quotes need to be doubled, as is the standard VBA syntax required to get a single quote to appear in the final string as is "seen" by VBA). If you are using variables, rather than hard coding, then you never need this next complicated syntax which I am showing, ( provided you have your variables at the top of a module, outside any subroutine ). What I am saying is, that the following complicated argument syntax , which one often sees, is , in most cases, more complicated than needed

""" & Arg1 & """ ,  """ & Arg2 & """

In most cases, that complicated form above can be reduced to this sort of form below

Arg1 ,  Arg2

To use that simplified form, the variables must be outside the macro with the scheduling Application.OnTime code line, and it must be at the top of the code module, or else, the scheduled macro which is to be set off by VBA later , won’t know where to get the variables from. ( If the variables are not in the same module as the scheduling module, then they must be declared as Public. It is best to referrence them explicitly, like Module2.Arg1 or Sheet1.Arg1 or ThisWorkbook.Arg1 etc )

So do not really “need” that complicated syntax, provided you use “module level” variables.

But if you use that complicated syntax, it will have the effect of placing the value from the variable in the final argument string that VBA puts into the code line it write to run the scheduled macro later. This would have the effect of that if you use that syntax, and your variables are local, ( that is to say they are within the scheduling macro ) , then you might be fooled into thinking that you , ( that is to say VBA in the scheduled macro later ), are using the variables.

In fact what you are doing is that you are hard coding with values into the string that will finally be used by VBA later in the scheduled macro. I suppose you might say that is using variables within the scheduling macro, at least from the practical point of use. But understanding what is actually going on, helps , I think, to see where the sometime daunting syntax comes from. The point is that in such a case you are not really putting variables in the argument. What you are actually dong is using variables within the scheduling macro to hard code the arguments

In my demo macros, I refer to that way of using the scheduling macro variables as "Pseudo" variables use.

Further more, the point that Nick P was making in his answer, is that 4 of those quotes around each variable in that very complicated argument syntax, are there to give the typical required finally seen double enclosing " " pair around a string value. If one of those variables in the example, for example Arg2 , is a number, then even for the case of using the “trick” to make it appear that you are using variable within the scheduling macro, you can do away with some of those quotes, in particular the ones giving finally as seen by VBA, the enclosing " " pair, reducing it to

""" & Arg1 & """ ,  " & Arg2 & "

That is what Nick P was demonstrating.

_.____________________

Examining the right hand side syntax for macro name and arguments.

In all the coding I have a Debug.Print after each Application.OnTime code line. What this is showing is the actual RHS part of the string that VBA uses later when running the scheduled macro. So that is showing the part containing the macro name and the arguments. This helps to show the main point I am trying to get across.

For example, the string in what I refer to as the "Pseudo" variables use , looks like this:

!'Modul1.DoubleCheck  "465.42"  ,   "25.4"  '

Or, as noted, if a variable, for example, the second is a number , then you can also use this

!'Modul1.DoubleCheck  "465.42"  ,   25.4  '

For what I call the Real variable use, the string “seen” must actually use the variable names

!'Modul1.DoubleCheck  Modul1.Pbic_Arg1  ,   Pbic_Arg2  '

Just to clarify that Last code line above. The sub routine being scheduled is Sub DoubleCheck( ) which I have located in my code module with the code name Modul1

Also in that same code module are placed at the top of the module , declarations for the variable, Pbic_Arg1 and Pbic_Arg2 . Pbic_Arg1 is Private, and Pbic_Arg2 is Pubic

If you try my coding out running from the VB Editor in step ( F8 ) mode , whilst you have the Immediate Window open , then I think that will help make everything clear

Summary

At the end of the day, the key to getting the syntax correct , and to understanding it , is as follows:

You must arrange it such that what VBA “has”, ( which you can check via a Debug.Print of the string you are giving ) needs to have on the right hand side a similar form to how you might manually write in arguments in a code line to call a sub routine taking in arguments.

You can add a few extra spaces between multiple arguments and the separating comer , just as you might do carelessly when typing in manually a series of arguments in a typical VBA Call code line. Presumably, VBA later, when it uses exactly your given string, it does something similar to what happens when you physically write or paste such things in, the result of which is that those extra spaces get removed.

The point of the enclosing ' ' is to indicate to VBA to take literally exactly as you have written it. In my explicit code lines we need that for both the LHS and the RHS. More typically the LHS is ommited.

Any use of a complicated combination of many double or triple " pairs is more of a trick to give you a way to effectively use variables that are within the scheduling macro , in the scheduling Application.OnTime code line.

If your variables are in a code module outside of any sub routine, then the variable syntax is much simplified. In this case you do not actually need any quotes within the main string, not even if the a variable type is string.

( The complete second argument of the Application.OnTime , which relates to the scheduled macro and its arguments , always needs to be enclosed in a quote pair. That is simply how the Application.OnTime has been written. ( That is very useful, since you can then build up the string with variables , rather than being restricted to hard coding ) )

Alan

Ref

https://groups.google.com/forum/?hl=es#!msg/microsoft.public.excel.programming/S10tMoosYho/4rf3VBejtU0J

https://www.mrexcel.com/board/threads/calling-a-procedure-with-parameters.81724/#post398494

http://markrowlinson.co.uk/articles.php?id=10

http://www.excelfox.com/forum/showthread.php/2404-Notes-tests-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11870&viewfull=1#post11870

P.S. @ Holger Leichsenring - Hi . I think the apostrophes must enclose the macro name AND the arguments. Any number types can be passed without quotes. The macro you want to call can reside in any module, in any workbook, (open or closed) , and need not be Public. ( My geuss is that Application.OnTime uses the same wiring as Application.Run , which has the advantage, over simple Calling a sub , that it will run both Public and Private subs ( Difference between Calling a Sub and Application.Run ) )

Gruß, Alan

Weymouth answered 19/1, 2020 at 17:0 Comment(0)

Recommended topics

Hot tags