Improving Your Macro

After you record your macro, you may realize that the macro can perform some additional tasks. Adding new instructions to the macro code is not very difficult if you are already familiar with the Visual Basic language. In most situations, however, you can do this more efficiently when you delegate the extra tasks to the macro recorder. You may argue that Excel records more instructions than are necessary. One thing is for sure, however—the macro recorder does not make mistakes, and you can rely fully on it.

If you want to add additional instructions to your macro using the macro recorder, you must record a new macro, then copy the sections you want, and paste them into the correct location in your original macro. Let's add a thick border around cells A1:B3.

1. Activate the Microsoft Excel window with the worksheet shown in Figure 1-6.

2. Choose Tools | Macro | Record New Macro.

3. In the Macro dialog box, click OK to accept the default macro name and begin recording.

4. Select cells A1:B3.

5. Choose Format | Cells and click the Border tab.

6. In the Presets section of the dialog box, click the Outline button.

7. Click the thickest line in the Style box and click OK to close the dialog box.

8. Click cell A1. Notice the thick border around cells A1:B3.

9. Click the Stop Recording button on the Stop Recording toolbar, or choose Tools | Macro | Stop Recording.

To view the recorded macro, switch to the Visual Basic Editor window. The macro code that adds a thick border around cells A1:A3 is shown here:

Sub Macro2() ' Macro2 Macro

' Macro recorded 5/31/2002 by Julitta Korol Range("A1:B3").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With

With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With

With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With

With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With

Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("A1").Select End Sub

Now let's analyze the recorded code. Do you think you can get rid of some instructions? Before you go ahead and delete the unnecessary lines of code, think of how you can use the comment feature. Before you delete any macro code, comment it out and run the macro with the commented code. If the Visual Basic Editor does not generate any errors, you can safely delete the commented lines. If you follow this guideline, you will never find yourself recording the same keystrokes more than once. And if the macro does not perform accurately, you can remove the comments from the lines that may be needed after all. For details on working with comment blocks, please see Chapter 2.

When you create macros with the macro recorder, you can quickly learn the VBA equivalents for the Excel menu options and dialog box settings. Then you can look up the meaning and the usage of these Visual Basic commands in the online help. It's quite obvious that the more instructions Visual Basic needs to read, the slower the execution of your macro. Eliminating extraneous commands will speed up your macro. However, to make your macro code easier to understand, you may want to put on your detective hat and search for a better way to perform a specific task. For example, take a look at the code the macro recorder generated for placing a border around selected cells. It appears that the macro recorder handled each line separately. It seems hard to believe that Visual Basic does not have a simple one-line command that places a border around a selection of cells. Learning the right word or expression in any language takes time. If you look long enough, you will find that Visual Basic has a BorderAround method that allows you to add a border to a range of cells and set the Color, LineStyle, and Weight for the new border.

Using Visual Basic for Applications, the quickest way to create the thick border around a selection of cells is with the following statement:

Range("A1:B3").BorderAround Weight:=xlThick

The above instruction uses the BorderAround method of the Range object. It uses the thick line to create a border around cells A1:B3. (The next chapter covers Visual Basic objects, properties, and methods.)

Now let's add the above instruction to the WhatsInACell macro:

1. Activate the Code window with the WhatsInACell macro.

2. Enter a new line after ActiveCell.FormulaR1C1 = "Formulas".

3. In the blank line, enter the following instruction: Range("A1:B3").BorderAround Weight:=xlThick

4. Place the cursor anywhere in the macro code and press F5 to run the modified macro.

Tip 1-7: Including Additional Instructions

■ To include additional instructions in the existing macro, add empty lines in the required places of the macro code by pressing Enter and type in the necessary Visual Basic statements.

■ If the additional instructions are keyboard actions or menu commands, you may use the Macro recorder to generate the necessary code and then copy and paste the necessary lines into the original macro.

Let's say you would like Visual Basic to notify you when it has finished executing the last macro line. This sort of action cannot be recorded, as Excel does not have a corresponding menu option. However, using the Visual Basic language, you can add new instructions to your macro by hand.

1. In the Code window, click in front of the End Sub keywords and press Enter.

2. Place your cursor on the empty line and type the following statement: MsgBox "All actions have been performed."

3. Make sure the cursor is located anywhere in the macro code, and press F5.

4. When Visual Basic completes the last recorded instruction, it displays the message. Click OK. You now know for sure that the macro has finished running.

MsgBox is one of the frequently used VBA functions. You will learn more about its usage in Chapter 4.

0 0

Post a comment