Dick Kusleika - Excel VBA Programming For Dummies

For the record, the following concepts were used to create this simple macro, all of which are covered later in this book:

Defining a Sub procedure (the first line)

Assigning values to variables (Msg and Ans)

Concatenating (joining) a string (using the & operator)

Using a built-in VBA function (MsgBox)

Using built-in VBA constants (vbYesNo, vbNo, and vbYes)

Using an If-Then construct (twice)

Ending a Sub procedure (the last line)

Not bad for a beginner, eh?

Another way you can get code into a VBA module is by recording your actions, using the Excel macro recorder. If you worked through the hands-on exercise in Chapter 2, you already have some experience with this technique.

By the way, there is absolutely no way you can record the GuessName procedure shown in the preceding section. You can record only things that you can do directly in Excel. Displaying a message box is not in Excel’s normal repertoire. (It’s a VBA thing.) The macro recorder is useful, but in many cases, you’ll probably need to enter at least some code manually.

Here’s a step-by-step example that shows how to record a macro that inserts a new worksheet and hides all but the first 10 rows and all but the first 10 columns. If you want to try this example, follow these steps:

1 Open a new, blank workbook.

2 Click the Developer tab, and make sure that Use Relative References is not highlighted.This macro is recorded using Absolute References.

3 Choose Developer ⇒ Code ⇒ Record Macro, or click the icon next to the Ready indicator on the left end of the status bar.Excel displays its Record Macro dialog box.

4 In the Record Macro dialog box, name the macro TenByTen, specify that you want the macro stored in This Workbook, and press Shift+T for the shortcut key.The macro can be executed when you press Ctrl+Shift+T.

5 Click OK to start recording.Excel automatically inserts a new VBA module into the project that corresponds to the active workbook. From this point on, Excel converts your actions to VBA code. While you’re recording, the icon in the status bar turns into a small square, which is a reminder that the macro recorder is running. You can also click that icon to stop the macro recorder.

6 Click the New Sheet icon to the right of the last sheet tab.Excel inserts a new worksheet.

7 Select the entire Column K (the 11th column) and press Ctrl+Shift+right arrow; then right-click any selected column and choose Hide from the shortcut menu.Excel hides all the selected columns.

8 Select the entire Row 11 and press Ctrl+Shift+down arrow; then right-click any selected row and choose Hide from the shortcut menu.Excel hides all the selected rows.

9 Select cell A1.

10 Choose Developer ⇒ Code ⇒ Stop Recording, or click the Stop Recording button on the status bar (the small square).Excel stops recording your actions.

To view this newly recorded macro, press Alt+F11 to activate the VBE. Locate the workbook’s name in the Project Explorer. You see that the project has a new module listed. The name of the module depends on whether you had any other modules in the workbook when you started recording the macro. If you didn’t, the module is named Module1. You can double-click the module to view the code.

Here’s the code generated by your actions:

Sub TenByTen()'' TenByTen Macro'' Keyboard Shortcut: Ctrl+Shift+T' Sheets.Add After:=ActiveSheet Columns("K:K").Select Range(Selection, Selection.End(xlToRight)).Select Selection.EntireColumn.Hidden = True Rows("11:11").Select Range(Selection, Selection.End(xlDown)).Select Selection.EntireRow.Hidden = True Range("A1").SelectEnd Sub

To try this macro, activate any worksheet and press the shortcut key that you assigned in Step 4: Ctrl+Shift+T.

If you didn’t assign a shortcut key to the macro, don’t worry. Here’s how to display a list of all macros available and run the one you want:

1 Choose Developer ⇒ Code ⇒ Macros.Keyboard fans can press Alt+F8. Either of these methods displays a dialog box that lists all the available macros.

2 Select the macro in the list (in this case, TenByTen).

3 Click the Run button.Excel executes the macro, and you get a new worksheet with 10 visible rows and 10 visible columns.

You can execute any number of commands and perform any number of actions while the macro recorder is running. Excel dutifully translates your mouse actions and keystrokes to VBA code.

And, of course, you can also edit the macro after you record it. To test your new skills, try editing the macro so that it inserts a worksheet with nine visible rows and columns — perfect for a Sudoku puzzle.

The final method for getting code into a VBA module is to copy it from another module or from some other place (such as a website). For example, a Sub or Function procedure that you write for one project might also be useful in another project. Instead of wasting time reentering the code, you can activate the module and use the normal Clipboard copy-and-paste procedures. (You can use the keyboard shortcuts Ctrl+C to copy and Ctrl+V to paste.) After pasting the code into a VBA module, you can modify the code if necessary.

By the way, you can find lots of VBA code examples on the web. If you want to try them, select the code in your browser and press Ctrl+C to copy it. Then activate a module and press Ctrl+V to paste it.

When you copy code from a website, it sometimes requires some fixing. For example, quote characters may be “smart quotes” and they must be converted to simple quote characters. And sometimes, long lines wrap around. Erroneous statements are easy to spot in the VBE because they appear in red.

If you’re serious about becoming an Excel programmer, you’ll spend a lot of time with VBA modules on your screen. To help make things as comfortable as possible (no, please keep your shoes on), the VBE provides quite a few customization options.

When the VBE is active, choose Tools ⇒ Options. You’ll see a dialog box with four tabs: Editor, Editor Format, General, and Docking. The sections that follow discuss the most useful options of those contained in each tab.

Figure 3-5 shows the options you can access by clicking the Editor tab of the Options dialog box. Use the following options in the Editor tab to control how certain things work in the VBE.

Auto Syntax Check option: The Auto Syntax Check setting determines whether the VBE pops up a dialog box if it discovers a syntax error while you’re entering your VBA code. The dialog box tells roughly what the problem is. If you don’t choose this setting, the VBE flags syntax errors by displaying them in a different color from the rest of the code, and you don’t have to deal with any dialog boxes popping up on your screen. FIGURE 3-5:The Editor tab of the Options dialog box.

Require Variable Declaration option: If the Require Variable Declaration option is set, VBE inserts the following statement at the beginning of each new VBA module you insert: Option ExplicitChanging this setting affects only new modules, not existing modules. If this statement appears in your module, you must explicitly define each variable you use. Chapter 7goes into the details why you should develop this habit.