Excel VBA multiple Application.OnTime

Question

I am trying to use multiple Application.OnTime inside my sub and I am getting the unexpected results.

This is what I want to do:

Run a sub (testOnTime) every 1 minute using a "recursive" call

Inside sub (testOnTime):
1. run "firstSection" 10 seconds after entering the sub (testOnTime)
  
  Inside "firstSection": write debug msgs to Excel sheet "MySheet"
2. run "lastSection" 40 seconds after entering the sub (testOnTime)
  
  Inside "lastSection": write debug msgs to Excel sheet "MySheet"
3. Write debug msgs to Excel sheet "Mysheet"
4. has sub "testOnTime" run 5 times?: No -> continue , yes -> exit

Code:

Option Explicit

Public rowCnt As Integer
Public Cnt As Integer
Public Const sheetName = "MySheet"
Public Const inc1 = "00:00:40"
Public Const inc2 = "00:00:10"
Public timeStr1 As Date
Public timeStr2 As Date
Public timeStr3 As Date

Public Sub MyMain()

     rowCnt = 1
     Cnt = 0

     Call testOnTime

     Worksheets(sheetName).Range("A" & CStr(rowCnt)).Value = "Done"

End Sub

Public Sub testOnTime()


     ' wait-time for last section
     timeStr1 = Format(Now + TimeValue(inc1), "hh:mm:ss")
     ' wait time for first section
     timeStr2 = Format(Now + TimeValue(inc2), "hh:mm:ss")
     ' wait for 1 minute
     timeStr3 = Format(Now + TimeValue("00:01:00"), "hh:mm:ss")


     ' wait utill 10 seconds
     Application.OnTime TimeValue(timeStr2), "firstSection"

     ' wait utill 40 seconds
     Application.OnTime TimeValue(timeStr1), "lastSection"

     ' debug msgs
     Worksheets(sheetName).Range("A" & CStr(rowCnt)).Value = "Outside @ " & CStr(timeStr3)
     Worksheets(sheetName).Range("B" & CStr(rowCnt)).Value = CStr(rowCnt)
     Worksheets(sheetName).Range("C" & CStr(rowCnt)).Value = CStr(Cnt)
     rowCnt = rowCnt + 1

     Cnt = Cnt + 1

    If Cnt < 5 Then
         ' wait until Now + 1 min
         Application.OnTime TimeValue(timeStr3), "testOnTime"
    End If

End Sub

Public Sub firstSection()
    ' debug msgs for first section
    Worksheets(sheetName).Range("A" & CStr(rowCnt)).Value = "In first section @ " & CStr(timeStr2)
    Worksheets(sheetName).Range("B" & CStr(rowCnt)).Value = CStr(rowCnt)
    Worksheets(sheetName).Range("C" & CStr(rowCnt)).Value = CStr(Cnt)
    rowCnt = rowCnt + 1
End Sub

Public Sub lastSection()
    ' debug msgs for first section
    Worksheets(sheetName).Range("A" & CStr(rowCnt)).Value = "In last section @ " & CStr(timeStr1)
    Worksheets(sheetName).Range("B" & CStr(rowCnt)).Value = CStr(rowCnt)
    Worksheets(sheetName).Range("C" & CStr(rowCnt)).Value = CStr(Cnt)
    rowCnt = rowCnt + 1
End Sub

Current output

Why does "Outside..." get written first to the excel sheet, when it is supposed to be last instruction inside the macro?
Why does the macro output "two" msgs of the same thing when it should be outputting only once?
Is there a better way of acheiving my desired goal without use of "OnTime"?

too many public vars, no arguments in calling subs, integer should be long, sheetname not declared, i just can't stop with the programming mistackes, rowcnt should be retested each cycle in its own sheet rowcnt=cells(rows.count,1).end(xlup).row — Patrick Lepelletier

Aidan Aidan · Accepted Answer · 2014-05-27T12:50:12

I ran the code and I think it ran correctly - I must confess I'm a little confused as to what its goal is. I didn't see any doubling up of the messages.

I was able to engineer a doubling of the messages by running the macro twice - excel is happy to let it run twice and you get a doubling of the messages then. Is this what caused the problem?

I think you are misunderstanding how Application.OnTime runs - it is a non-blocking call so it creates the future call to the specified subroutine and then continues - this is why the "outside" message is being displayed first. I think it is also why the "Done" message is displayed immediately until it gets over-written.

Hope this helps

Excel VBA multiple Application.OnTime

2 Answers