Timed Alarm in Excel VBA

Question

I made a calendar to track tasks and similar items in Excel 2003. What I need to be able to do is to set a timer via VBA. Something like this:

run_in_x_secs ( timetowait, function to exec)

Is there a way to do that in excel vba, or should I try and find an alarm to run via the command line and run that from VBA.

How would you do it?

Answer 1

This should do the trick:

Public Sub RunSubInXSeconds(secondsToWait As Long, nameOfFunction As String)
    Application.OnTime Now + secondsToWait/(24# * 60# * 60#), nameOfFunction 
End Sub

Although you do have to be a little careful with OnTime, especially if you're going to be setting it to wait for a long period of time... E.g. if you close the workbook before the timer expires and the Sub executes, then you can end up with your workbook opening itself to run it. Confusing if you don't expect it!

Answer 2

Public Sub tt()

timerset = TimeValue("01:00:00") 'the time you want
Application.OnTime timerset, "myfun"

End Sub

Sub myfun()
 'do whatever you want
End Sub

Answer 3

two more options:

'Option 1
Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

Sub Sleep()
 Sleep 1000   'whait 1 second
End Sub

'Option 2
Sub MyWaitMacro()
  newHour = Hour(Now())
  newMinute = Minute(Now())
  newSecond = Second(Now()) + 3
  waitTime = TimeSerial(newHour, newMinute, newSecond)
  Application.Wait waitTime
End Sub