Making Macros Run Faster, Take Two

by Allen Wyatt
(last updated April 28, 2014)

Last week's tip about making macros run faster was met with a good number of comments, questions, and caveats by WordTips readers. Some readers recognized the technique described--putting multiple statements on the same line and separating them with colons--as something that was often done in older versions of the BASIC programming language. Several readers also asked for additional information regarding the technique and how it is implemented "behind the scenes" in VBA. (Even though this macro specifically refers to VBA, the concepts it discusses and the techniques resulting therefrom are also applicable to WordBasic macros.)

When you create a macro in VBA, it is "pseudocompiled" or "tokenized" when you save the macro or when you save the template in which the macro is stored. Instead of the human-readable form that is visible in the VBA Editor, each keyword or directive is converted internally to a three-byte "token" which is easily processed by VBA. Variable names, comments, and literal string values--none of which are keywords or directives--are not modified and are saved "as is." Even though VBA tokenizes the keywords and directives, it does not "compress" any of the lines in the macro; each line is maintained as it is, with the carriage return at the end of the line intact.

When the macro is executed, the VBA processor fetches a single line of the macro at a time. The tokens on the line are interpreted and executed, in the order that they appear on the line. When one line is completed, the next line is fetched and executed, and so on, through the end of the macro. When you combine multiple statements on a single line and separate them by colons, the idea is to reduce the number of fetch cycles that the processor must go through. This is where the speedup comes into play; less fetching means less time spent doing "overhead" and more time spent actually executing the macro itself.

It should be obvious that when you combine statements on a single line, the resulting macro can be much more difficult for you, as a human, to read. Thus, the "optimize" or "don't optimize" decision involves a tradeoff--harder to read code vs. faster code. It has been this same way since the early days of BASIC, long before Word was even dreamed of.

You should understand that the time saved by combining statements on a single line is miniscule, particularly with today's fast computers. Thus, it doesn't make much sense to combine statements in which the portion of code will be run only once. The real savings comes into play if the code will be repeated hundreds or thousands of times during the course of the macro. The time savings realized from each iteration of the code may be small, but the cumulative time savings may be quite attractive.

If you decide to "compress" your code by combining multiple statements and keywords on a single line, there are a few things to keep in mind. First of all, the maximum line length is 255 characters. Also, Sub statements, which are used to start macros, must appear on their own line, as must With and End With statements. (In WordBasic you can concatenate the Sub statement with other statements, but if you later convert the macro to VBA, you must manually break the line in two.)

Astute readers probably picked up another idea by the information presented earlier in this tip. Since the "pseudocompiler" does not compress variable names, literals, or comments, you can somewhat speed up your macro by reducing the length of variable names, the use of literals, and the use of comments. Again, the tradeoff of such a decision is in readability. When maintaining your macro at a later date, shorter variable names are more cryptic, and a lack of comments can be counterproductive. Thus, you must be the judge on whether the tradeoff is worth it.

There is one other interesting thing to note--if you do any concatenation of lines in your macros, you will obviously want to do some testing to make sure that they still work as you intend. As you are stepping through the macros (in the VBA Editor), notice that the Step Into command (F8) still only executes one statement at a time, not a whole line at a time. This behavior is specifically for the interactive development environment, and does not reflect how the VBA engine behaves when the macro is running full-speed. At run-time, the macro is executed as described above--a full line at a time.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (1644) applies to Microsoft Word , , , , and .

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Automatically Moving from Cell to Cell when Entering Data

As you enter data in a worksheet, you may want to have Excel automatically move from cell to cell based on the length of what ...

Discover More

Checking for Proper Entry of Array Formulas

Excel allows you to enter two different types of formulas in a cell: A regular formula or an array formula. If you need to ...

Discover More

Creating a Log/Log Chart

If you need to create a chart that uses logarithmic values on both axes, it can be confusing how to get what you want. This ...

Discover More

Do More in Less Time! Are you ready to harness the full power of Word 2013 to create professional documents? In this comprehensive guide you'll learn the skills and techniques for efficiently building the documents you need for your professional and your personal life. Check out Word 2013 In Depth today!


Changing ToolTips for a Macro Button

Want to change the ToolTip that appears when you hover the mouse over a button on a toolbar? It's a bit more involved than ...

Discover More

Cleaning Up Text in a Macro

Need to remove extraneous characters from a text string? VBA makes it easy through the CleanString method, described in this ...

Discover More

Creating a Command List

Want a list of all the commands available in Word? You can get one easily by following these steps.

Discover More

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)