Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OnTime: Pass Object Arguments
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
OnTime: Pass Object Arguments
Maybe like this
UserForm1.ComboBox1.Vaule "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
OnTime: Pass Object Arguments
ComboBox.Value
I miss typed Value in last post "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
OnTime: Pass Object Arguments
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 On Oct 11, 9:47 am, Mike wrote: Maybe like this UserForm1.ComboBox1.Vaule "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
OnTime: Pass Object Arguments
ComboBox1.Name
"Greg Lovern" wrote: 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 On Oct 11, 9:47 am, Mike wrote: Maybe like this UserForm1.ComboBox1.Vaule "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
OnTime: Pass Object Arguments
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 "Greg Lovern" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to pass variables as arguments of a function | Excel Worksheet Functions | |||
How to pass arguments to vlookup in VBA | Excel Programming | |||
Application.OnTime -- Unable to Pass Macro with Numeric Parameter | Excel Programming | |||
Run/execute VBS and pass arguments | Excel Programming | |||
How to pass arguments from ThisWorkbook to a UserForm | Excel Programming |