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.
Lesson 3
Introducing the Visual Basic Editor
Lesson 2 explains how to create a macro, and you saw a couple of easy ways to run the macro you created. Now it's time to view your macro and have a look at the environment called the Visual Basic Editor (VBE), within which all macros and VBA procedures are stored. Seeing where macros live and breathe improves your understanding of the VBA programming process, especially when you start to edit existing macros or create new macros without the Macro Recorder.
What is the VBE?
It's fair to say that for many users of Excel, the worksheets, pivot tables, charts, and hundreds of formula functions are all the tools they need to satisfactorily handle their spreadsheet activities. For them, the familiar workbook environment is the only side of Excel they see, and understandably the only side of Excel they are probably aware of.
But Excel has a separate, less visible environment working behind the scenes – the Visual Basic Editor – which is interconnected with the workbook environment even if no programming code exists in the workbook. Both environments are constantly but quietly working together, sharing information back and forth about the entire workbook. The Visual Basic Editor is a user-friendly development environment where programmed instructions are maintained in order to make your spreadsheet applications work.
How to Get Into the VBE
With Excel open, a fast and easy way to get into the Visual Basic Editor is to press Alt+F11 on your keyboard. You can do this from any worksheet. It's just as quick with your mouse, too; you click the Visual Basic Editor icon on the Visual Basic toolbar in versions up to 2003, as shown in Figure 3.1, or the Visual Basic button from the Developer tab on the Ribbon in later versions, as shown in Figure 3.2.
NOTE If you don't see the Developer tab on your Ribbon, see the steps to show it in Lesson 2, in the section “Accessing the VBA Environment.”
CAREFUL, THAT WAS ALT+F11!
The Ctrl key is commonly used in conjunction with other keys for keyboard shortcuts. By force of habit, you might mistakenly press Ctrl+F11 instead of Alt+F11 when attempting to go to the VBE. However, pressing Ctrl+F11 has a curious result: Instead of being taken to the VBE, you will have created an outdated type of sheet called a macro sheet, with the strange tab name of Macro1. Prior to Excel version 97, macros were stored on macro sheets, which you can still create, though they have no practical use with today's Excel, and they no longer hold any programming code. It's OK to just delete the macro sheet if you create one, and take another stab at the Alt key with F11 to get into the VBE.
Understanding the VBE
The Visual Basic Editor can show a number of different windows, depending on what you want to see or do. For the majority of work you do with the help of this book, you want to eventually become familiar with four windows: the Project Explorer window, the Code window, the Properties window, and the Immediate window. Figure 3.3 shows what the VBE looks like with these four windows.
The Project Explorer Window
The Project Explorer is a vertical pane on the left side of the VBE. It behaves similarly to Windows Explorer, with folder icons that expand and collapse when clicked. If you do not see the Project Explorer window in your VBE, press Ctrl+R, or from the VBE menu bar, click View
VBA code is kept in objects known as modules, which are discussed later in further detail. Figure 3.3 shows one module called Module1. Double-clicking a module name in the Project Explorer displays that module's VBA code contents
