8 3.1 3.2 The Visual Basic Editor Introduction At this point, you might be asking where VBA lives. I claimed in Chapter 1 that if you own Excel, you also own VBA, but many of you have probably never seen it. You do your VBA work in the Visual Basic Editor (VBE), which you can access easily from Excel by pressing the Alt+F11 key combination. The VBE provides a very user-friendly environment for writing your VBA programs. This chapter walks you through the VBE and shows you its most important features. It also helps you write your first VBA program. By the way, you might also hear the term Integrated Development Environment (IDE). This is a general term for an environment where you do your programming, regardless of the programming language. The VBE is the IDE for programming with VBA in Excel, Important Features of the VBE To understand this section most easily, you should follow along at your computer. Open Excel and press Alt+F11 to get into the VBE." It should look something Figure 3.1, although the configuration you see might be somewhat different. By the time this discussion is completed, you will be able to make your sereen look like that in Figure 3.1 or change it according to your own preferences. This is your pro- gramming workspace, and you have a lot of control over how it appears. This chapter provides some guidance, but the best way to learn is by experimenting. The large blank pane on the right is the Code window. This is where you write your code. (If any of the windows discussed here are not visible on your screen, you can select the View menu from the VBE and then select the window you want to make visible.) The rest of the VBE consists of the top menu, one or more toolbars, and one or more optional windows. Let’s start with the windows. "In Excel 2008 and eae, the Tools -> Maero ~+ Visual Basic Editor men item also gets you into the VBE, but Alt+F11 is quicker. In Excel 2007 and later versions, you should first make the Developer ribbon visible. To do this in Excel 2007, click the Office button and then Excel Options. Under the Popular tab, Select the third option at the top: Show Developer tab in the Ribbon. In Excel 2010 and later versions, rightclick any ribbon and seleet Customize the Ribbon. Then check the Developer item in the right pane. You need to do this only once, The Developer tab is a must for programmers. Among other things, you can gee to the VBE by clicking on its Visual Basic button, but again, AltsFLL is quicker‘The Visual Basic Editor 19 Figure 3.1. Visual Basic Editor (VBE) Type a question for hep 5-8 Personal (PERSONALS) 1-8 SoLveR (SoLvER-xLA) 5 Bf StatToolsxt (StatToolsa) 28 VoaProject (Bookz) EG) Marra Excel objets Shot (Sect!) Tsrbok SG Mees Radi The Project Explorer window, repeated in Figure 3.2, shows an Explorer- type list of all open projects. (Your list will probably be different from the one shown here. It depends on the files you have open and the add-ins that are loaded.) For example, the active project shown here has the generic name VBA- Project and corresponds to the workbook Book2—that is, the file Book2.xlsx.” Below a given project, the Project Explorer window shows its “elements.” In the Microsoft Excel Objects folder, these clements include any worksheets or chart sheets in the Excel file and an clement called ThisWorkbook, which refers to the workbook itself. There can also be folders for modules (for VBA code), user forms (for dialog boxes), and references (for links to other libraries of code you need), depending on whether you have any of these in your project. Modules, user forms, and references are discussed in detail in later chapters. 2For our purposes, there i no difference between a project and a workbook. However, VBA allows them to have separate names: VBAProject and Book2, for example. If you save Book2 as Practicealsm, sy, the project name will sll be VBAProject Admitely, it i somewhat confusing, but just think of projects as Excl les20. Chapter 3 Figure 3.2 Figure 3.3 Project Explorer Window © & Personal (PERSONAL.XLS) [© SS SOLVER (SOLVER.XLA) fe BS StatToolski (StatTools.xla) [88 vBaProject (Book2) -& Microsoft Excel Objects BB) Sheett (Sheet!) 8) ThisWorkbook 65 Modules The Properties window, shown in Figure 3.3, lists a set of properties. This list depends on what is currently selected. For example, the property list in Figure 3.3 is relevant for the project itself: It indicates a single property only—the project’s name, Therefore, if you want to change the name of the project from the generic VBAProject to something more meaningful, such as MyFirstProgram, this is the place to do it. Chapter 11 discusses the use of the Properties window in much more detail, At this point, you don’t really need the Properties window, so you can close it by clicking on its close button (the upper right X), The VBE also has at least three toolbars that are very useful: Standard, Edit, and Debug. They appear in Figures 3.4, 3.5, and 3.6, where some of the most useful buttons are indicated. (If any of these toolbars are not visible on your Properties Window eI Alphabetic | categorized | VeAProjectThe Visual Basic Editor 21 Figure 3.4 Standard Toolbar (Bs | 4 Pa 8 OO) ee | Se | @ | TC i Runa program ‘Show Control Toolbox Show Object Browser Pause a program cam prota ‘Show Properties Window ‘Show Project Window Figure 3.5 Edit Toolbar hh Bee Bae pa | \ \ Yoram sta block, Sate ‘Camment a block Seta Break Figure 3.6 Debug Toolbar Saas % [aaron] rl into] [Step over] computer, you can make them visible through the View menu.) From the Standard toolbar, you can run, pause, or stop a program you have written, You can also display the Project or Properties window (if itis hidden), and you can display the Object Browser or the Control Toolbox (more about these later). From the Edit toolbar, you can perform useful editing tasks, such as indenting or outdenting (the opposite of indenting), and you can comment or uncomment blocks of code, as is discussed later. Finally, although the Debug toolbar will probably not mean much at this point, itis invaluable when you need to debug your programs—as you will undoubtedly need to do. Itis discussed in more detail in Chapter 5. For fiature reference, here are a few menu items of particular importance. You usually need at least one module in a project. This is where you will typ: ically store your code. To insert a module, use the Insert > Module menu item, If you ever have a module you do not need, highlight the module in the Project Explorer window and use the File > Remove Module menu item. (Answer No to whether you want to export the module.)22 Chapter 3 3.3 ‘© Chapter 11 explains how to build your own dialog boxes. VBA calls these user forms. To insert a new user form into a project, use the Insert -> User Form menu item. You can delete an unwanted user form in the same way you delete a module. ‘© Under the Insert menu, there is also a Class Module item. You can usually ignore this. It is more advanced, but it is discussed briefly in Chapter 18. ‘© The Tools > Options menu item allows you to change the look and feel of the VBE in a variety of ways. You should probably leave the default settings alone— with one important exception. Try it now. Select Tools -* Options, and make sure the Require Variable Declarations box under the Editor tab is checked. ‘The effect of this is explained in Chapter 5. You might also want to uncheck the Auto Syntax Check box, as I always do. Ifit is checked, the editor beeps at you each time you make a syntax error ina line of code and then press Enter. This can be annoying. Even if this box is unchecked, the editor will still warn you about a syntax error by coloring the offending line red. ‘* Ifyou ever want to password-protect your project so that other people can- not see your code, use the Tools -> VBA Properties menu item and click the Protection tab. This gives you a chance to enter a password. (Just don’t forget it, or you will not be able to see your own code.) * Ifyou click the familiar Save button (or use the File + Save menu item), this saves the project currently highlighted in the Project Explorer window. It saves your code avd anything in the underlying Excel workbook. (It is all saved in the .xlsm file.) You can achieve the same objective by switching back to Excel and saving in the usual way from there. (Note, however, that in Excel 2007 and later versions, if your file started as an .xlsx file without any VBA code, you will have to save it as an .xlsm file once it contains code.) The Object Browser VBA’s Object Browser is a wonderful online help tool. To get to it, open the VBE and click the Object Browser button on the Standard toolbar (see Figure 3.4). Ifyou prefer keyboard shortcuts, you can press the F2 key. Either way, this opens the win- dow shown in Figure 3.7. At the top left, there is a dropdown list of Hibraries that you can get help on. Our main interest is in the Excel library, the VBA library, and, to a lesser extent, the Office library. ‘The Excel library provides help on all of the objects and their properties and methods in the Excel object model. The VBA library provides help on the VBA elements that are common to all applications that can use VBA: Excel, Access, Word, and others. The Office library provides help on objects, common to all Office programs, such as CommandBars objects (menus and toolbars) For now, sclect the Excel library. In the bottom left pane, you see a list of all objects in the Excel object model, and in the right pane, you see a list of all properties and methods for any object selected in the left pane. A property is des- ignated by a hand icon, and a method is designated by a green rectangular icon. A few objects, such as the Workbook object, also have events they can respond to. An event is designated by a lightning icon.