When a macro makes rapid changes to a document by frequent navigation or manipulating content on the screen, it can slow the macro and also cause unpleasant screen flickering while the macro runs. Both effects are more common with macros that manipulate copious amounts of text, especially in large documents. Today, we improve the former and fix the latter all for one low price.
Thanks for your interest
This content is part of a paid plan.
Toggle Screen Updates
Removing inefficiencies is the most important way to upgrade a slow macro, but we can also tweak a few Word settings to improve the speed and even the presentation of our macros.
What’s the plan?
We can reap some quick improvements by turning off screen updates during macro execution to prevent Word from repeatedly updating the display until our macro is done. More importantly, this also ensures our macro runs cleaner since a user won’t see any unsightly text manipulations in progress. They just see the final result.
We can further limit some extraneous calculations Word does while the macro is running—
“In Word? What calculations are you talking about?”
Just hold on.
Word is constantly checking and updating the document under the hood while you work, so with just flip a few simple switches we can make Word wait until we’re done before resuming regular operations.
Specifically, we can toggle document pagination. We can also turn off spelling and grammar checking while the macro runs. Of these options, toggling pagination is probably more important, but the latter two don’t hurt since the editor is always running in the background.
Create subroutine skeletons
Due to their simplicity, we don’t need any returned values, so we create subroutines instead of functions. We start by creating the skeletons.
Since we’re making fixed changes to these settings, there are also no parameters to include in the subroutines.
If you prefer to have a single toggle function, check out the upcoming member content extension.
What do we change?
So we want our macro to run faster and possibly avoid some screen flicker. What can we do?
Screen updates
The screen updates setting is part of the top level Application object. Assuming the macros are written reasonably well, turning it off will further improve the speed of many macros.
We simply set it to a True or False value depending on what we want, and it does exactly what the name implies.
False disables screen updates during the macro execution which also prevents screen flickering from the rapid document changes.
Document pagination
If a macro makes any sizeable changes to documents, particularly large ones, it may also help to temporarily disable document pagination.
This disables Word recalculating the page layout of the document in real time as the macro runs. At the end of the macro, we turn it back on, and Word can recalculate the pagination once.
Spelling and grammar
If we really want to go hog wild, we can turn off grammar and spell checking.
This setting does just what the name implies. It controls whether Word checks spelling in real-time while you type.
This setting controls whether Word also checks grammar while it checks the spelling in the document.
Of course, if you always work with spell checking turned off, you can omit these settings.
Gotchas
Two of these gotchas cannot necessarily be solved, but we should still be aware of them.
Not turning screen updates back on
What happens if a macro doesn’t turn screen updates back on (perhaps it crashes before the macro ends). In my experience, Word won’t let screen updates stay off after the macro finishes, so we’re mostly safe.
However, it’s bad manners and potentially risky to take chances with sloppy assumptions, so you should definitely turn the screen updates and pagination settings back on by the end of the main macro.
Avoiding redundant updates
If you have macros calling others where you’re toggling screen updating in several of them, it could get confusing or at least redundant to toggle the settings off and on in rapid succession as your macro moves in and out of the various subroutines or functions.
Store update options
One solution is to store the current setting states and possibly restore them at the end of the function in question. I don’t find it necessary often, but see the member extension for a example of how this can be accomplished using “Static” variables.
Navigation pane still updated
Unfortunately, the navigation pane still seems to be updated during macro execution. There doesn’t seem to be much we can do about that as regular users, so you’ll probably still see the pane text jiggle.
Macro efficiency?
General programming efficiency tips are beyond the scope of this article, but I’ll mention one. Don’t loop through documents in super inefficient ways—by individual words or characters, for example—unless absolutely necessary. Find a better way to accomplish the task.
Word-specific tips
A few other more speed suggestions follow. To keep this article concise here, I’ll just briefly mention them:
- Avoid using the Selection object except for smaller, simpler macros since this manipulates the active selection or insertion point on the screen. Word will keep up with the changes in real time. Turning screen updates off mitigates this effect, but it’s better to use Ranges instead.
- Avoid using the clipboard if there are native methods to accomplish the same task. Even if you use the clipboard, you can paste unformatted text.
- Use Find operations when possible rather than looping through any text. There are some idiosyncrasies when working with Find, but it’s extremely fast in comparison to home-baked solutions.
- Avoid rapidly swapping back and forth between open documents using document activation. This can be a big speed drag for certain macros, so work around it in a different way.
For small macros, you’ll likely never notice the speed difference, but in more complex macros, perhaps with loops over large amounts of content, you can start to see some significant improvements.
If you’re intersted in more information about any of these, let me know. If there is sufficient interest, I can elaborate in a separate article.
There are other suggestions, but they become more problem-specific improvements.
More minor suggestions
Other more minor speed improvements include:
- Use With statements when you have many references to the same object in succession. This is more of an incremental improvement and probably not a big deal overall unless you’re doing long loops, but with statements also make your macro look cleaner and easier to read.
- Use specific data types for your variables. Generic or “Variant” data types are slower to access.
Final Subroutines
Putting everything together, the subroutines are:
I use these my versions of these subroutines regularly for any macro that makes more than a few on screen document changes.
Screen updates and pagination are straight forward. You basically want to turn them off and then back on as they should be at the end of the main macro.
If you never use spelling and grammar checking, then omit or “comment out” those lines. Just add an initial single quote comment character, and the line will become inactive as far as VBA see them.
Spelling and grammar checking could initially be on or off depending on the user, so ideally, we should save and restore the starting values if they’re included. This is a little more complicated than it sounds, so see the member extension for how to save and restore specific user settings.
