Word.Tips.Net WordTips (Menu Interface)

Making Macros Run Faster, Take Two

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 97, 2000, 2002, and 2003.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

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!)

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.