Читать книгу Excel VBA Programming For Dummies - Dick Kusleika - Страница 60

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.