Video

Background

I was asked today how to make some Excel VBA code pause for 5 seconds. Having spent time with Python and C++, I knew that these languages have built in expressions to allow code to wait but I was pretty sure that VBA didn’t.

A quick search on the web confirmed my suspicions and, whilst I knew that it would be a simple thing to code, the explanations presented seemed a little off the ball. Therefore I thought it would be an ideal quick post to detail how to do it and I might as well throw in some other examples too. It should be noted that, strictly speaking, the code isn’t paused it’s just running an operation for a set time which gives the appearance of pausing.

Standard execution to demonstrate the three languages

Rather than simply demonstrate the code – which is short and sweet in two of the cases – I thought it would be better to slip it into something slightly more useful. Hence, the routine I’ve thrown together simply writes “Starting to Sleep”, pauses for 5 seconds and then continues to run by outputting “Awoken and ready to run more code”.

In each of the example I’ve hard coded the time values but it would be an easy exercise to pass in an alternative value or to prompt the user for a value.

C++

C++ has built in support for a sleep/pause function and can be found in windows.h or unistd.h (depending upon platform). The one thing to remember is that the Sleep() function takes a single numerical argument which will be the length the code will wait in milliseconds. Since we wish to pause the code for 5 seconds, we pass in 5000

Windows C++

[sourcecode language=”cpp”]

#include <iostream>;
#include <windows.h>;
using namespace std;

int main()
{
cout << “Starting to sleep” >> endl;
Sleep(5000);
cout << “Awoken and ready to run more code >> endl;
return 0;
}

[/sourcecode]

Unix C++

[sourcecode language=”cpp”]

#include <iostream>;
#include <unistd.h>;

using namespace std;

int main()
{
cout << “Starting to sleep” >> endl;
Sleep(5000);
cout << “Awoken and ready to run more code >> endl;
return 0;
}

[/sourcecode]

Python

Python’s ‘time’ library has support for pausing code via the time.sleep() function and accepts an argument in default second intervals. Thus, we pass in the 5 in this case.

[sourcecode language=”python”]
import time

def sleepExample():
time.sleep(5)

if __name__ == “__main__”:
print “Starting to sleep”
sleepExample()
print “Awoken and ready to run more code”
[/sourcecode]

Excel VBA

Unlike the C++ and Python examples, Excel VBA does not have a simple mechanism for pausing code but we can write one pretty easily by utilising VBA’s time handling ability. Also, Excel doesn’t use main() but I have squished in a sub named main() just to keep the examples equivalent and it is this that you would call from Excel’s macro dialogue.

I’ve also commented out the DoEvents and you can re-enable this if you like.

[sourcecode language=”vb”]

Sub Sleep()

waitUntil = Now() + TimeValue(“00:00:05”)

While Now() < waitUntil
‘DoEvents
Wend
End Sub

Sub main()
MsgBox “Starting to sleep”
Call Sleep
MsgBox “Awoken and ready to run more code”
[/sourcecode]

Excel Application.Wait()

After posting I had a message informing me of the built in method in Excel of Application.Wait(). So, here I am now kicking myself for not remembering it (and that does answer the nagging thought I had that Excel did have a way to do it!) and of course this would do the job too. So, self-flagellation can now commence and the code to do that would be as follows

There are advantages to writing your own code to do this as you may wish to have a finer level of control over the code. As a general rule, replicated existing functions tend to be slower than their native counterparts but, whilst I have not quantified it, I can think of little difference in performance between these two VBA approaches (especially as a proportion of the pausing time).

[sourcecode language=”vb”]

Sub main()
MsgBox “Starting to sleep”
Application.Wait(Now() + TimeValue(“00:00:05”))
MsgBox “Awoken and ready to run more code”
[/sourcecode]

1 COMMENT

  1. Hi! Thanks for the presentation of the pause button. I have a slightly different problem that Im struggeling with. I want to implement a pause button in a real time running process. So let me explain my process. I’ve a mechanical process with specific end time. This value is stored in cell B2. I’ve coded a real time clock, and this value is stored in cell C1. My start time is stored as NOW() in cell B1. And my cell A1 shows a precentage value of how far my process have run, after that I press the start button ofcourse.

    The precentage of A1 is calculated by following:
    Actual Time/Planned Time
    Where,

    Actual time is:
    Real date and time value – Start time value (C1-B1)

    And Planned time is:
    Stop time value – Start time value (B2-B1)

    This gives me a precentage in A1, that increases from 0% – 100% depending on the real date and time approaching the stop time.

    So here is my problem, lets say my mechanical process needs maintenence for example changing of a damaged engine and the process have already elapsed 28%. I want to implement a pause button that pauses the process at the actual time that I press pause and when I press the pause button again I want the process to continue exactly from the same precentage as when it was paused from.

    Is this possible?

    Thanks for taking your time and reading this problem, any help is appriciated.
    My email is samer.nameer@gmail.com you could e-mail your answear there.

    Cheers!

I'd love to hear what your thoughts are...please feel free to leave a reply