View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default OnTime: Pass Object Arguments

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 Auto_Open()
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, _
Procedu="'" & 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, _
Procedu=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

Greg Lovern wrote:

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


--

Dave Peterson