Excel VBA Programming For Dummies. Dick Kusleika
Читать онлайн книгу.
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.
Copying VBA code
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.
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.
Customizing the VBE
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.
Using the Editor 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 7 goes into the details why you should develop this habit.
Auto List Members option: If the Auto List Members option is set, the VBE provides some help when you’re entering your VBA code. It displays a list that would logically complete the statement you’re typing. This bit of magic is sometimes called IntelliSense. It helps you write code more quickly and accurately, making it one of the best features of the VBE. Figure 3-6 shows an example (which will make lots more sense when you start writing VBA code).FIGURE 3-6: An example of Auto List Members.
Auto Quick Info option: If the Auto Quick Info option is set, the VBE displays information about functions and their arguments as you type, which can be very helpful. Figure 3-7 shows this feature in action, telling you about the arguments for the MsgBox function.FIGURE 3-7: Auto Quick Info offers help about the MsgBox function.
Auto Data Tips option: If the Auto Data Tips option is set, the VBE displays the value of the variable over which your cursor is placed when you’re debugging code. When you enter the wonderful world of debugging,