Download this white paper as a
Word 6.0 document (103K)
Download this white paper as a
Word 6.0 document (103K)
As far as some people are concerned, you can't be too rich or too thin. Similarly, program developers and users alike desire small, high-performance, quick-loading applications. To accomplish this ideal state, conscientious coders often go through great pains to reduce their VBA file size. This is generally an attempt to improve loading time and performance. But like an anorexic who purges in an effort to get thinner, many developers are actually reducing performance by taking counter-productive measures to obtain smaller code.
After discovering methods that temporarily reduce file size (discussed below), and then watching the file sizes yo-yo like someone on a bad diet, developers can get downright emotional about these issues. The following are direct quotes from a couple of developers on an Internet news list (see footnote 1):
"Various people have remarked on VBA's nasty habit of blowing itself up like a pufferfish whenever a module is modified."
Another exasperated developer exclaimed,
"This nasty business is an everyday occurrence...I don't like it but hey what can I do about it? Rant and rave, I suppose... Ever wonder why it is happening? Do you ever wonder if *they* care?"
In reality, VBA doesn't have these "nasty habits" but rather a predictable behavior that isn't obvious. There are steps you can take to reduce file size and increase performance. In this article we'll disclose the ideal methods for maximizing loading performance and minimizing file size (and these strategies don't include getting sick after each development session). By understanding some of the little-known details of how VBA code is stored and run, you can make your applications load and run faster. This will allow you to make informed decisions as you trade file size for performance. While you may not always have the smallest waist line on the block, your code will be healthy and high performance. You will be lean and mean, but not all skin and bones.
Cutting the Fat by Stripping Modules
When developing solutions using VBA, you can reduce the apparent file size of your application very quickly. I call this process "stripping modules." Here's the simplified step by step:
Repeat this process for all modules in your file. (Attached toolbars get lost in this process; you'll need to reattach any toolbars that are part of your project.) In some files, you'll notice an immediate file-size reduction of 20-50%, depending on the size of the module. At first glance this can seem wonderful-but, like losing a few pounds of water weight, it is quick but hardly permanent.
From a performance point of view, this process can sometimes actually increase loading, startup, and run (see footnote #2) times for your application. In other words, the smallest file size on disk doesn't always translate into a lean, quick-loading program. The real reasons for stripping your modules will be come clearer as we learn more about VBA.
The Yo-Yo Diet
If you've ever exercised heavily and immediately stepped on the scale, you may find yourself much lighter. Losing weight due to water loss is very common during intense exercise. But it's not permanent or even desirable. Drink a few glasses of water and you can be right back where you started. The trick is to reduce the fat; losing water doesn't make you healthier.
When you edit your newly stripped modules-even if you add as little as a single character-and then re-run your code, the file size may quickly return to its original size. Sometimes it doesn't. Turning the code into an Add-In (using Tools, Make Add-In...) can also return a file to its previous size. For the uninitiated, it can appear random, with file size changing dramatically with tiny alterations.
The Same Substance in Multiple States
VBA can loosely be compared to H2O; it exists in more than one state. H2O exists in three states called water, ice, or steam, depending on its temperature. To move between states requires some work, time, and, depending on which direction you are moving, sometimes more or less space.
VBA also exists
in different states, depending on what you've done previously.
The table below provides names and comments for each state:
(Browsers which do not show tables can view
this information as a graphic.)
| State |
Comments |
| Opcodes |
Platform -independent VBA
codes Always stored on disk (see footnote #3) |
| Text View |
View of Opcodes - never
stored Displayed on-the-fly in modules |
| Excodes |
Platform-dependent VBA
codes Stored after module is compiled (see footnote #4) |
Let's discuss each one individually and then explain what causes your code to move between states, and the effect on file size and performance.
Opcodes:
Platform-Independent Code
You can think of Opcodes as "Operational codes," the
codes stored and used by VBA when you're operating (editing) the
code. Since VBA is designed to run on multiple platforms (Windows
3.1/95/NT or Macintosh), Microsoft settled on this intermediate
state that never changes even when you change platforms. This
allows VBA to handle the same code running back and forth for
16-bit and 32-bit versions of Excel, as well as moving between
the Windows and Macintosh platforms. While computers find Opcodes
interesting, they aren't exactly user-friendly. So Microsoft
allows us to view the Opcodes in a different state, which I call
"text view."
Text View
Technically, this isn't really a state, but rather a view of the
Opcodes. We can edit and read the text and it looks familiar to
us, but VBA never stores the ASCII text. As you save a file, the
module is converted to Opcodes and stored on disk. When you open
an Excel file and switch to a module, VBA reads the Opcodes and
converts it back to text that you can read and edit. This occurs
so quickly it's hard to believe the conversion process is really
happening, but it happens every time.
Excodes:
Platform-Specific Code
Excodes are the compiled versions of Opcodes and are
platform-specific. Excodes are created when you run a routine on
a module or when you choose the Tools, Make Add-in command. Once
these Excodes are created, they are saved with the file so the
code will start running much faster the next time a routine from
the module is called. Depending on a module's size, the process
of creating these Excodes can be a relatively time-consuming
operation. (This is under a second or two for most modules, but
your mileage may vary.) With an XLS file, each module can contain
either Opcodes or Opcodes plus Excodes, depending on whether or
not a routine was run before the file was saved. This can make a
big difference in your file size!
When a file that was run and saved in Excel 5 (16 bit) is subsequently run in Excel 7 (32 bit), the VBA compiler has to create all new Excodes before the code runs. The same process occurs when moving from the PC to the Mac, or vise-versa. Remember, Excodes are machine/platform specific, and they are recreated each time you run on a different platform.
Cutting Fat without Losing Muscle
When your application is loading, the ideal situation is having your code pre-compiled into the Excodes. These Excodes are the muscle of your application. If they aren't available, Excel has to load its code for creating them and compile your Opcodes before your application can run. This process takes time. When you reduce your file size by saving the modules out as text and deleting them, you are actually stripping all the Excodes, along with some additional, behind-the-scenes items that VBA uses to track routines.
These additional items may include extra fat-items that are no longer used, but aren't deleted by VBA until you strip the module.
For example, Excel maintains a name table with routine names, variable names, and some other information. Stripping your modules will reset the name table to the minimum size required for your project. When you delete routines and variables, they aren't removed from the name table until and unless you strip the modules.
The average user will find the extra fat to be in the 5-10% range of the total file. It's worth getting rid of this excess baggage, but it's not worth being sick or starving.
Very large projects can benefit from more frequent strippings if routines are removed and edited heavily. Of course, getting rid of extra comments, routines that are no longer used, and cleaning up current code can often provide an even bigger gain, but that has nothing to do with VBA dieting.
The Ideal Diet
In an ideal world, you'd strip all your modules (removing the true VBA fat) and then run a routine on each module to be sure it contains all the Excodes before saving. A simple strategy for creating Excodes is to add the following routine to each module in your project:
Sub
CompileNow() ''' used to force compile
End Sub
This routine's
sole purpose is to force VBA to create the Excodes for its
module. Run this macro on every module before saving your file
and you'll have the Excodes ready for maximum performance. The
code for automating this process can be found in our Baarns
Developer Jump Start (on our Web site).
(see footnote #5)
When you create an Add-in, VBA compiles all the Excodes for all modules in a similar fashion. This is automatic and you don't need to do anything other than select the Tools Make Add-in command. But be aware that if you run your VBA app on a different platform, you'll find a slight hit at startup time because your code has to be re-compiled. If you are into maximum speed in all cases, you'd actually create a separate file for each platform. Unless your project contains thousands of lines of code in multiple modules, it generally isn't worth the hassles of maintaining multiple copies of the same code. The best compromise is to compile on the specific platform your users are most likely to be using.
Getting the Skinny
Exercise and diet are important, but anything can be taken too far. The process of stripping your modules isn't necessary until you're near final development. In my case I might do it once a week on a larger project, but often I wait until near the end of development. I tend to run code that creates all the Excodes (and saves the file) quite often during the development process. This prevents my having to wait any longer than necessary while testing, and provides a real-world insight into how well my code is performing.
Once a project is in production with users and we're shipping updates (often used as a polite term for bug fixes), I generally just worry about all the Excodes being compiled and ready to go. A project should only need stripping again if you do major development. In other words, once a project is lean, there's no need for drastic dieting again. The Excodes are the muscle of your project so it rarely pays to eliminate them from your file.
More Information
This article is just a small part of the information available from Baarns. To see examples of routines discussed in this article, check out the Baarns Publishing web site at http://www.baarns.com. This web site contains "Frequently Asked Questions" (FAQs), development tips, source code and other interesting Microsoft Office related materials.
The Baarns Developer Jump Start is an excellent source of battle-tested source code for developers. This can also be found on the Baarns web site.
Credits
Author: Don
Baarns
Technical Editors: Chris Kinsman and Steve Mitchell
Edits, suggestions and feedback: Lisa Baarns, Tony Hardin, Ray
Petersen, Fred Carlsen, and Craig Stinson
Special thanks to Larry Tseng and Russell Williams of Microsoft
for technical insights and research assistance!
© 1996 Baarns Consulting Group, Inc.
All Rights Reserved. No part of this document may be reproduced
or transmitted in any form or by any means without the written
permission of Baarns Consulting Group, Inc.
Microsoft is a registered trademark of Microsoft Corporation.
Footnotes:
 Despite the cost of living, have you noticed how it remains so popular? |
Copyright© 1996-1999, Baarns Consulting Group, Inc. - All rights reserved. |