Excel VBA Programming For Dummies. Dick Kusleika
the font size of both cells to 16 point
This macro won’t be winning any prizes for Most Complicated Macro of the Year, but everyone must start somewhere. (And here’s a secret: The macros you use the most are the simplest ones.) This macro accomplishes all these steps in a single action. As I describe in the following sections, you start by recording your actions as you go through these steps. Then you test the macro to see whether it works. Finally, you edit the macro to add some finishing touches.
Preparing the Environment
This section describes the steps you take prior to recording the macro. In other words, you need to make a few preparations before the fun begins:
1 Start Excel, if it’s not already running.
2 If necessary, create a new, empty workbook.Pressing Ctrl+N is a quick way to do that.
3 Click the Developer tab, and then take a look at the Use Relative References button in the Code group.If the color of that button is different from the other buttons, you’re in good shape. If the Use Relative References button is the same color as the other buttons, you need to click it to enable this option.
You explore the Relative References button in Chapter 6. For now, just make sure that the option is turned on. When it’s turned on, the Use Relative References button is a different color from the other buttons in the group.
Recording a Macro
Here comes the hands-on part. Follow these instructions carefully:
1 Select a cell.Any cell will do.
2 Choose Developer ⇒ Code ⇒ Record Macro, or click the Macro Recording button on the status bar.The Record Macro dialog box appears, as shown in Figure 2-2.FIGURE 2-2: The Record Macro dialog box appears when you’re about to record a macro.
3 Enter a name for the macro.Excel provides a default name (something like Macro1), but it’s better to use a more descriptive name. NameAndTime (with no spaces) is a good name for this macro.
4 Click the Shortcut Key box, and enter Shift+N (for an uppercase N) as the shortcut key. Specifying a shortcut key is optional. If you do specify one, you can execute the macro by pressing a key combination — in this case, Ctrl+Shift+N. Be aware that if you assign a common shortcut key (for instance, Ctrl+C), you lose the normal functionality for that shortcut key; Excel will trigger your macro instead.
5 Verify that the Store Macro In setting is This Workbook.
6 (Optional) Enter some text in the Description box.Some people like to describe what the macro does (or is supposed to do).Figure 2-3 shows the Record Macro dialog box filled in with a name, optional shortcut, and optional description.FIGURE 2-3: The completed Record Macro dialog box.
7 Click OK.The Record Macro dialog box closes, Excel’s macro recorder is turned on, and the Record Macro button’s caption is changed to Stop Recording. From this point, Excel monitors everything you do and converts it to VBA code.
8 Type your name in the active cell.
9 Select the cell below and enter this formula: =NOW()The formula displays the current date and time.
10 Select the formula cell, and press Ctrl+C to copy that cell to the Clipboard.
11 Choose Home ⇒ Clipboard ⇒ Paste ⇒ Values (V).This command converts the formula to its value.
12 Select both the cell with your name and the one with the date and time.
13 Use the controls in the Home ⇒ Font group to change the formatting to Bold and make the font size 16 point.
14 Choose Developer ⇒ Code ⇒ Stop Recording.The macro recorder is turned off.
Congratulations! You just recorded your first Excel VBA macro. You might want to phone your mother and tell her the good news.
Running the Macro
Once you’ve finished writing your macro, it’s time to see if it actually works. The following are some of the ways to run a macro:
Press the shortcut key. If you created a shortcut key sequence for your macro, you can run it by press those keys. For example, in the previous section you assigned Ctrl+Shift+N in the Macro Options dialog. If you hold down Ctrl and Shift, and press N, your macro will insert your name and the date and format them.
Choose Developer ⇒ Code ⇒ Macros. This shows the Macro dialog where you can select your macro and click Run.
Run it from the VBE. Open the VBE, place the cursor anywhere within your macro, and choose Run ⇒ Run Sub/UserForm from the menu (or press F5).
Assign the macro to the Quick Access toolbar. Right-click on the Quick Access toolbar and choose Customize Quick Access Toolbar to show the Excel Options dialog. Choose Macros from the Choose commands from the drop-down dialog box and add your macro. Click OK to close the dialog. Now you can click the tool on the Quick Access toolbar to run the macro.
Whatever way you choose to run your macro, run it and make sure it does what you expect it to. If it doesn’t, read on to see how to view and edit it.
Viewing a Macro in the Visual Basic Editor
After you record and run a macro, you may be curious to see what the macro looks like. You might even wonder where it’s stored.
Excel stores macros in the workbook you indicate when creating the macro. So for the example used in this chapter, the macro is stored in This Workbook. To view macros, however, you need to activate the Visual Basic Editor (VBE, for short).
Follow these steps to see the macro used as an example throughout this chapter:
1 Choose Developer ⇒ Code ⇒ Visual Basic (or press Alt+F11).The Visual Basic Editor program window appears, as shown in Figure 2-4. This window is highly customizable, so your VBE window may look a bit different. The VBE window contains several other windows, which can be intimidating. Don’t fret; you’ll get used to it.
2 In the VBE window, locate the Project Explorer.The Project Explorer contains a list of all workbooks and add-ins that are currently open. Each project is arranged as a tree and can be expanded (to show more information) or contracted (to show less information). The VBE uses quite a few different windows, any of which can be open or closed. If a window isn’t immediately visible in the VBE, you can choose an option from the View menu to display the window. For instance, if the Project Explorer is not visible, you can choose View ⇒ Project Explorer (or press Ctrl+R) to display it. You can display any other VBE window in a similar manner.