Enhancing Recorded Selections | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

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 Selectionor ActiveCell. What you do next depends on whether a single Selection (or ActiveCell) statement follows the Select statement or whether a group of statements follows.

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.

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.
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, the word Select appears 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.
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

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 does make the macro run faster. Another reason is that running a macro can seem less disruptive ifit 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 want 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.

In Excel, select a sheet with labels in the first row, and start recording a macro named MakeBoldItalic.
Click cell B1, click the Bold button, click the Italic button, and then click the Stop Recording button.

Bold

Italic

Stop Recording
Edit the macro to 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, the macro won’t control which cells will become italicized.
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
```
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.
Save the Chapter04 workbook.

Eliminating the selection when there’s a group might not seem like much of a simplification. And with only two statements, it probably isn’t. But when you have several statements that use the same selection, storing the range in a variable can make the macro much easier to read.

Tip

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

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

Tip

Secretly in the background, the With structure really just creates a hidden variable, takes the object from the With statement, and assigns that object to the hidden variable. It then puts the hidden variable in front of each “dangling” period. The End With statement discards the hidden variable. An advantage of using an explicit object variable is that you can delare the variable with a specific object type-for example, Dim myRange as Range-and then VBA checks to make sure any methods or properties you use are appropriate. With an explicitly declared variable, VBA also offers Auto Lists to help you modify a macro.