Creating a Simple Macro

Excel has a large collection of tools conveniently available on the Ribbon or on shortcut menus. Sometimes, however, a built-in tool doesn’t work quite the way you want. Creating a macro to enhance a built-in tool is a good way to get started with writing macros.

Format Currency by Using a Built-In Tool

On the Home tab of the Ribbon is a Number Format list from which you can choose any of several built-in formats. You can use the list to apply a Currency format to the selected cells.

1. In the Chapter01 workbook, select cells D3:F4 on the Budget worksheet.

2. On the Home tab of the Ribbon, click the Number Format arrow, and click Currency.

   

   Excel reformats the selected cells as Currency with two decimal places.

   

   Tip 

   In addition to selecting Currency from the list, you can get the same result by clicking the Accounting Number Format button. The distinction between the Currency and Accounting formats can be confusing, especially because the Excel user interface (UI) is inconsistent. Here’s how it works: Excel has two different types of formats that look like currency. One is called Currency and the other is called Accounting. The Currency format puts the currency symbol immediately in front of the number. The Accounting format puts the currency symbol to the far left of the cell. In Excel 2007, even though the name on the Ribbon button is Accounting Number Format, the default format it applies is Currency. To use the Accounting Number Format button to apply an Accounting format, click the arrow and choose one of the specific currency types-for example, $ English (U.S.). Unless you are doing specific accounting applications, the Currency format is usually easier to read.

Record a Macro to Format Currency

The standard Currency format has two decimal places. Two decimal places may be appropriate in something like a checkbook, where you know the exact amount you spent. But a budget worksheet contains estimates, and displaying estimated values to the penny seems silly. You can create a macro that formats the currently selected cells as Currency, but with no decimal places.

On the status bar, immediately to the right of the cell mode (Ready) indicator, in the bottom-left corner of the Excel window, is a button that will start the macro recorder. When no macro is currently recording, you can think of this as a Record Macro button. If the Record Macro button is not visible in the status bar, right-click the status bar, select the check box for Macro Recording, and click OK.

Troubleshooting 

The Record Macro button is very close, both in appearance and in location, to the Insert Worksheet button, so be careful to click the right one.

1. On the Budget worksheet, select cells D7:F8.

2. On the status bar, click the Record Macro button.

   
   Record Macro

3. Replace the default macro name with FormatCurrency, and then click OK.

   A macro name must begin with a letter, and it can contain uppercase and lower-case letters, numbers, and underscores, but no spaces or other special characters.

   

   The only apparent change is that the icon on the Record Macro button changes to a square, so you can now think of it as a Stop Recording button, which happens to be what the tool tip says it is. The Stop Recording button is like an “On the Air” button at a TV studio: You’re recording.

4. Right-click in the selected range, and click Format Cells. If necessary, click the Number tab in the Format Cells dialog box.

5. Select Currency from the Category list.

6. Replace the value in the Decimal Places box with 0.

   

7. Select Custom from the Category list, and look in the Type box.

   The characters $#,##0 represent a currency format with no decimal places. This is the format that gets applied to the selected cells.

   See Also 

   To learn more about format strings, click the Office Excel Help Button and type “number format codes” in the search box.

8. Click OK to format the selected cells as currency without decimal places.

9. On the status bar, click the Stop Recording button.

   
   Stop Recording

That’s it. You have recorded a macro to format a selection with the currency format you want.

Run the Macro

Now you probably want to try the macro to see how it works.

1. On the Budget worksheet, select cells D9:F10.

2. On the View tab of the Ribbon, click the Macros button at the right end of the tab.

3. Select the FormatCurrency macro in the list, and click Run.

   

It worked! You ran your first macro, and the macro applied your customized currency format to the selected cells.

Tip 

In the Excel Options dialog box, on the Popular page, is a check box labeled Show Developer Tab In The Ribbon. You can enable the Developer tab if you want, but it doesn’t really make it any easier to write macros. The Developer tab contains three sections: The Code section includes buttons that are already easily available-either as the Record Macro button on the status bar or as the Macros button on the View tab of the Ribbon. The Controls section contains items that are very useful when adding controls to a worksheet. The XML section has nothing to do with creating macros.

Assign a Shortcut Key to the Macro

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

1. On the View tab of the Ribbon, click the Macros arrow, and click View Macros.

2. Select the FormatCurrency macro in the list, and then click the Options button.

   The Macro Options dialog box allows you to change the macro’s shortcut key assignment and its description. Other than the fact that you can’t change the name, this dialog box looks very similar to the Record Macro dialog box, and in fact you can also assign a shortcut key at the time you first record a macro.

3. With the box below Shortcut Key selected, press Shift+C.

   As you can see by the label next to the box, this assigns Ctrl+Shift+C as the shortcut key.

   

   Important 

   Excel uses many Ctrl key combinations as built-in shortcuts. For example, Ctrl+C is Copy and Ctrl+Z is Undo. If you assign one of these shortcuts to your macro, pressing the shortcut key combination runs your macro rather than the built-in command. If you always use Ctrl+Shift key combinations when you assign shortcut keys to your macros, you’ll be less likely to override a built-in shortcut.

