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"
.
.
End Sub
I now want to add a parameter to the CreateNewWorksheet procedure call (and
the proc itself) i.e. something like:
Application.OnTime dTimeDefault, "CreateNewWorksheet(" & Wb & ")"
But it errors!
Please can you help with the syntax?
THANKS
Tim
PS Have searched on Google and John Green kindly helped previously (2002)
but it was for a fixed parameter rather than a variable (anyway I am stuck
now!):
http://groups.google.com/group/microsoft.public.excel.programming/browse_thr
ead/thread/c5a0ba87b7bc254f/b9bb97043d13dd44?lnk=st&q=Application.Ontime+Par
ameter&rnum=3&hl=en#b9bb97043d13dd44
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" <tsn...@yahoo.co.uk> wrote in message
news:#yBhGHDN...@TK2MSFTNGP02.phx.gbl...
thanks v much.
I will try this out
Tim
"Peter T" <peter_t@discussions> wrote in message
news:uedoMcD...@TK2MSFTNGP04.phx.gbl...
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
In article <#yBhGHDN...@TK2MSFTNGP02.phx.gbl>, tsn...@yahoo.co.uk
says...
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
"Tushar Mehta" <tmUndersc...@tushar-mehta.SeeOhEm> wrote in message
news:MPG.200dffb5...@msnews.microsoft.com...