Microsoft Office Tutorials and References
In Depth Information
Editing a Macro with Comments and Improvements to the Code
You’ve now seen plenty of comments in the example macros, and how useful
comments can be in your VBA code. To enter a comment line of text,
simply type in the apostrophe character, and everything you type after that, on
that same line, will be regarded as a comment and not executed as VBA code.
Usually, comments are written as stand alone lines of text, meaning the very first
character on that line is the apostrophe. However, some programmers prefer to
place comments on the same line as actual VBA code, for example:
Range(“A1”).Clear ‘Make cell A1 be empty for the next user.
In any case, comments will be green in color by default, and will not be executed
as VBA code.
Another way you can speed up your macros is to use the With statement when you are performing
multiple actions to the same object, such as to a range of cells. Suppose as part of your macro, you
need to clear a range of cells and format the range for the next user. If you use the Macro Recorder
to do this, here is the code you might get:
Selection.Locked = False
Selection.FormulaHidden = False
Selection.Font.Bold = True
Selection.Font.Italic = True
Notice there are five lines of code that all start with the Selection object, which refers to the
selected range of A1:D8. If this code was to run as the Macro Recorder produced it, VBA would
need to resolve the Selection object for each line of code.
You can do two key edits to these lines of code by avoiding the Select method altogether and
referring to the range object only once at the beginning of a With structure. Between the With and End
With statements, every line of code that starts with a dot shall be evaluated by VBA as belonging
to the same range object, meaning the range reference need only be resolved once. Here is the
condensed code using a With structure for greater efficiency:
.Locked = False
.FormulaHidden = False
.Font.Bold = True
.Font.Italic = True