26.

Recording Actions in a Macro

So far, all the macros you've recorded have changed the value of one or more properties of an object. Some actions that you can record don't change the value of a property. Let's see what a macro looks like when it doesn't change a property.

Suppose 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, and then create a macro that can change any formula to a value.

Convert a Formula to a Value Using Menu Commands

1. Activate the Budget window, and then select cell D4.

   

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

2. On the Edit menu, click the Copy command.

3. Don't change the selection. On the Edit menu, click the Paste Special command. The Paste Special dialog box appears. The Paste Special dialog box has two option groups- Paste and Operation-and two check boxes-Skip Blanks and Transpose. You can choose only one option within a group, and only one state for a check box, so you have four distinct options you can choose within the Paste Special dialog box.

   

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 selected cells is an action. Pasting the values of selected cells is a different action. When you execute the Copy command, you don't see a dialog box. Excel simply puts a moving border around the cells and a message in the status bar; 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. Some actions in Excel require additional information about how to carry out the action, and some don't.

Convert a Formula to a Value with a Macro

Record a macro to convert formulas to values. Compare how the macro recorder handles actions that display a dialog box to how it handles actions that don't.

1. On the Budget worksheet, select cell E4. Notice the formula in the formula bar: =E3-E54.

   Record Macro

   

2. On the VBA Toolbar, click Record Macro, replace the default name with ConvertToValues, set the shortcut key to Ctrl+Shift+V, and click OK.

3. On the Edit menu, click Copy.

4. On the Edit menu, click Paste Special, click the Values option, and click OK.

5. Press the Esc key to get rid of the moving border.

   Stop Recording

   

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 VBA to look at the recorded macro. 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. The last statement in the body of the macro, for example, sets the value of the CutCopyMode property in much the same way that the ToggleGrid macro changed the value of the DisplayGridlines property of the active window. The two statements that begin with Selection, however, are something new. Neither has a simple equal sign in it.

Selection.Copy looks similar to Selection.NumberFormat from the FormatCurrency macro. In that macro, NumberFormat was a property of the selection and you were assigning a new value to the NumberFormat property. Copy, however, isn't a property. That's why it doesn't have an equal sign after it. You don't assign anything to Copy; you just do it. Actions that don't use an equal sign to set the value of a property-that is, actions like Copy-are called methods. Like the names of properties, the names of methods are recorded by Excel and displayed at the end of the object's name.

When you use the Copy command from the menu, Excel doesn't ask 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. Similar to Copy, PasteSpecial is a method, not a property. Similar to Copy, PasteSpecial does not have an equal sign after it. When you execute the Paste Special command in Excel, you see a dialog box that lets you give extra information to the command. When you execute the PasteSpecial method in a macro, you need to give the same extra information to the method. The extra pieces of information you give to a method are called arguments.

Using a method with an object is like giving instructions to your nine-year-old son. With some instructions-like, 'Come eat'-you don't have to give any extra information. With other instructions-like, 'Go to the store for me'-you have to tell what to buy (milk), how to get there (on your bike), and when to come home (immediately). Giving these extra pieces of information to your son is like giving arguments to an Excel method. (You call them arguments because whenever you tell your son how to do something, you end up with one.)

The four arguments you give to PasteSpecial correspond exactly to the four option groups in the Paste Special dialog box. Each argument consists of a name for the argument (for example, Paste) joined to the argument value (for example, xlPasteValues) by a colon and an equal sign (:=).

Don't confuse an argument with a property. When you assign a new value to a property, you separate the value from the property with an equal sign, as in this statement:

ActiveWindow.DisplayGridlines = False

You read this statement as 'Let ‘False' be the DisplayGridlines state of the active window.'

Assigning a value to a property is superficially similar to using a named argument with a method. There is an equal sign involved in each case. When you assign a value to a property, you separate the property name from the property value with an equal sign (=). When you use a named argument with a method, you separate the argument name from the argument value with a colon plus an equal sign (:=). Confusing an equal sign with a colon plus equal sign is like confusing beer with root beer. Another difference is that the equal sign used with a property always has spaces on both sides, whereas the colon plus equal sign used with a named argument never has space on either side.

You separate the method name from the argument name with a space, and when you have more than one argument, separate each one from the next with a comma and a space, as in this statement:

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone

An argument looks a lot like a property, but a property is followed by an equal sign, while an argument is followed by a colon and an equal sign. Also, an argument follows a method, while a property follows an object. With a property, assigning the value to the property is what carries out the action, while an argument simply affects the way the method does its job.

Make a Long Statement More Readable

When a statement in a macro gets to be longer than about 70 characters, the macro recorder puts 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 settings of object properties, but this macro executes object methods. 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.