VBA thankfully allows you to also use another function called Do Events. Seriously, it doesn’t do anything more that handle all MS Office events. Now why would we want to use the Do Events function you might ask? Wait does not allow you to wait for intervals shorter than 1 second? Need to wait for 500 milliseconds – no more no less? Did you know that each time a cell that affects any formula in your spreadsheet is changed or manipulated, Excel recalculates the entire worksheet?In worksheets that have a large amount of formulas, this behavior can drastically slow down your macros. Calculation property to tell Excel to switch to manual calculation mode.Second, resetting Screen Updating to True is a wise practice to cover your bases.If for example the macro errors out or is interrupted before it completes, Screen Updating would still be off while Excel is running.Future versions of Excel will invariably support new, or stop supporting existing, VBA methods and properties, so it’s an unnecessary risk to not reset Screen Updating.Third, there might be times when you are running code in sequence and you want to see what you did while you are doing it. Macros Two and Three are called by Macros One and Two, but you cannot see the results of your code until the last macro is completed, unless you had set the Screen Updating back to the way it was in each macro. Screen Updating = False Msg Box "Screen Updating is off now !! Value = "Two" Msg Box "Screen Updating is " & Application.
One reason this helps is that if you’re updating (via VBA) several different ranges with new values, or copy / pasting from several ranges to create a consolidated table of data, you likely do not want to have Excel taking time and resources to recalculate formulas, display paste progress, or even redraw the grid, especially after every single operation (even more so if your code uses loops). The benefit here is that you probably don’t need Excel using up resources trying to draw the screen since it’s changing faster than the user can perceive.
The Sleep function pauses the entire process for a certain delay specified in milliseconds. It adds 1 second to the current clock time and asks VBA to wait until that moment to return from the function. Wait for 10 seconds at any time and resume control over your project.
The Sleep function is not available by default in VBA, and has to be imported from the #If VBA7 Then Public Declare Ptr Safe Sub Sleep Lib "kernel32" (By Val milliseconds As Long Ptr) 'MS Office 64 Bit #Else Public Declare Sub Sleep Lib "kernel32" (By Val milliseconds as Long) 'MS Office 32 Bit #End If your process entirely – preventing any input or interaction with your application (even breaking code execution). If you thought those were your only options – you were wrong.
Just one recalculation and one redraw at the end of your code execution is enough to get the workbook current with all your changes. Enable Events display Page Break State = Active Sheet. Since it requires lots of resources to draw the screen so frequently, just turn off drawing the screen until the end of your code execution.
Here’s some sample code that shows how and what to shut off while your code runs. Display Page Breaks ‘note this is a sheet-level setting ‘turn off some Excel functionality so your code runs faster Application. Be sure to turn it back on right before your code ends.
There are tons of sites, pages, and people who are experts as well on this subject, have performed their own tests, and shared their results and ideas.
If you think I missed an important concept for how to optimize Excel VBA performance, or if you’ve got a valuable comment or link to share, please feel free to post here so everyone can benefit. Turn Off Everything But the Essentials While Your Code is Running This optimization explicitly turns off Excel functionality you don’t need to happen (over and over and over) while your code runs.
When a workbook is in manual calculation mode, the workbook will not recalculate until you explicitly trigger a calculation by pressing the F9 key.
Place Excel into manual calculation mode, run your code, and then switch back to automatic calculation mode.
As I will try to prove – there are better, more productive approaches to pausing your code execution or utilizing potentially application idle time. Wait function is similarly pretty easy to use, although a little different than what you might expect with the Sleep function. Wait will This approach is of course less practical as usually you want to wait simply for a precise interval of time, like say 3 seconds.
Let us start by introducing the VBA Sleep function. Wait is a native VBA function that pauses code execution until a certain time is reached. Wait function is: The time when the function should return e.g. That is why we need to aid ourselves with the use of either the Date Add function or the Time Value function: What does the function above do?