Temporary alarm in Excel VBA

Question

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

run_in_x_secs ( timetowait, function to exec) 

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

How do you do this?

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 need to be a little careful with OnTime, especially if you are going to set it up for a long period of time ... For example. if you close the workbook before the timer expires and Sub executes, you can end your workbook to start it. Confuse if you do not 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