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 ...