Waiting in Excel Macros

Question

I am recording an Excel Macro where I would like it to wait half a second before switching to the next value. I am plotting changes to a graph, and right now it just storms through the values so you dont really see any changes. I would therefor like it to wait half a second before moving on.

The Macro Im using looks like this:

Sub Macro2()
' Macro2 Macro
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("C2").Select
End Sub

Now I would love to do something like the following but it doesnt work.

Sub Macro2()
' Macro2 Macro
    Range("C1").Select
ActiveCell.FormulaR1C1 = "1"
Range("C1").Select
Sleep (100)
ActiveCell.FormulaR1C1 = "2"
Range("C2").Select
Sleep (100)
End Sub

Help please? I'm on a Mac Office Excel 2011.

What's 'Sleep' on a Mac? I'm familiar with importing the `Sleep()` API call from Kernel32 in Windows, but I doubt that the code would work on planet Apple... So that's one of your problems, right there. The answers involving `Application.Wait()` will show you the way forward, as that's native to Office on all platforms. — Nigel Heffernan, Nov 30 '15 at 17:29
FYI, Wait use simplified: 1second will wait from (very nearly) zero, TO 1s. 2 will wait from 1 TO 2 seconds ETC. I.e. 1 second imprecision due to ignoring the milliseconds elapsed so far in the second. Details at https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba — Stax, Aug 30 '21 at 22:47

score 7 · Accepted Answer · answered Nov 30 '15 at 14:51

7

Try Application.Wait(time). It pauses the macro until the specified time, then returns True.

To wait 1 second:

Application.Wait(Now + TimeValue("0:00:01"))

(Tested in Excel 2010 on Windows)

answered Nov 30 '15 at 14:51

Olle Sjögren

5,315
3
31
51

Thank you - works perfectly! I knew it was one of those easy things where I guess i was more inhibited by not really know how to ask the question than understanding the answer (if that makes sense) ... – Saucepan Nov 30 '15 at 15:14

Bathsheba · Answer 2 · 2015-11-30T17:50:36.957

3

Application.Wait(Now + s / 86400) is one way, where s is the number of seconds. There are 86400 seconds in a day. Note that the wait time could be greater than this due to how this works internally.

If this isn't reliable enough (it seems that s needs to be a whole number), and your OS supports it, then you could always resort to using the kernel function which will give you sub-second granularity:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Include this at the top of your module, and call in the normal way: Sleep(500) will pause for (at least) 1/2 a second.

edited Nov 30 '15 at 17:50

answered Nov 30 '15 at 14:48

Bathsheba

231,907
34
361
483

This does not seem to be working. When I try to count time with the following function, I always get 0. I feel no delay too. `Dim c: c = Timer: Application.Wait (Now + 0.5 / 86400): MsgBox Timer - c` – ZygD Nov 30 '15 at 15:10
Perhaps the VBA interpreter is doing some funky rounddown. Did you try 1.0 / 86400? – Bathsheba Nov 30 '15 at 15:11
Yes, it works then, although `1.0` gets converted to `1#`. – ZygD Nov 30 '15 at 15:13
That's fine. It needs to be a floating point literal else the division will round. It might be better though to call the kernel function as above. – Bathsheba Nov 30 '15 at 15:14
Loving this kernel function! But the first option... repeating myself, gives me 0 delay – ZygD Nov 30 '15 at 15:18
1

Hum. I suspect VBA is doing something funky behind the scenes. Seems that 1 second is the finest amount of granularity you can use. I've amended the answer. – Bathsheba Nov 30 '15 at 15:54
This question was asked by someone working in the Office for Macintosh environment: are you sure that you can declare `Sleep()` ? – Nigel Heffernan Nov 30 '15 at 17:36
@Bathsheba is correct: the granularity of `Application.Wait` is 1 second: and the precision (or lack of it) is driven by task swapping and thread priority. Nevertheless, Wait is still a good tool: VBA is suspended but background calculation and incoming data feeds are still processed, unlike Sleep, which suspends the entire application... Which is often exactly what you wanted to do. – Nigel Heffernan Nov 30 '15 at 17:41
@nile that's a good point. I can't verify so I've fudged the answer. – Bathsheba Nov 30 '15 at 17:51

Tom · Answer 3 · 2015-11-30T15:06:22.670

3

Just on a side note - when using the macro recorder, go through the code generated afterwards and see if you can remove the select statements.

Your code would then become

Sub Macro2()
' Macro2 Macro
    Range("C1").FormulaR1C1 = "1"
    ' Whichever method you want to use from the other two answers put here
    Range("C1").FormulaR1C1 = "2"
End Sub

This will make it a lot more efficient. I haven't added the answer to your question as it's already been answered

edited Nov 30 '15 at 15:06

answered Nov 30 '15 at 14:56

Tom

9,725
3
31
48

Thank you for the tip! Cheers – Saucepan Nov 30 '15 at 15:14

score 2 · Answer 4 · answered Nov 30 '15 at 15:03

Application.Wait Now + TimeSerial(0, 0, 1)

This should wait for maximum 1 second. But you will never be sure with VBA. This waits for the specified time, so if you had 11:30:30 and a half, you will wait until 11:30:31, which will make it only for 0,5 seconds.

R3uK · Answer 5 · 2015-11-30T22:08:50.980

-1

As simple as that Application.Wait (Now + 0.000001) for a 1/10sec :

Sub Macro2()
' Macro2 Macro
Range("C1").FormulaR1C1 = "1"
Application.Wait (Now + TimeValue("0:00:01")) '1sec
Range("C1").FormulaR1C1 = "2"
Application.Wait (Now + 0.000001) '1/10sec
Range("C2").Select
End Sub

edited Nov 30 '15 at 22:08

answered Nov 30 '15 at 15:14

R3uK

14,417
7
43
77

Have you tested that? – Nigel Heffernan Nov 30 '15 at 17:27
@Nile : Yes, I tested it on Excel 2013 64bits and Win7 64bits, no problem with it! – R3uK Nov 30 '15 at 22:08
@R3iK ...And did it wait for a tenth of a second, or for an integer (or zero) number of seconds? Have a look at testing that with a precision timer API function, or the `VBA.DateTime.Timer` property, which returns a single-precision float, accurate to ±0.01 seconds, instead of the ±1 second precision functions passing the VBA 'Time Serial' variant. – Nigel Heffernan Dec 14 '15 at 13:44
@Nile : I just retried and most of the time, I've a got a decimal and sometimes got 0... A bit astonished by the 0... – R3uK Dec 14 '15 at 13:52

Waiting in Excel Macros

5 Answers5