OnTime Confusion/Rant

[hatman]

Does anyone else find the OnTime Method to be Hokey? Or at least underdocumented?

Let's start SIMPLE.

Example 1:

Sub stuff()

    Application.OnTime Now(), "stuff2"

End Sub

Sub stuff2()

    MsgBox "hello"

End Sub

Fine, werx as expected. But what if I wanted to pass an argument?

Example 2:

Sub stuff()

    Dim hlp As String

    hlp = "Help"

    Application.OnTime Now(), "stuff2(hlp)"

End Sub

Sub stuff2(a As String)

    MsgBox a

End Sub

Yields an error.

Addition of Single Quotes will eliminate the error

Sub stuff()

    Dim hlp As String

    hlp = "Help"

    Application.OnTime Now(), "'stuff2(hlp)'"

End Sub

Sub stuff2(a As String)

    MsgBox a

End Sub

But upon closer examination, the variable 'a' is actually empty upon execution of stuff2. If anyone can tell me how to pass an argument, I'd be much obliged, but as far as I can tell, it's not possible.

Example 3 (real life application):

Yesterday, I was all proud of myself for figuring out how to make a Pair of Slaved ListBoxes work to my satisfaction. You'll notice that I used the Ontime Method to initiate an event that in turn raises the Change Event of the control that called it in the first place (placing the call directly within the Change Event of the control causes VBA to disable the call in order to prevent infinite recursion).

So this morning, I opened this file, and the functionality no longer worked AT ALL. No error messages, no hangups, just no activity following events that should be triggering stuff. By executing in break mode, I finally raised an error message on the issuance of the OnTime Method, which kindly informed me that the referenced Macro could not be found in the workbook (note the syntax of the message box calls out the ENTIRE file/pathname along with the Macro name). After a whole slew of experimenting this morning, I finally inferred that the routine being called must be public. I assume that when I tested this yesterday in a book that had not been saved, somehow I slipped in uder the radar since there was no file/pathname for the workbook yet, but upon saving it, Thisworkbook.Path became populated, which then made the Private Procedure invisible. I know, it makes no sense, but I tried it in a new workbook: the structure works fine in an unsaved workbook, save it, and it stops working.

So in order to make my code for Slaved ListBoxes work, I had to do what I HATE to do: create a Global Object Variable. Here is a simplified version of what I did:

Private Sub UserForm_Initialize()

    Set Spec_Conn_Uf = Me

End Sub

Private Sub UserForm_Terminate()

    Set Spec_Conn_Uf = Nothing

End Sub

Private Sub ListBox1_Change()

    If Stp Then
    
        If Me.ListBox1.ListIndex = 0 Then
        
            If Me.ScrollBar1.Value > 1 Then
            
                Application.OnTime Now() + 1 / 24 / 3600 / 100, "Decrement"
                
            End If
            
        ElseIf Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 Then
        
            If Me.ScrollBar1.Value < Me.ScrollBar1.Max Then
            
                Application.OnTime Now() + 1 / 24 / 3600 / 100, "Increment"
                
            End If
            
        End If
        
    End If

End Sub

Global Spec_Conn_Uf As Special_Connectors

Sub Increment()

    Spec_Conn_Uf.ScrollBar1.Value = Spec_Conn_Uf.ScrollBar1.Value + 1

End Sub

Public Sub Decrement()

    Spec_Conn_Uf.ScrollBar1.Value = Spec_Conn_Uf.ScrollBar1.Value - 1

End Sub

(note: if you didn't check out the other thread, the Scrollbar_Change event has code that triggers the ListBox_Change event)

How messy is THAT code? Sure, it works, and does what I want... but the level of indirection needed to make it work falls into my definition of Cludge. And where in the documentation does it say that I can't do this the way I wanted to in the first place?

Seeing this in hidsight, I suppose that I can accept that OnTime somehow works like a Call statement that is issued from some ghost Module that needs to be able to "see" the procedure that it's trying to call. But up front, you figure that as long as you issue the command within the same Private Module where the called procedure is located, Ontime should have similar mechanics to the Call Statement.

 

[Oorang]

You might find these helpful.
http://vb.mvps.org/samples/project.asp?id=TimerObj
http://www.enhanceddatasystems.com/ED/Pages/ExcelTimer.htm
Mr Pearson also gives APIs that could be turned to this end here:
http://www.cpearson.com/excel/ontime.htm