OnTime: Pass Object Arguments

[Greg Lovern]

I understand how to pass number and string arguments with OnTime. But
what about object arguments? Can I pass a range object, combobox
object, etc.?


Here's how I'm passing number and string arguments. How do I pass
object arguments?


Function tester1()
Const NUM_ARG As Long = 5
Const STRING_ARG As String = "String"

Debug.Print "tester1: " & NUM_ARG & ", " & STRING_ARG & ", " & Now
Application.OnTime Now + 0.0000001, "'tester2 " & NUM_ARG & ", """
& STRING_ARG & """'"
End Function


Function tester2(NUM_ARG, STRING_ARG)
Debug.Print "tester2: " & NUM_ARG & ", " & STRING_ARG & ", " & Now
Debug.Print
End Function


Thanks,

Greg

 

[Mike]

Maybe like this
UserForm1.ComboBox1.Vaule

 

[Mike]

ComboBox.Value
I miss typed Value in last post

 

[Greg Lovern]

You mean just pass the value of the combobox? I don't want to do that.
If all I wanted was the value of the combobox, I'd just pass that
value.

What I'm doing now is passing strings that can be used to identify the
objects, such as range addresses, combobox names, worksheet names,
etc., then rebuilding the objects from those strings in the function
that is called by OnTime.

It works, but it would be nice if there was a more direct way.

Greg

 

[Mike]

ComboBox1.Name

You mean just pass the value of the combobox? I don't want to do that.
If all I wanted was the value of the combobox, I'd just pass that
value.

What I'm doing now is passing strings that can be used to identify the
objects, such as range addresses, combobox names, worksheet names,
etc., then rebuilding the objects from those strings in the function
that is called by OnTime.

It works, but it would be nice if there was a more direct way.

Greg

 

[Dave Peterson]

I would "pass" the variable to the subroutine via a public variable:

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 5 '5 seconds
Public Const cRunWhat = "myMacroName" ' the name of the procedure to run
Public myObj As Variant 'or Range or MSForms.Combobox if you know for sure.
Sub Autpen()
Call StartTimer
End Sub
Sub Auto_Close()
Call StopTimer
End Sub
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
'just some testing
Set myObj = ThisWorkbook.Worksheets("Sheet1").Range("A1")
Set myObj = ThisWorkbook.Worksheets("sheet1").ComboBox1

Application.OnTime EarliestTime:=RunWhen, _
Procedure:="'" & ThisWorkbook.Name & "'!" & cRunWhat, _
Schedule:=True
End Sub
Sub myMacroName()
If TypeName(myObj) = "Range" Then
MsgBox myObj.Address(external:=True)
ElseIf TypeOf myObj.Object Is MSForms.ComboBox Then
MsgBox "It's a combobox"
End If

'get ready for the next time
StartTimer
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, _
Procedure:=cRunWhat, Schedule:=False
End Sub

FYI: You may want to take a look at Chip Pearson's notes:
http://www.cpearson.com/excel/OnTime.aspx

 

[Peter T]

Just to confirm, you can only pass strings (incl numbers) as arguments to a
routine called with the OnTime method. In addition to Dave's suggestion you
could also pass one or more string arguments such that you can recreate the
object, eg

set rng = Workbooks(sArg1).Worksheets(sArg2).Range(sArg3)

or

set rng = range(sFullAddress)
where sFullAddress = "'[Book1]Sheet1'!A1"
there's a pair of apostrophes in that, and would need to be less than 255

Regards,
Peter T