Taking Microsoft Excel 2003 VBA courses is the first step in adding new functionality to your spreadsheet projects. However it is only the beginning. Efficient and powerful macro programming takes experience and practice to achieve.
Here are a few tips to get recent, or not-so-recent, graduates of Microsoft Excel 2003 VBA courses pointed in the right direction.
Reduce Traffic Between Worksheets And Macros
Passing data back and forth from cells on the worksheet to your macros is a very slow process. Minimizing this communication greatly speeds up macro function. Some users, inspired to use techniques learned in Microsoft Excel 2003 VBA courses, use macros for simple calculations better handled by worksheet functions. This may be for readability with complex formulas. It is easier to understand a macro name than a huge equation.
The problem is the more readable macro solution is slower because of the communication overhead. In a small spreadsheet the speed difference is undetectable, but in a large project unnecessary macros can slow calculations down dramatically.
Another aspect of worksheet-macro traffic that is not always covered in Microsoft Excel 2003 VBA courses is the number of read/write events. It is much faster to read a block of a thousand cells than to read each cell individually. If a macro is acting on a large range of data, read the range in first before looping through the data rather than looping and reading a cell on each iteration.
Avoid Worksheet Functions In Macros
Graduates of Microsoft Excel 2003 VBA courses often find it is convenient to use existing worksheet functions, from simple Max formulas to advanced numerical functions, rather than re-inventing the wheel. However worksheet functions are slow.
A large part of this is the communication problem from the last section however sometimes the existing function is simply too complex for the macro designer's needs. Excel functions are designed to cover a wide range of possibilities so contain many logic tests and extra code to cover eventualities that your macro may never face.
Even built in macro functions discussed in Microsoft Excel 2003 VBA courses are often less efficient than simply doing it yourself. If there is a VBA function you use often, try speed tests to see if it would be faster to enter equations directly instead.
Provide Feedback
A watched spreadsheet never calculates. Sometimes your macros may be a marvel of efficient design, a prime example of skills learned in Microsoft Excel 2003 VBA courses, but users may perceive it as slow.
Any time the user is going to have to wait for more than a few seconds, provide some kind of feedback. An updating dialogue, a progress bar, or a status cell on the sheet are ways you can give the user some indication of how much remains to be done.
Don't worry about predicting the time left, but a percentage complete at least lets the user decide whether to wait or go get a cup of coffee.
