66.

Enhancing Recorded Selections

When you record a macro, the macro recorder dutifully follows all your actions, including selecting ranges before acting on them. You can make a macro do less work-and make it easier to read-by eliminating unnecessary selection changes. A powerful technique for eliminating unnecessary changes to the selection begins with watching for a statement ending in Select followed by one or more statements beginning with Selection or ActiveCell. What you do next depends on whether a single Selection (or ActiveCell) statement follows the Select statement or whether a group of them follow.

Simplify Select…Selection Pairs

When a single Selection statement follows a Select statement, you can collapse the two statements into one. Record and simplify a macro that puts the names of the months across the top of a worksheet.

1. In Excel, insert a blank worksheet and start recording a macro named LabelMonths. Type the labels January, February, and March in the cells B1, C1, and D1.

   

2. Turn off the recorder, and then edit the macro. The macro should look similar to the following code. (Your macro might be slightly different, depending on the key you press to enter the values into the cells.)

   Sub LabelMonths()     Range("B1").Select     ActiveCell.FormulaR1C1 = "January"     Range("C1").Select     ActiveCell.FormulaR1C1 = "February"     Range("D1").Select     ActiveCell.FormulaR1C1 = "March"     Range("D2").Select End Sub 

   For each cell, you see the word Select at the end of one statement followed by either the word Selection or ActiveCell at the beginning of the next statement. You can delete both words, leaving only a single period. If a Select statement is the last one in a macro, you can delete it entirely.

3. Remove the unnecessary selections from the LabelMonths macro by deleting Select and ActiveCell each time they appear. The final macro should look like this:

   Sub LabelMonths()     Range("B1").FormulaR1C1 = "January"     Range("C1").FormulaR1C1 = "February"     Range("D1").FormulaR1C1 = "March" End Sub

4. Insert a new blank worksheet, and test the macro. The labels appear in the cells, and the original selection doesn't change.

Why should you get rid of Select…Selection pairs? One reason is that doing so makes the macro run faster. Another reason is that running a macro can seem less disruptive if it doesn't end with different cells selected than when it started. But the most important reason is unquestionably that Select…Selection pairs in a macro are a dead giveaway that you're a beginner who uses the macro recorder to create macros. It's OK to use the macro recorder; you just have to cover your tracks.

Simplify Select Groups

When you eliminate a Select…Selection pair, be sure that only a single statement uses the selection. If you have a single Select statement followed by two or more statements that use the selection, you can still avoid changing the selection, but you must do it in a different way.

1. In Excel, select a sheet with labels in the first row and start recording a macro named MakeBoldItalic.

2. Click cell B1, click the Bold button, click the Italic button, and then click the Stop Recording button.

   

3. Edit the macro. It will look like this:

   Sub MakeBoldItalic()     Range("B1").Select     Selection.Font.Bold = True     Selection.Font.Italic = True End Sub

   Obviously, if you delete the first Select…Selection pair, you won't be able to predict which cells will become italicized.

4. Edit the macro to assign the range to a variable named myRange. Then replace the Selection object with the myRange object. The finished macro should look like this:

   Sub MakeBoldItalic()    Dim myRange As Range    Set myRange = Range("B1")    myRange.Font.Bold = True    myRange.Font.Italic = True End Sub

5. Change "B1" to "C1" in the macro, and then press F8 repeatedly to step through the macro. Watch how the format of the cell changes without changing which cell is originally selected.

6. Save the Chapter05 workbook.

Eliminating the selection when there's a group might not seem like much of a simplification. With only two statements, it probably isn't. When you have several statements that use the same selection, however, converting the selection to an object variable can make the macro much easier to read.

Note 

You could also replace the Select group with a With structure, like this:

With Range("B1")      .Font.Bold = True     .Font.Italic = True End With 

Here's what the With structure does secretly in the background: it creates a hidden variable, takes the object from the With statement and assigns that object to the hidden variable, and then puts the hidden variable in front of each 'dangling' period. The End With statement discards the hidden variable.