Читать книгу Excel VBA 24-Hour Trainer - Tom Urtis - Страница 6

In this lesson, you practice creating a recorded macro.

Lesson Requirements

To get the sample workbook file, you can download Lesson 2 from the book's website at www.wrox.com/go/excelvba24hour.

Hints

Name your macros with a word or concise phrase that is easy to read and gives an idea about what the macro does. For example, a macro named Print_Expense_Report is more descriptive than Macro5.

Step-by-Step

Start with a worksheet on which some cells contain numbers that were manually entered, and other cells contain numbers produced by formulas, such as in the downloadable budget workbook shown in the video for this lesson. I have a number of steps in this “Try It” lesson to help demonstrate the value of a macro that can automatically perform tedious, recurring manual tasks with a simple keyboard shortcut or click of a button.

Create a macro that fills the manually entered numeric cells with one color, and the formula-containing numeric cells with another color:

1. Click the Record Macro button to turn on the Macro Recorder.

2. In the Record Macro dialog box, name the macro My_Macro and assign it the shortcut Ctrl+Shift+W.

3. Click OK to start recording your My_Macro macro.

4. Click the button above row 1 and to the left of column A to select the all the worksheet cells.

5. Show the Format Cells dialog box. Right-click any selected cell and select Format Cells from the menu, or press the Alt+O+E keyboard shortcut.

6. In the Format Cells dialog box, click the Fill tab. Click the No Color button and click OK to remove the fill colors from all cells.

7. With all the worksheet cells still selected, press the F5 key to show the Go To dialog box. Click the Special button.

8. In the Go To Special dialog box, select the option button for Constants, leave the Numbers check box selected, and deselect the check boxes for Text, Logicals, and Errors. Click OK.

9. Repeat Step 5 to show the Format Cells dialog box.

10. In the Format Cells dialog box, click the Fill tab, select a color from the palette, and click OK.

11. Repeat Step 4 to select all the worksheet cells.

12. Repeat Step 7 to show the Go To Special dialog box.

13. In the Go To Special dialog box, select the option button for Formulas, leave the Numbers check box selected, and deselect the check boxes for Text, Logicals, and Errors. Click OK.

14. Repeat Step 5 to show the Format Cells dialog box.

15. In the Format Cells dialog box, click the Fill tab, select a color from the palette that is different from the color you selected for Constants in Step 10, and click OK.

16. Select any cell on the worksheet to deselect all the selected special cells.

17. Turn off the Macro Recorder by clicking the Stop Recording button.

18. Before running your new macro to see it in action, repeat Steps 4, 5, and 6 to remove the fill color from all cells.

19. Show the Macro dialog box to run your macro. You can either click the Developer tab on the Ribbon and then click the Macros icon in the Code panel, or you can press the Alt+F8 keyboard shortcut.

20. To run your My_Macro macro from the Macro dialog box, select its name in the list box and click the Run button, or double-click its name in the list box.

21. To run your My_Macro macro using your keyboard, press the Ctrl+Shift+W shortcut keys you assigned in Step 2.

REFERENCE Please select the video for Lesson 2 online at www.wrox.com/go/excelvba24hour. You will also be able to download the code and resources for this lesson from the website.