Recording Methods in a Macro

So far, in all the macros you’ve created, the macro recorder has used property assignments to carry out actions. Sometimes assigning a value to a property is not the best way to carry out an action. For example, sometimes it is critical to make multiple changes simultaneously, and assigning properties is not the best way to keep everything synchronized. The ToggleCleanDisplay macro is a good example of that: because each property is independent of the others, it’s very easy to get the toggle state inconsistent. Consequently, the Excel object model allows for a different way to carry out an action. This second method is called a-method. You can watch a method at work by using the macro recorder.

Convert a Formula to a Value by Using Menu Commands

For example, suppose that you want to freeze the formulas of some cells in the Budget worksheet at their current values. First change the formulas to values using menu commands-watching carefully how Excel does or does not prompt for additional information-and then create a macro that can change formulas to values for any arbitrary selection.

  1. Start by activating the Budget window, and then select cell D4.

    image from book

    Notice the formula in the formula bar: =D3-D54.

  2. Right-click the cell, and click the Copy command.

  3. Right-click the cell again, and click the Paste Special command.

    The Paste Special dialog box appears. This dialog box has four independent parts: the Paste group, the Operation group, the Skip Blanks check box, and the Transpose check box. You can choose only one option within each part, so you have four distinct choices you can make within this dialog box.

    image from book

  4. Select the Values option from the Paste group, and click OK.

    Excel pastes the value from the cell over the top of the existing cell, eliminating the formula that was in it. The moving border is still visible around the cell, indicating that you could paste the value again somewhere else.

  5. Press the Esc key to get out of copy mode and clear the moving border.

    In the formula bar, cell D4 now contains the value 28094.9.

Copying and pasting cell values are actions that don’t lend themselves to simple property assignments. When you execute the Copy command, what property would that be? Notice also that when you copy, you don’t see a dialog box. Excel simply puts a moving border around the cells; you don’t tell Excel how to do the copying.

When you execute the Paste Special command, on the other hand, you do see a dialog box. Excel needs additional information about exactly how you want the paste to behave. Think back to the Alignment tab of the Cell Format dialog box. That dialog box had multiple options, but the ones in Paste Special are different, because they interact with each other-if you select the Values option and the Add option and the Skip Blanks option all at the same time, they combine together to affect the one action of pasting. Given the interactive nature of all the dialog box controls, how would you do all that with a simple property assignment? When you record the same process in a macro, you’ll see how a method looks different from a property.

Convert a Formula to a Value by Using a Macro

You can learn about how a macro uses methods by recording a macro that converts formulas to values. As you look at the recorded macro, you can compare the statements for actions that display or don’t display a dialog box.

  1. On the Budget worksheet, select cell E4.

    Notice the formula in the formula bar: =E3-E54.

  2. Click Record Macro, replace the default name with ConvertToValues, press Shift+V to set the shortcut key to Ctrl+Shift+V, and then click OK.

  3. Right-click cell E4, and click Copy.

  4. Right-click cell E4 again, then click Paste Special, click the Values option, and then click OK.

  5. Press the Esc key to remove the moving border.

  6. Click the Stop Recording button, and save the Chapter01 workbook.

    In the formula bar, cell E4 now contains the value 28332.9.

  7. Switch to the Visual Basic editor to look at the recorded macro.


    If you closed the editor, go to the View tab of the Ribbon, click Macros, select ConvertToValue, and then click Edit.

Ignoring comments, the macro looks like this:

Sub ConvertToValues()   Selection.Copy   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _    SkipBlanks:=False, Transpose:=False   Application.CutCopyMode = False  End Sub 

The basic structure of this macro is the same as that of the other macros you’ve seen in this chapter: It starts with a Sub and ends with an End Sub, and has a bunch of statements in the middle. Also, the final statement in the body of the macro uses a familiar property assignment to set the value of the CutCopyMode property. This is how Excel interprets pressing Esc to remove the moving border around the cells.

The two statements that begin with Selection, however, are something new. Neither has a simple equal sign in it.

The statement Selection.Copy has two words, separated by a period. A word followed by a period is probably an object, and that’s exactly what this is: a range of cells object. The word Copy, however, isn’t a property; it’s a method. That’s why it doesn’t have an equal sign after it. You don’t assign anything to Copy; you just do it. Remember that the object is really just a way of grouping available commands. You can copy a range of cells, but you can’t copy, say, a workbook window, so there’s no such thing as ActiveWindow.Copy.

When you execute the Copy command in Excel, you don’t see a dialog box asking you for any extra information. In the same way, when you use the Copy method in a macro, you don’t give any extra information to the method.

The next statement begins with Selection.PasteSpecial. Once again, the word followed by a period-Selection-refers to an object. Once again, the word that follows the period- PasteSpecial-does not have a simple equal sign after it, so it’s not a property. It’s another method.

When you execute the Paste Special command in Excel, you see a dialog box that lets you give extra information to the command. In the same way, when you use the PasteSpecial method in a macro, you give the same extra information to the method. The extra pieces of information you give to a method are called arguments.

Using a method is like giving instructions to your nine-year-old son. With some instructions-such as “Come eat”-you don’t have to give any extra information. With other instructions-such as “Go to the store for me”-you do have give more instructions: what to buy (milk), how to get there (on your bike), and when to come home (immediately). Giving an extra piece of information to your son is like giving an extra piece of information to an Excel method. In both cases, you end up with an argument.

The four arguments you use with the PasteSpecial method correspond exactly to the four distinct parts of the Paste Special dialog box. Each argument even has a name that matches the caption in the dialog box: Paste, Operation, SkipBlanks, and Transpose. When you use an argument, you don’t actually have to include the argument name. This statement would function the same as

Selection.PasteSpecial xlPasteValues, xlNone, False, False

The names just make it easier to read, so the macro recorder includes them. If you do use a name for an argument, you put a colon-equal sign (:=) between the argument name and its value. The colon-equal sign may include an equal sign, but it’s easy to tell them apart because the equal sign (used in a property assignment) always has a space on both sides.

Make a Long Statement More Readable

When a statement in a macro gets to be longer than about 70 characters, the macro recorder inserts a space and an underscore ( _) after a convenient word and continues the statement on the next line. The underscore tells the macro that it should treat the second line as part of the same statement. You can manually break long statements into several lines, as long as you break the line after a space. You can also indent related lines with tabs to make the macro easier to read.

  1. In the ConvertToValues macro, put each argument of the PasteSpecial statement on a separate line, using a space and an underscore character at the end of each line except the last.

    Sub ConvertToValues()   Selection.Copy   Selection.PasteSpecial _   Paste:=xlValues, _   Operation:=xlNone, _   SkipBlanks:=False, _   Transpose:=False   Application.CutCopyMode = False End Sub 

    Splitting a statement into several lines doesn’t change the way the macro runs; it just makes it easier to read.

  2. In Excel, select cell F4 and press Ctrl+Shift+V to run the macro. Look at the formula bar to make sure the formula changed to a value.

  3. Save the Chapter01 workbook.

Most of the macros in this chapter change the property of an object, but this macro executes the method of an object. Both properties and methods are separated from objects by periods, and both allow you to carry out actions. However, you assign a value to a property to carry out the action, whereas you simply execute a method, sometimes giving it arguments along the way.

Microsoft Office Excel 2007 Visual Basic for Applications Step by Step
Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))
ISBN: 0735613591
EAN: 2147483647
Year: 2004
Pages: 99
Authors: Reed Jacobsen © 2008-2017.
If you may any questions please contact us: