Excel VBA  - Sleep Function to Animate charts

Posted on 2014-01-21
Last Modified: 2014-01-21
I have created a VBA routine that increases the size of a named range every 1 second.  This named range provides the data for a graph.  The intention was that, upon click of a button, the graph would slowly grow/complete itself and therefore provide a good effect in a presentation.  However my VBA skills are not up to the task and approximately 75% of the time the animation does not work.  Can anyone suggest a better way to incorporate the Sleep function into my VBA routine that would allow for a more effective outcome?
Question by:JohnNZExcel
  • 2
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39798802
See attached.

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 500 total points
ID: 39798805

The best way to pause code execution is to provide a mechanism that gives the parent application such as Excel or Word opportunities to handle events as well as other operating system tasks. The routine below provides both and allows a pause of as little as a hundredth of a second.

Note that the declaration of the Sleep API function has to be placed above all other routines in the module.

[Begin Code Segment]

Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)

Public Sub Pause( _
      ByVal Seconds As Single, _
      Optional ByVal PreventVBEvents As Boolean _

' Pauses for the number of seconds specified. Seconds can be specified down to
' 1/100 of a second. The Windows Sleep routine is called during each cycle to
' give other applications time because, while DoEvents does the same, it does
' not wait and hence the VB loop code consumes more CPU cycles.

   Const MaxSystemSleepInterval = 25 ' milliseconds
   Const MinSystemSleepInterval = 1 ' milliseconds
   Dim ResumeTime As Double
   Dim Factor As Long
   Dim SleepDuration As Double
   Factor = CLng(24) * 60 * 60
   ResumeTime = Int(Now) + (Timer + Seconds) / Factor
      SleepDuration = (ResumeTime - (Int(Now) + Timer / Factor)) * Factor * 1000
      If SleepDuration > MaxSystemSleepInterval Then SleepDuration = MaxSystemSleepInterval
      If SleepDuration < MinSystemSleepInterval Then SleepDuration = MinSystemSleepInterval
      Sleep SleepDuration
      If Not PreventVBEvents Then DoEvents
   Loop Until Int(Now) + Timer / Factor >= ResumeTime
End Sub

[End Code Segment]

Note that the expression

   Int(Now) + Timer / Factor

is used to create a time that both handles midnight crossovers and is precise to within 1/100 of a second. Just the Timer function alone is precise to within 1/100 of a second but does not handle midnight crossovers. The Now function is only precise to within about 1/4 of a second.

The DoEvents call is used to give the managed environment such as Excel or Word opportunities to handle events and do other work. But DoEvents only works within the managed environment and can still consume a considerable amount of resources without some other throttling mechanism. By also using the Windows Sleep API call the Windows operating system is given an opportunity to let other processes run. And, since the code is doing nothing but waiting, it is the appropriate thing to do.

Often the task involves waiting for an asynchronous task to complete such as a web query. To use the above routine while waiting for such a task to compete, two time durations are needed: the total amount of time to wait until it can be assumed that a failure has occurred in the other task, and the amount of time to wait between checks that the other task has completed. Determining how long to wait until giving up requires consideration of the longest possible time that the task could reasonably take and how long the user is willing to wait for that task to complete - wait too long and the user gets frustrated, don't wait long enough and the risk increases of falsely assuming an error occurred when it didn't. This duration is the more difficult to determine of the two. The second time, the duration between checks for completion, is easier to determine. This duration should be long enough to not consume unnecessary CPU cycles doing the check, but short enough to respond quickly when the status of the asynchronous task changes. A duration of between a quarter of a second and one second is usually reasonable. The sample code below illustrates how to wait for an asynchronous task to complete that usually finishes in less than 10 seconds.

   Dim TimeoutTime As Date
   TimeoutTime = Now() + TimeSerial(0, 0, 30) ' Allow 30 seconds for the asynchronous task to complete before assuming it failed
   Do While Now() < TimeoutTime And Not IsTaskComplete
      Pause 0.5 ' Pause half a second to allow the ashyncronous task (and the rest of the environment) to do work

The above example uses a function named IsTaskComplete to determine if the asynchronous task completed. The function can do anything such as checking if a cell changed, checking if a control's property is set, or checking if a file exists.

Other techniques for pausing code execution and the problems with each are listed below. These should all be avoided in any well-designed application.

Wait Method (VBA only):

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

The Wait method suspends all application activity and may prevent other operations from getting processing time while Wait is in effect. However, background processes such as printing and recalculation continue. The net effect of pausing using the Wait method is to shut down the application (e.g. Excel) event handling and slow or stop other applications. This method does not allow any fractional seconds to be used.

Windows Sleep:

   Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
   Sleep 10000

Using the Sleep Windows API call is system friendly by allowing all other processes to get processing time but it effectively shuts down the parent application. This is generally not a good idea as an application should always be responsive to user requests, even if the application is waiting for an asynchronous task to complete. Extended sleep periods can also cause problems when Windows is sending out system events such as system shut down notifications.

DoEvents Loop

   TimeoutTime = Now() + TimeSerial(0, 0, 10)
   Loop Until Now > TimeoutTime

Performing a DoEvents loop to pause gives the parent application a chance to handle events but, because there is no pause between DoEvents calls, virtually all available processing time is dedicated to the loop and nothing else which means this is not a good way to pause code execution. This method does not allow any fractional seconds to be used.

Basic Loop without DoEvents

   TimeoutTime = Now() + TimeSerial(0, 0, 10)
   Loop Until Now > TimeoutTime

A tight loop without a DoEvents call effectively brings the workstation to a halt until the loop exits. This is the worst technique to pause code execution. This method does not allow any fractional seconds to be used.

There are other, more sophisticated, techniques that monitor event queues and other system resources but the net result is the same as a simple loop with both a DoEvents and a Sleep. As long as some other throttling mechanism is used such as the Windows Sleep function, DoEvents will consume very little resources as all it does is look for any pending events and then either processes those events or returns immediately to the caller.


Author Closing Comment

ID: 39799236
Very impressed by the thoroughness of the feedback and edited file.  Thank you 1000 times.

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question