Multiple Variable Arguments to Application.OnTime
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 gone here https://stackoverflow.com/questions/...ication-ontime , as well as arrived a few times at the other links referenced below in the second post. As often they almost, but not quite, got me there.
I spent some time making myself some worked examples to reference in the future, and 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 thel 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, the first would 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
In other words, you would replace that last bit with something like …_
C\Elston\Desktop\MyFolder\UverFile.xls
_ .. giving a complete code line of this sort of form:
Code:
Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'" & "!'Modul1.MacroInUverFile ""465""'": Debug.Print "!'Modul1.MacroInUverFile ""465""'"
Open workbook - MainFile.xls : https://app.box.com/s/prqhroiqcb0qccewz5si0h5kslsw5i5h
Module "Modul1" in MainFile.xls
(This is the main module from which all macros are run)
See here: http://www.excelfox.com/forum/showth...ll=1#post11861
http://www.excelfox.com/forum/showth...2070#post12070
Worksheets Class module of first worksheet "Tabelle1" in MainFile.xls
Code:
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
Code:
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
Code:
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 I have done some exaggerated spaces in all code lines which helps to split up
_ 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 are likely typically to 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 Pubic )
Just to make that clear, I have some exaggerated spaces in the code windows above either side of one of the `&`s , so pseudo I have
Code:
"---------LHS-------------" & "---------RHS------------------"
or like, pseudo
Code:
"String bit containing 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 will vanish if you copy and paste that code into the VB editor code window. If I 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. 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. This needs to be stored into a buffer from when it is then retrieved and pseudo physically put in. This is why I can add some rogue spaces, as I have done in the code section named ' mess about with argument positions. 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 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 indicating that a string is being given. ( Since you are already inside a string, then the double quotes need to be doubled, as is standard VBA syntax).
If you are using variables, rather than hard coding, you never need this following often seen complicated syntax, ( 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 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
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, 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 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 calling 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.
In my demo macros, I refer to that way of using the calling 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 seen by VBA the the enclosing " " pair, reducing it to
""" & Arg1 & """ , " & Arg2 & "
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 Publc
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
See next post
Reply With Quote
Bookmarks