VBA Programming Series...3

I’m not much of a swimmer, but I have found that the best way to get into a cold body of water is to jump right in — no sense prolonging the agony.

First Things First

Before you can call yourself an Excel programmer, you must go through the initiation rites. That means you need to make a small change so Excel will dis-play a new tab at the top of the screen: Developer.

When you click the Developer tab, the Ribbon displays information that is of interest to programmers (that’s you!).

The Developer tab is not visible when you first open Excel; you need to tell Excel to show it. Getting Excel to display the Developer tab is easy (and you only have to do it one time). The procedure varies, though, depending on which version of Excel you use.

Excel 2010 Users

Follow these steps:

1. Right-click any part of the Ribbon, and choose Customize The Ribbon.

2. In the Customize Ribbon tab of the Excel Options dialog box, locate Developer in the second column.

3. Put a check mark next to Developer.

4. Click OK, and you’re back to Excel with a brand-new tab: Developer.

Excel 2007 Users

Follow these steps:

1. Choose File➪Excel Options.

Remember, in Excel 2007 the File command means clicking the round button in the upper left.

2. In the Excel Options dialog box, select Popular.

3. Place a check mark next to Show Developer tab in the Ribbon.

4. Click OK to see the new Developer tab displayed in the Ribbon.

What You’ll Be Doing

In this section, I describe how to create your first macro. The macro that you’re about to create will do this

✓ Type your name into a cell.

✓ Enter the current date and time into the cell below. ✓ Format both cells to display bold.

✓ Change the font size of both cells to 16 point.

This macro won’t be winning any prizes in the Annual VBA Programmer’s Competition, but everyone must start somewhere. The 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. Ready?

Taking the First Steps

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 (Ctrl+N is my favorite way to do that).

3. Click the Developer tab, and take a look at the Use Relative References button in the Code group.

If the color of that button is different than the other buttons, then you’re in good shape. If the Use Relative References button is the same color as the other buttons, then you need to click it. For now, just make sure that the option is turned on. When it’s turned on, it will be a different color.

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

3. Enter a name for the macro.

Excel provides a default name, but it’s better to use a more descriptive name. NameAndTime (with no spaces) is a good name for this macro.

4. Click in 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, then you can execute the macro by pressing a key combination — in this case, Ctrl+Shift+N.

5. Make sure the Store Macro In setting is This Workbook.

6. You can enter some text in the Description box if you like. This is optional. Some people like to describe what the macro does (or is sup-posed to do).

7. Click OK.

The dialog box closes, and Excel’s macro recorder is turned on. From this point, Excel monitors everything you do and converts it to VBA code.

8. Type your name in the active cell.

9. Move the cell pointer to 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.

This command converts the formula to its value.

12. With the date cell selected, press Shift+up arrow to select that cell and the one above it (which contains your name).

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 created your first Excel VBA macro. You may want to phone your mother and tell her the good news.

Testing the Macro

Now you can try out this macro and see whether it works properly. To test your macro, move to an empty cell and press Ctrl+Shift+N.

In a flash, Excel executes the macro. Your name and the current date are dis-played in large, bold letters.

Another way to execute the macro is to choose Developer➪Code➪Macros (or press Alt+F8) to display the Macros dialog box. Select the macro from the list (in this case, NameAndTime) and click Run. Make sure you select the cell that will hold your name before executing the macro.

Examining the Macro

So far, you’ve recorded a macro and you’ve tested it. If you’re a curious type, you’re probably wondering what this macro looks like. And you might even wonder where it’s stored.

Remember when you started recording the macro? You indicated that Excel should store the macro in This Workbook. The macro is stored in the work-book, but you need to activate the Visual Basic Editor (VBE, for short) to see it.

Follow these steps to see the macro:

1. Choose Developer➪Code➪Visual Basic (or press Alt+F11).

The Visual Basic Editor program window appears, as shown in Figure

2-3. This window is highly customizable, so your VBE window may look a bit different. The VBE program window contains several other windows and is probably very intimidating. Don’t fret; you’ll get used to it.

2. In the VBE window, locate the window called Project.

The Project window (also known as the Project Explorer window) con-tains 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 infor-mation) or contracted (to show less information).

The VBE uses quite a few different windows, any of which can be either 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 window 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. I explain more about the components of the Visual Basic Editor in Chapter 3.

3. Select the project that corresponds to the workbook in which you recorded the macro.

If you haven’t saved the workbook, the project is probably called VBAProject (Book1).

4. Click the plus sign (+) to the left of the folder named Modules.

The tree expands to show Module1, which is the only module in the project.

5. Double-click Module1.

At this point, the macro probably looks like Greek to you. Don’t worry. Travel a few chapters down the road, and all will be as clear as the view from Olympus.

The NameAndTime macro (also known as a Sub procedure) consists of several statements. Excel executes the statements one by one, from top to bottom. A statement preceded by an apostrophe (’) is a comment. Comments are included only for your information and are essentially ignored. In other words, Excel skips right over comments.

The first actual VBA statement (which begins with the word Sub) identifies the macro as a Sub procedure and gives its name — you provided this name before you started recording the macro. If you read through the code, you may be able to make sense of some of it. You see your name, the formula you entered, and lots of additional code that changes the font. The Sub procedure ends with the End Sub statement.