Creating a Simple Macro
Excel has a large collection of convenience tools readily available as shortcut keys and as buttons on toolbars. Sometimes, however, a built-in convenience tool doesn't work quite the way you want. Enhancing a built-in tool is a good first macro to create.
Show the Visual Basic Toolbar
Before you start creating the macros, take one small step that will make your work with macros much easier.
The buttons on the toolbar are supposed to resemble controls on a VCR. When you're ready to record a macro, click the circle on the toolbar-the 'record' button. Then, when you're ready to run a macro, click the triangle-the 'play' button.
Format Currency with a Built-In Tool
On the Formatting toolbar, Excel has a button that formats the current selection as currency: the Currency Style button.
The currency format that Excel applies when you click the Currency Style button has two decimal places. In your checkbook, you might want to display currency with two decimal places. But your budget contains estimates, and displaying values to the penny seems silly. You want to create a macro that formats selected cells as currency with no decimal places.
Record a Macro to Format Currency
That's it. You recorded a macro to format a selection with the currency format you want. Now you probably want to try the macro to see how it works.
Run the Macro
Your macro applies your customized currency format to the selected cells. Running the macro from the Macro dialog box isn't much easier than directly assigning the number format. To make the macro easy to run, you can use a shortcut key.
Assign a Shortcut Key to the Macro
Now you've successfully recorded, run, and enhanced a macro-all without seeing the macro itself. Maybe you'd like to actually see what you created.
Look at the Macro
The macro is hidden away in the workbook, and you need to open the Visual Basic Editor to see it.
The window has the caption Module1. A module is the place where the recorder puts macros. Your macro is in the Module1 module. The macro looks like this:
Sub FormatCurrency() ‘ ‘ FormatCurrency Macro ‘ Macro recorded 5/20/2001 by Reed Jacobson ‘ ‘ Selection.NumberFormat = "$#,##0" End Sub
The five lines that start with apostrophes are comments. An apostrophe tells Visual Basic to ignore all remaining text on the line. (The blank line among the comments, without even an apostrophe, is where the recorder would have put the shortcut key combination if you had assigned it when you recorded the macro.) The recorder puts in the comments partly to remind you to add comments as you write a macro. You can add to them, change them, or delete them as you want without changing how the macro runs. Comments appear in green to help you distinguish them from statements. Everything in Visual Basic that is not a comment is a statement. A statement tells Visual Basic what to do.
The first statement in the macro begins with Sub, followed by the name of the macro. This statement tells Visual Basic to begin a new macro. Perhaps the word Sub is used because a macro is typically hidden, or out of sight, like a submarine. Or maybe Sub is used for historical reasons. The last statement of a macro is always End Sub. This statement tells Visual Basic to stop running the macro.
All the statements between Sub and End Sub form the body of the macro. These are the statements that do the real work. The first (and only) statement in the body of the FormatCurrency macro begins with Selection.NumberFormat. The word Selection refers to an object-the currently selected range of cells. The word NumberFormat refers to an attribute-or property-of that object. Whenever the macro recorder creates a statement that contains an equal sign, the word to the left of the equal sign is a property. The property is preceded by an object, and the object and its property are separated by a period. The value to the right of the equal sign is what gets assigned to the property. In this example, the value assigned to the NumberFormat property is the number format code you saw earlier in the Format dialog box. To interpret a Visual Basic statement, read it from right to left. For example, read this statement as, 'Let such-and-such be the number format of the selection.'