ABC ~ All 'Bout Computers
The Online Web-azine for Computer Enthusiasts
-- brought to you by

contents page for this issue

Chad's Macro Mania
~~Chad K. Welch

“My what big code you have!”

In the last issue of ABC,  we discussed the Macro Recorder and it’s role in VBA in the Microsoft Office Suite.  The recorder is a very quick and easy way to incorporate Visual Basic code into your Office document.  I begin programming  a lot of my VBA projects with the recorder for two basic reasons.  1. It records code a lot faster than I can type it, and 2. it has a better memory than I do.  When I write the code I sometimes have to stop and think what the method is called that I am trying to use, but the recorder will always get the name correct and in the right order. 

Some of the disadvantages of using the recorder are that it will also record the errors I make (obviously) and also the code that it generates is usually very lengthy.  Cleaning up the code after recording it is a very good habit to get into.  Clean code is easier to read, follow and debug.  It generally runs quicker, and it will lessen the margin of error.  There are three basic steps to cleaning up recorded code: identify the ambiguous, consolidate the duplicates and speed up the process. 

First we will identify the ambiguous.  When recording a macro you will probably select cells in Excel or a paragraph in Word, etc.  The recorder will add those actions to your code; however, VBA does not generally need to select the object to act on it.  That means that your code will have a lot of “Select,” “Selection,” and “Activate” in it that can (and should) be changed.  For example the following lines of code were recorded:

Range(“A1:C5”).Select

Selection.Font.Bold = True

Those two lines of code should be changed to:

Range(“A1:C5”).Font.Bold = True

Similarly:

Sheets("Sheet2").Activate

ActiveSheet.Delete

Can be changed to:

Sheets(“Sheet2”).Delete 

Two general rules of thumb:

 1. If one line contains Select and the following line(s) contains Selection, 

or 2. The first line contains Activate and the following line(s) contains Active??? 

then they should be changed.  

Of course there are exceptions to these rules, but are very infrequent.  In the next issue of ABC I will explore the exceptions, but for now suffice it to say that those rules are both examples of potentially ambiguous code and should be identified and corrected.

Second, consolidate the duplicates and unnecessary steps.  Lets face it, we’re humans and we make mistakes and change our minds.  Suppose that while recording your code you decide to make the font of a sentence red, so you highlight the sentence and change the font color to red.  Then you realize that you marked the wrong sentence, so you change the font color back to black, highlight the correct sentence and change it’s font color to red.  Later you change your mind and decide to make the entire paragraph have red font.  The recorder will record all of your actions, but you really only need the last format change.  Deleting the other procedures will speed up your code. 

Another example in Excel.  Start the recorder to record the following steps:

1. Select cell C3

2. Click on the toolbar button that looks like a percent sign (%) to change the format to percent.

3. Then click twice on the toolbar button to increase the decimals displayed.

4. Stop the recorder.

Viewing your recorded code you will see the following:

Range("C3").Select

Selection.Style = "Percent"

Selection.NumberFormat = "0.0%"

Selection.NumberFormat = "0.00%"

Lines two and three are not necessary.  Try running the following line and you will end up with the same results:

Range(“C3”).NumberFormat = “0.00%”

The preceding steps were recorded, but only the final result is actually needed.

The last part of our cleanup is to make your macro run even faster than it already does.  The previous two steps have already sped things up considerably.  Of course that may not be obvious since it probably runs very fast to begin with.  There are still two major things that can be done to speed the code up.  You will notice that when running your macro, you can still see what is happening on the screen.  This slows your code down quite a bit.  Adding two lines to your macro will turn this feature off. 

At the beginning of your macro (after the line that has the word “Sub” in it) add the line:

Application.ScreenUpdating = False

And at the end of your macro (before the “End Sub” line) add:

Application.ScreenUpdating = True

Now your screen won’t change until the macro has finished. 

Anther way to speed things up in Excel is to turn off automatic calculation.  This however, can be a little more tricky.  To turn it off use the line:

Application.Calculation = xlCalculationManual

To turn it back on use:

Application.Calculation = xlCalculationAutomatic

You can also recalculate the worksheet at any time without turning the calculation back on, just by entering the line:

Application.Calculate

This, as I mentioned, can be tricky.  If you aren’t careful you will get erroneous results.  To demonstrate, enter random numbers in cells A1:C5 and then run the following code.  It will enter the formula =SUM(A1:A5) in cell A6, copy the formula  to cells A6:C6 and then copy and paste values in those cells.  With calculation on, it would place the sum of A1:A5 in A6, the sum of B1:B5 in B6 and the sum of C1:C5 in C6, but with calculation off it enters the sum of A1:A5 in all three cells A6:C6.  The worksheet should be calculated before the paste special is performed.

Sub WrongCalculation()

Application.Calculation = xlCalculationManual

Range("A6").FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"

Range("A6").Copy Destination:=Range("A6:C6")

‘(Application.Calculate ‘should be placed on this line to give correct results)

Range("A6:C6").Copy

Range("A6:C6").PasteSpecial Paste:=xlPasteValues

Application.Calculation = xlCalculationAutomatic

End Sub

Next month I will go into more detail of the cleaning procedure.  In the meantime try recording and cleaning your own code.  

Good Luck and Happy Coding!
Chad

<<editor's note: for information on creating Macros in Word, see this month's article on Customizing Word by Chas Kenyon>>

Chad K. Welch works as a technician/enabler in Utah.  He is available for consulting or application programming with Microsoft Office and VBA.  Do you have a question or tip you’d like to have Chad address in this column?  Send an email to linda@personal-computer-tutor.com or contact him directly at chad@welchkins.com for information