Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Running Macros in the Background.
When you run a macro in Excel, the program turns its full attention to completing the macro. (Sounds almost anthropomorphic, doesn't it?) This means that if the macro does quite a bit of heavy-duty processing of your data, it can seem as if your system has "locked up" during the processing of the macro.
Rest assured that the macro processing is only affecting Excel, however. You can open a different application and work within it while the macro chunks away in Excel in the background. Of course, the attention being paid to the macro by your system will probably slow down the response of the other program, but this depends on the version of Windows you are using on your system. The reason? Sharing of resources requires a process known as multitasking. Different versions of Windows handle multitasking in different ways.
You may wonder how you can do other work in Excel while the program is busy running a macro. Easy—just open another instance of Excel (run it again from your Start menu) and do some other work. All you need to do is make sure that you don't try to work on the same workbook (or workbooks) being utilized by the macros in your first instance of Excel.
Another way to do a bit of work is to modify your macro just a bit. Chances are good that if your macro is running for a long period, it is due to it executing a loop of some type. By adding within the loop the DoEvents command, the macro will suspend execution temporarily and hand back control to the user's processes. So if you've pressed a key, clicked the mouse, or started to type something, DoEvents takes notice and performs whatever tasks are required to handle what you want done. When all user input has been service, the macro continues happily on its way.
Adding DoEvents slows down your macro a bit, but it does allow you to continue working a bit. (Understand that Excel will still be sluggish; your macro does take resources to run.) Adding DoEvents can also provide an opportunity to break out of the macro (using Ctrl+Break) in a controlled manner.
Remember, though, that you can only figure out what works best in your situation by testing (and more testing).
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8971) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Running Macros in the Background.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you use For ... Next loops in your macros, make sure you give a way to jump out of the loop early. That way you can ...
Discover MoreDo you want a way to reverse names within a cell, making them "last, first" instead of "first last?" Here's a handy macro ...
Discover MoreIf you have a large, complex workbook, you may want to make sure that it is always calculated manually instead of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2014-12-22 08:27:49
BHershman
Many thanks. That is very helpful.
2014-12-22 06:38:31
Barry
@BHershman
This is what I found in the "offline" help system, but you'll have to ask Microsoft as to why it is not available in the on-line help system but is in the MSDN site (BTW I find the offline help better than the on-line anyway).
DoEvents Function
Yields execution so that the operating system can process other events.
Syntax
DoEvents( )
Remarks
The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications.
DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.
DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.
Caution
Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.
Example
This example uses the DoEvents function to cause execution to yield to the operating system once every 1000 iterations of the loop. DoEvents returns the number of open Visual Basic forms, but only when the host application is Visual Basic.
' Create a variable to hold number of Visual Basic forms loaded
' and visible.
Dim I, OpenForms
For I = 1 To 150000 ' Start loop.
If I Mod 1000 = 0 Then ' If loop has repeated 1000 times.
OpenForms = DoEvents ' Yield to operating system.
End If
Next I ' Increment loop counter.
© 2010 Microsoft Corporation. All rights reserved.
2014-12-21 11:21:12
BHershman
Thanks, Barry,
But do you have any idea why it is not mentioned at all in the active Excel Help system?
2014-12-21 06:34:11
Barry
@BHersham
The DoEvents is pretty much as described in the tip. There's lots more if you Google "Excel VBA DoEvents" such as:
http://support.microsoft.com/kb/118468 together with issues when using it.
2014-12-20 08:53:05
BHershman
What is "DoEvents"? I cannot find any reference to it anywhere in the Excel Help system.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments