Programming Excel with VBA and .NET

Where’s My Code?

Excel stores Visual Basic code in the workbook (.xls), template (.xlt), or add-in (.xla) file when you save it. File formats other than those omit the Visual Basic code the same way that special formatting is lost when you save a workbook as a text (.txt) or comma-delimited file (.csv).

You can view the code in a currently open Excel file by pressing Alt-F11, by choosing Tools → Macro → Visual Basic Editor or by clicking the Visual Basic Editor button on the Visual Basic toolbar (Figure 1-26).

Figure 1-26. The Visual Basic toolbar lets you edit, run, or stop code; create controls; and set macro security

Within the editor, code is organized into modules and classes . Modules are static code files that typically contain recorded code and public procedures that you want users to be able to call directly from Excel. Classes are associated with an instance of an object in Excel, such as a workbook or worksheet. Classes usually contain code that responds to Excel events, such as when a command button is clicked or when the user opens the workbook.

Excel creates a new module called Module1 when you first record code as shown earlier in this chapter. Excel provides a class for each new sheet you add to a workbook. Similarly, Excel deletes that sheet’s class when you delete the sheet from the workbook, so be careful when deleting sheets while programming! You can see a workbook’s classes and modules in the editor’s Project window (Figure 1-27).

You can also use the Project window to export classes or modules to text files and to import code stored as text into the workbook. Unfortunately, there’s no easy way to store code separately from the workbook (which would be nice when more than one person is working on code).

Visual Basic displays information about each class or module in the Properties window below the Project window, as shown in Figure 1-28. As you select a different item in the Project window, the item displayed in the Properties window changes.

You can use the Properties window to rename modules or classes or to control various aspects of a class. For example, to rename Module1 something descriptive, like RecordedCode, select Module1 in the Project window and type RecordedCode in the (Name) property of the Properties window. You can also use the Properties window to hide sheets by setting the class’s Visible property.

Figure 1-27. Double-click on a class or module to open it in a code window

Figure 1-28. Select ThisWorkbook in the Project window to see the workbook’s properties