4. Click OK to return to the Macro dialog box, and then click Cancel to return to the worksheet.

5. Select cells D11:F12, and press Ctrl+Shift+C to run 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.

1. On the View tab of the Ribbon, click the Macros arrow, and click View Macros.

2. Click FormatCurrency, and then click Edit.

   The Visual Basic editor window opens. The Visual Basic editor appears to be a separate program, but it is “owned” by Excel-that is, if you quit Excel, the editor automatically shuts down. Inside the Visual Basic editor, a window captioned Module1 appears as well.

3. Maximize the Module1 window so that it fills the editor, and then resize the editor window so that you can see the Excel workbook in the background.

4. If any other windows are visible in the Visual Basic editor, close them now.

   

The window has the caption Module1. A module is the place where the recorder stores macros. Your macro is in the Module1 module. The macro looks like this:

Sub FormatCurrency() ' ' FormatCurrency Macro ' '   Selection.NumberFormat = "$#,##0" End Sub 

The four lines that start with apostrophes are comments. An apostrophe tells Visual Basic to ignore all subsequent 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 inserts the comments 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 perhaps it is because macro-writers are sort of like hackers, and they are known to be subversive. Or maybe Sub is just used for boring historical reasons. The final statement of the macro is End Sub. This statement tells Visual Basic to come back to the surface.

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 some thing in Excel-in this case, the currently selected range of cells. The thing is called an object. Remember that objects are basically just a way of logically organizing the million of commands that are possible in Excel. Specifying Selection as the object says that the following instruction is going to have to do with a range of cells.

The word NumberFormat refers to an attribute-or property-of the range of cells. One way that Excel carries out an action is by assigning a new value to a property. In this case, it assigns the new format string to the NumberFormat property. In essence, the statement says, “Hey Excel, I’ve got a range here. Change the number format to look like this custom currency format, OK?”

This macro assumes that the current selection is a range of cells. If you happen to select a graphical shape and then run the macro, it fails. That’s because when the macro says, “Hey Excel, I’ve got a rectangle here. Change the number format, OK?” Excel says, “No way! It doesn’t make sense to change the number format of a rectangle, stupid!”

Of course, it doesn't use those words. It says “Object doesn’t support this property or method,” but the meaning is about the same. Later, in Chapter 8, “Extend Excel and Visual Basic,” you’ll learn how to fix a macro so that you can give it to friends without the risk of having Excel tell them that they’re stupid.

Save the Macro Workbook

If you’ve been following the instructions very carefully, you have not yet saved your workbook. If you have tried to save your workbook, you’ve received an ominous message about how a Visual Basic project cannot be saved in a macro-free workbook. This is a little bit annoying, but it is a nice new feature of the 2007 Microsoft Office release. As I’m sure you know, evil people have figured out that they can use Excel workbooks and Word documents to spread viruses and other infestations. I distinctly remember the time when I spread a virus all over my company because I got a workbook from a “trusted” colleague and so I told Excel it was OK to enable the macro. The problem was not that my colleague was untrustworthy; it was that he didn’t even know that the workbook he sent me had a virus macro in it.

So now, in the 2007 Office release, a standard workbook is macro-free: it is guaranteed not to have any macros in it. There’s no way it can spread a virus. If you do put a macro into a workbook, you must save it as a macro-enabled workbook.

Important 

Macro-free workbooks happen to have the extension .xlsx, and macro-enabled workbooks have the extension .xlsm, but the file extension is not what matters. Macros are stored in a special section inside the workbook. Excel can easily tell whether the workbook contains a macro by looking at the content list of the workbook file; it doesn’t even have to open the part of the workbook that contains the macro and could therefore be potentially dangerous. If you change the extension of a macro-enabled workbook to.xlsx, you just get an error when you try to open the workbook.

1. Click the Microsoft Office Button, point to the Save As arrow, click Excel Macro-Enabled Workbook, and click Save.

   This saves the workbook as Chapter01.xlsm.

2. Close the workbook, and use the Recent Files list on the Office menu to open it again.

   The file opens, but there’s an alert message under the Ribbon that warns you that the macros are disabled for your safety.

   

3. On the security bar, click the Options button.

4. In the Microsoft Office Security Options dialog box, select Enable This Content.

   

5. Click OK to enable the macros and make the security bar disappear.

6. Select cells D13:F13 and press Ctrl+Shift+C to format them as customize currency to make sure that the macro you created does work.

For your protection, Excel requires you to enable the macros each time you open the workbook. Even if you don’t enable the macros, but then re-save the workbook, the macros don’t go away. The next time you open the workbook you will still get the option to enable the macros. At the end of this chapter, you’ll learn how to trust specific macro-enabled workbooks so that you don’t have to enable the macros each time.