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?

Try storing your data in a (hidden) cell - this way it sure won't be lost! — Peter Albert, Apr 02 '13 at 09:47

score 2 · Answer 1 · answered Apr 02 '13 at 13:15

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

score 1 · Accepted Answer · answered Apr 02 '13 at 09:55

In the example, the variable dTime is declared as Public. In fact, it doesn't need to be because only RunOnTime() accesses it, but it's useful to be able to access it with a function you wrote. CancelOnTime() doesn't depend on the variable.

I think you may be confusing yourself with Watch. To watch a public varaiable, you set the context to All Procedures - All Modules.

ThisWorkbook not holding global variable value to cancel ontime()

2 Answers2

Linked