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
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.
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.
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.