Excel VBA 24-Hour Trainer. Tom Urtis

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

Figure 3.1

Figure 3.2

      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.

Figure 3.3

      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 ViewProject Explorer. As indicated by the first item at the top of the Project Explorer window in Figure 3.3, the name of the workbook I am using (in Excel terms, the VBAProject) is MacroExamples.xlsm.

      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 in the Code window, as you see in Figure 3.3.

      The Code Window

      The Code window is where the code for macros and VBA procedures are located. The VBE provides separate code windows for each module. A good way to think of this is, for every object (worksheet, module, and so on) you see listed in the Project Explorer, the VBE has provided a Code window. Note that the drop-down in the upper right-hand corner of Figure 3.3 displays the name of the macro that is currently showing in the Code window (mySort). As you create multiple macros, you can use this drop-down to quickly move from one macro to another.

      The Properties Window

      The Properties window is located in the left vertical pane near the bottom of the VBE. If you do not see the Properties window in your VBE, press F4, or from the VBE menu bar click ViewProperties Window. This window displays a list of the properties and their assigned values of whatever object is selected in the Project Explorer window. For example, in Figure 3.3, Sheet1 has been selected and the Properties window shows you, among other details, that the Name property for the selected object is Sheet1.

      The Immediate Window

      The Immediate window is located at the bottom of the VBE, usually below the Code window, as shown in Figure 3.3. If you do not see the Immediate window in your VBE, press Ctrl+G, or from the VBE menu bar click ViewImmediate Window. The name “Immediate” has nothing to do with urgency, but rather with the notion that you can query a line of code and immediately obtain its returned result, without having to run a macro to see what that code line does. This comes in handy for code debugging tactics in Lesson 20, but for now I just wanted to point out the Immediate window to familiarize you with its name and location.

      Understanding Modules

      I touched on modules earlier, but they are worth another mention. A module is a container for your code. A single module may hold one or many macros, depending on the workbook and your preference for how you manage your code. For smaller projects with maybe two or three macros, just one module is sufficient. If you develop larger projects with dozens of macros, it's a good idea to organize them among several modules by theme or purpose.

      Several types of modules exist:

      • Standard modules: These are the kind you have seen already, which hold macros you create from scratch on your own or from the Macro Recorder.

      • UserForm modules: These belong to a custom user interface object called a UserForm, which is covered in Lessons 21, 22, and 23.

      • Class modules: These contain the kind of VBA code that enables you to create your own objects programmatically. Creating your own classes is very cool, and you learn about that in Lesson 24.

      • Worksheet modules: These hold VBA code that looks and acts like macros, but to make things interesting Microsoft refers to that code as a procedure instead of as a macro. Worksheet-level procedures