excel - ThisWorkbook not holding global variable value to cancel ontime()

Question

I am using the below link:

http://www.ozgrid.com/Excel/run-macro-on-time.htm

to store the time at which I call a function via ontime and then cancel it. However, after I store the last time, I call ontime() then my function closes. The problem is the time I have just stored goes out of scope and chanegs to empty. So using the link above, I am saying that once RunOnTime() has finished, the value of dTime is empty and therefore when I call CancelOnTime() it does not work. I declared dTime just as in the example. Everything is the same except I am in ThisWorkbook.

Can anyone advise?

Answer 1

You can't run that code in the ThisWorkbook module as posted. ThisWorkbook is a class module and procedures in it aren't available outside of the class by default. First, you need to make any procedure called by OnTime public so it's accessible from outside the class (OnTime lives outside the class). Next, any references to the procedure need to be preceded by the class name. With this two changes, your code will look like this:

Public dTime As Date
Dim lNum As Long

Public Sub RunOnTime()
    dTime = Now + TimeSerial(0, 0, 10)
    Application.OnTime dTime, "ThisWorkbook.RunOnTime"

    lNum = lNum + 1
    If lNum = 3 Then
       CancelOnTime
    Else
        MsgBox lNum
    End If

End Sub

Sub CancelOnTime()
    Application.OnTime dTime, "ThisWorkbook.RunOnTime", , False
End Sub