last cell in a workbook), those who use a version prior to Excel 2007 will receive an error because those pre-Excel 2007 worksheets had only 65,536 rows and 255 columns (the last cell is IV65536).
Excel 2010 and later also have some new objects, methods, and properties. If you use these in your code, users with an older version of Excel will receive an error when they run your macro — and you’ll get the blame.
Chapter 2
Building Simple Macros
IN THIS CHAPTER
The best way to get into a cold body of water is to jump right in — no sense prolonging the agony. By wading through this chapter, you can get your feet wet immediately but avoid getting in over your head.
By the time you reach the end of this chapter, you’ll start feeling better about this Excel programming business, and you’ll be glad you took the plunge. This chapter provides a step-by-step demonstration of how to develop a simple but useful VBA macro.
Displaying the Developer Tab
Before you can call yourself an Excel programmer, you need to learn the secret handshake. That means you need to make a small change so that Excel will display a new tab at the top of the screen: Developer. Getting Excel to display the Developer tab is easy (and you only have to do it once). Just follow these steps:
1 Right-click any part of the Ribbon and choose Customize the Ribbon from the shortcut menu.
2 In the Customize Ribbon tab of the Excel Options dialog box, locate Developer in the box on the right.
3 Put a check mark next to Developer.
4 Click OK.You’re back to Excel with a brand-new tab: Developer.
When you click the Developer tab, the Ribbon displays information that is of interest to programmers (that’s you!). Figure 2-1 shows how the Ribbon looks when the Developer tab is selected.
FIGURE 2-1: The Developer tab is normally hidden, but it’s easy to unhide.
Creating a Macro
In this chapter, you create your first macro. The macro that you create does the following:
Types your name in a cell
Enters the current date and time in the cell below
Formats both cells to display bold
Changes 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
