Syntax for Application.Ontime incl Variable Parameter

[Tim Childs]

Hi



I have sub below, which calls another proc after it finishes (it seemed to
error otherwise).



Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)

...

...



dTimeDefault = Now() + TimeValue("00:00:05")

Application.OnTime dTimeDefault, "CreateNewWorksheet"

 

[Peter T]

I always forget the syntax so I keep the following handy (some gleaned from
this ng, others I worked out myslf). Here's the complete set, numbers and
strings as values and variables -

Sub test()
Dim nVar1 As Long, nVar2 As Long
Dim sVar1 As String, sVar2 As String
Dim sMacro As String

' these four Ontimes will run in order from last to first

'''' number values
sMacro = " 'MacroNum ""123"" , ""789"" ' "

Application.OnTime Now, sMacro


'''' number variables
nVar1 = 1111: nVar2 = 2222
sMacro = " 'MacroNum " & nVar1 & " , " & nVar2 & " ' "

Application.OnTime Now, sMacro


'''' string values
sMacro = " 'MacroStr ""Prompt value1"", ""Title value2"" ' "

Application.OnTime Now, sMacro


'''' string variables
sVar1 = "Prompt var1": sVar2 = "Title var2"
sMacro = " 'MacroStr " & Chr(34) & sVar1 & Chr(34) & _
", " & Chr(34) & sVar2 & Chr(34) & " ' "

Application.OnTime Now, sMacro

End Sub

Sub MacroStr(s1 As String, s2 As String)
MsgBox s1, , s2
End Sub

Sub MacroNum(n1 As Long, n2 As Long)
MsgBox n1, , n2
End Sub

Regards,
Peter T

 

[Tim Childs]

Hi Peter

thanks v much.

I will try this out

Tim

 

[Tushar Mehta]

You cannot pass an object as an argument with the method John (Green)
demonstrated. How to use a variable is something Peter has already shown
you.

You can always use a global variable to pass an object to a OnTime procedure
-- provided you can guarantee that that object variable will remain
untouched, and in your case I believe that will indeed be true.

So, in a standard module:

public aWB as workbook

sub CreateNewWorksheet ()
'aWB is now accessible from here
end sub

In the class module where you have the application variable:

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
dTimeDefault = Now() + TimeValue("00:00:05")
set aWB=Wb
Application.OnTime dTimeDefault, "CreateNewWorksheet"
End Sub

Also, if you are using this technique to defer execution of some code
because of problems doing so in the WorkbookOpen procedure, you should be
able to use just Now() rather than impose a 5 second delay. It's a "trick"
I, myself, have used on more than a few occasions. Essentially,

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
set aWB=Wb
Application.OnTime Now(), "CreateNewWorksheet"
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Peter T]

Hi Tushar,

Yes I should have mentioned it's not possible to pass an object OnTime,
especially as the OP had named the variable 'Wb'. I suppose as an
alternative to setting a global object reference it would be possible to
pass the workbook's string name and then set a new reference in the called
macro. I wasn't sure what the OP was doing.

I also use OnTime in the open event of some addins to do things that are
only possible once there is an activeworkbook, eg certain application level
settings. Like you I use 'Now' as it will only be called after everything
else has loaded, including any workbooks primed to load.

Regards,
Peter T