Clean
Up This Mess!!
Part 2
Last
month
we learned a few steps to clean up the code that the macro
recorder records. As you may recall, the recorder will
record all steps that you make regardless of whether or not it is
a necessary step. For example, begin recording a macro in
Excel. Select cell C4, type ‘45’ and press enter.
Stop the recorder and select Tools>Macro>Macros.
In
the dialog box that opens highlight the macro you just recorded
and click on Edit.
The
following lines of code are recorded:
Range("C4").Select
ActiveCell.FormulaR1C1
= "45"
Range("C5").Select
As
we learned last month, those three lines can be reduced to:
Range(“C4”)
= 45
VBA
does not need to select the cells to place a value in it.
The general rule of thumb is:
If
the first line contains Select or Activate and the
following line contains Selection or Active___, the
lines can be rewritten on one line.
There
are a couple of exceptions to these rules. Let’s record
another macro to see an exception. Once again in Excel start
the macro recorder. Select cells D8:F11. Press the
copy icon on the toolbar.
Select
cell A1 and press the paste icon on the toolbar.
 Stop
the macro, and go into the VBE again to view the code. This
is what was recorded:
Range("D8:F11").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
The
first two lines contain a Select and a Selection.
Those lines can be consolidated to:
Range(“D8:F11”).Copy
The
last two lines also contain a Select and an Active___.
However, consolidating these lines to Range(“A1”).Paste
would create a run-time error. The reason is that the
selection is a range, but the Paste method is used on a
sheet object.
WHAT!?
I know; that was confusing. Let me rephrase that and leave
out the Geekese. Range(“A1”).Select selects a
cell, while ActiveSheet.Paste is performing an action
(paste) to a worksheet.
Because
the Select and Active___ refer to different
components of a workbook, they cannot be consolidated.
Let’s rephrase our rule of thumb:
If
the first line contains Select or Activate and the
following line contains Selection or Active___ and
refer to the same object, the lines can be rewritten on one line.
Good Luck and
Happy Coding!
Chad
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
|
,
