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?

http://visualbasic.about.com/od/learnvba/a/vbatiming1.htm – Mitch Wheat Feb 02 '11 at 15:30 — Mitch Wheat, Feb 02 '11 at 15:30

score 3 · Answer 1 · answered Feb 02 '11 at 15:50

3

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

answered Feb 02 '11 at 15:50

Dr. belisarius

60,527
15
115
190

yours works great too (as soon as I found that it was the absolute time). Thnx – Diego Castro Feb 02 '11 at 17:29
@Diego Sorry, I forgot to mention it. Boa sorte! – Dr. belisarius Feb 02 '11 at 17:32

score 3 · Accepted Answer · answered Feb 02 '11 at 17:10

3

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!

answered Feb 02 '11 at 17:10

Simon Cowen

1,903
16
20

This will only happen, if the Excel application is still running... right? – froeschli Feb 02 '11 at 17:26
This happens if you schedule the OnTime, then before it runs the callback you close the workbook, **but** leave the application open. It's an interesting way to hack Excel into reopening a file! – Simon Cowen Feb 02 '11 at 17:28
Great Simon. That's the desired behavior. My users don't even know it's an excel file, they only use my Buttons. (and my close button just closes the workbook, not the app) – Diego Castro Feb 02 '11 at 17:37

score 2 · Answer 3 · edited Nov 04 '12 at 10:32

2

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

edited Nov 04 '12 at 10:32

Mario S

11,715
24
39
47

answered Dec 14 '11 at 18:54

Tomamais

21
1

Timed Alarm in Excel VBA

3 Answers3

Linked