So I'm a little new with excel, but programming in VBA is fun so I took on a little project involved consuming data from a webservice. The workflow went like this:
- Poll web service
- Drop new records in designated named range
- Run again in 30 seconds
I scheduled a subroutine to perform this using Application.Ontime. The problem was, when i closed the workbook, it kept on opening the workbook back up. So I had to figure out a way to cancel the scheduled subroutine. You do this by setting schdeule=false, but you have to pass in the same time that it was schedule for in the earliest time param. Here's how you do it:
Public RunWhen As Double - I put this in a module called mPublic
Private Sub Workbook_Open()
Time_set
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Bye_Bye
End Sub
Sub Time_set()
RunWhen = Now + TimeValue("00:00:30")
Application.OnTime RunWhen, "time_set"
DoWork
End Sub
Sub Bye_Bye()
Application.OnTime EarliestTime:=RunWhen, Procedure:="time_set", Schedule:=False
End Sub
Rate this post to let me know if it actually helped anyone!