Microsoft Office Tutorials and References
In Depth Information
Editing a Macro with Comments and Improvements to the Code
Next, you went back to select column C because it was empty, and you deleted it.
Next, you selected range A1:C13 where the table of data was.
Next, you sorted the selected range.
Next, you selected range C2:C13, which contained numbers you wanted to format.
Next, you formatted the selected cells with the thousands comma separator.
Next, you selected range A1:C1 where the column labels were.
Next, you formatted the selected range in order to Bold the font of those label cells.
Finally, you turned off the Macro Recorder, which produced the End Sub line. All macros
end with the End Sub statement.
That’s quite a few “Nexts” in the explanation for what is going on! Fortunately, you can edit a
macro by typing your own descriptive comments, and you can consolidate a lot of the code so it
runs faster and looks cleaner.
Editing a Macro with comments and improvements to the code
As good as the Macro Recorder is at teaching VBA code, it is woefully lacking in the efficiency
department with the volume of code it produces. To be fair, the Macro Recorder was never meant
to be a lean, mean coding machine. Its primary function, which it performs lawlessly, is to produce
VBA code that represents your every on-screen action.
It should be said, there is no law in the universe dictating that you must modify your every recorded
macro. Sometimes, for simple macros that do the job, leaving them in their original recorded state is
fine — if they work the way you want them to, you’ve won that round.
However, for the majority of VBA code that gets produced by the Macro Recorder, the
superfluous and inefficient nature of its excessive code will be impossible to ignore. Besides, when you send
your VBA workbook masterpieces to other users, you’ll want your code to look and act beyond the
beginner stage of recorded code.
You will find that editing a macro in the Code window is very similar to editing a
Word document. Of course, rules exist for proper syntax of VBA code lines, but
the principles of typing text, selecting words and deleting them with the Delete
key, pressing Enter to go to the next line down — all these word processor kinds
of behaviors with which you are familiar — will help make the macro edit
process an intuitive one.
A rule of thumb in VBA development is, don’t select or activate objects unless you need to. The
methods of Select and Activate are among the biggest culprits of slow, meandering macro
execution. For example, the first two lines of code in the recorded macro are: