VBA Programming Series...5

As a more-experienced-than-average Excel user, you probably know a good deal about workbooks, formulas, charts, and other Excel goodies. Now

it’s time to expand your horizons and explore an entirely new aspect of Excel: the Visual Basic Editor. In this article, you find out how to work with the Visual Basic Editor, and you get down to the nitty-gritty of writing some VBA code.

What Is the Visual Basic Editor?

I’m going to save some wear and tear on my fingers, and refer to the Visual Basic Editor as the VBE. The VBE is a separate application where you write and edit your VBA macros. It works seamlessly with Excel. By seamlessly, I mean that Excel takes care of opening the VBE when you need it.

You can’t run the VBE separately; Excel must be running in order for the VBE to run.

Activating the VBE

The quickest way to activate the VBE is to press Alt+F11 when Excel is active.

To return to Excel, press Alt+F11 again.

You can also activate the VBE by using the Developer➪Code➪Visual Basic command. If you don’t have a Developer tab at the top of your Excel window, flip back to previous article where I explain how to get that handy Developer tab to show up.

Understanding VBE components

Figure shows the VBE program, with some of the key parts identified. Because so much is going on in the VBE, I like to maximize the window to see as much as possible.

Chances are your VBE program window won’t look exactly like what you see in Figure. The VBE contains several windows, and it’s highly customiz-able. You can hide windows, rearrange windows, dock windows, and so on.

Actually, the VBE has even more parts than are shown in Figure. I discuss these additional components throughout this series, when they become relevant.

Menu bar

The VBE menu bar works just like every other menu bar you’ve encountered. It contains commands that you use to do things with the various components in the VBE. You also find that many of the menu commands have shortcut keys associated with them.

The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands.

Toolbar

The Standard toolbar, which is directly under the menu bar by default (refer to Figure), is one of four VBE toolbars available. You can customize the toolbars, move them around, display other toolbars, and so on. If you’re so inclined, use the View➪Toolbars command to work with VBE toolbars. Most people (including me) just leave them as they are.

Project window

The Project window displays a tree diagram that shows every workbook cur-rently open in Excel (including add-ins and hidden workbooks). Double-click items to expand or contract them. I discuss this window in more detail in the upcoming “Working with the Project Window” section.

If the Project window is not visible, press Ctrl+R or use the View➪Project Explorer command. To hide the Project window, click the Close button in its title bar. Or right-click anywhere in the Project window and select Hide from the shortcut menu.

Code window

A Code window contains VBA code. Every object in a project has an associ-ated Code window. To view an object’s Code window, double-click the object in the Project window. For example, to view the Code window for the Sheet1 object, double-click Sheet1 in the Project window. Unless you’ve added some VBA code, the Code window will be empty.

You find out more about Code windows later in this article’s “Working with a Code Window” section.

Immediate window

The Immediate window may or may not be visible. If it isn’t visible, press Ctrl+G or use the View➪Immediate Window command. To close the Immediate window, click the Close button in its title bar (or right-click any-where in the Immediate window and select Hide from the shortcut menu).

The Immediate window is most useful for executing VBA statements directly and for debugging your code. If you’re just starting out with VBA, this window won’t be all that useful, so feel free to hide it and free up some screen space for other things.

What’s new in the Visual Basic Editor?

Excel 2007 introduced a brand-new user inter-face. Menus and toolbars are gone, and the new “Ribbon” replaces them. If you’ve used the Visual Basic Editor in a previous ver-sion of Excel, you’ll be in familiar territory. In Excel 2007, Microsoft left the VBE essentially untouched. And they continued the hands-off tradition in Excel 2010.The VBA programming language has been updated to accommodate the new Excel fea-tures, but the VBE has no new features, and the old-style toolbars and menus work exactly like they always have. Maybe they’ll eventually get around to updating the VBE, but I’m not holding my breath.

Working with the Project Window

When you’re working in the VBE, each Excel workbook and add-in that’s open is a project. You can think of a project as a collection of objects arranged as an outline. You can expand a project by clicking the plus sign (+) at the left of the project’s name in the Project window. Contract a project by clicking the minus sign (–) to the left of a project’s name. Or you can double-click the items to expand and contract them.

Figure shows a Project window with three projects listed: an add-in named pup7.xlam, a workbook named investments.xlsm, and the Personal Macro Workbook (which is always named PERSONAL.XLSB).

Every project expands to show at least one node called Microsoft Excel Objects. This node expands to show an item for each sheet in the work-book (each sheet is considered an object) and another object called ThisWorkbook (which represents the Workbook object). If the project has any VBA modules, the project listing also shows a Modules node. And, as you see in Part IV, a project may also contain a node called Forms, which con-tains UserForm objects (which hold custom dialog boxes).

The concept of objects may be a bit fuzzy for you. However, I guarantee that things become much clearer in subsequent articles. Don’t be too concerned if you don’t understand what’s going on at this point.

Adding a new VBA module

Follow these steps to add a new VBA module to a project:

1. Select the project’s name in the Project window.

2. Choose Insert➪Module.

Or

1. Right-click the project’s name.

2. Choose Insert➪Module from the shortcut menu.

When you record a macro, Excel automatically inserts a VBA module to hold the recorded code. Which workbook holds the module for the recorded macro depends on where you chose to store the recorded macro, just before you started recording.

Removing a VBA module

Need to remove a VBA module from a project?

1. Select the module’s name in the Project window.

2. Choose File➪Remove xxx, where xxx is the module name.

Or

1. Right-click the module’s name.

2. Choose Remove xxx from the shortcut menu.

Excel, always trying to keep you from doing something you’ll regret, will ask if you want to export the code in the module before you delete it. Almost always, you don’t. (If you do want to export the module, see the next section.)

You can remove VBA modules, but there is no way to remove the other code modules — those for the Sheet objects or ThisWorkbook.

Exporting and importing objects

Every object in a VBA project can be saved to a separate file. Saving an indi-vidual object in a project is known as exporting. It stands to reason that you can also import objects to a project. Exporting and importing objects might be useful if you want to use a particular object (such as a VBA module or a UserForm) in a different project.

Follow these steps to export an object:

1. Select an object in the Project window.

2. Choose File➪Export File or press Ctrl+E.

You get a dialog box that asks for a filename. Note that the object remains in the project; only a copy of it is exported.

Importing a file to a project goes like this:

1. Select the project’s name in the Explorer window.

2. Choose File➪Import File or press Ctrl+M.

You get a dialog box that asks for a file. Locate the file and click Open. You should only import a file if the file was exported by using the File➪Export File command.

Working with a Code Window

As you become proficient with VBA, you spend lots of time working in Code windows. Macros that you record are stored in a module, and you can type VBA code directly into a VBA module.

Minimizing and maximizing windows

If you have several projects open, the VBE may have lots of Code windows at any given time. Figure shows an example of what I mean.

Code windows are much like workbook windows in Excel. You can minimize them, maximize them, resize them, hide them, rearrange them, and so on. Most people find it much easier to maximize the Code window that they’re working on. Doing so lets you see more code and keeps you from getting distracted.

To maximize a Code window, click the Maximize button in its title bar (right next to the X). Or just double-click its title bar to maximize it. To restore a Code window to its original size, click the Restore button. When a window is maximized, its title bar isn’t visible, so you’ll find the Restore button below the VBE title bar.

Sometimes, you may want to have two or more Code windows visible. For example, you may want to compare the code in two modules or copy code from one module to another. You can arrange the windows manually, or use the Window➪Tile Horizontally or Window➪Tile Vertically command to arrange them automatically.

You can quickly switch among code windows by pressing Ctrl+Tab. If you repeat that key combination, you keep cycling through all the open code win-dows. Pressing Ctrl+Shift+Tab cycles through the windows in reverse order.

Minimizing a Code window gets it out of the way. You can also click the win-dow’s Close button (which displays “X”) in a Code window’s title bar to close the window completely. (Closing a window just hides it; you won’t lose any-thing.) To open it again, just double-click the appropriate object in the Project window. Working with these Code windows sounds more difficult than it really is.

Creating a module

In general, a VBA module can hold three types of code:

✓ Declarations: One or more information statements that you provide to VBA. For example, you can declare the data type for variables you plan to use, or set some other module-wide options.

✓ Sub procedures: A set of programming instructions that performs some action.

✓ Function procedures: A set of programming instructions that returns a single value (similar in concept to a worksheet function, such as SUM).

A single VBA module can store any number of Sub procedures, Function procedures, and declarations. Well, there is a limit — about 64,000 charac-ters per module. By way of comparison, this particular article has about half that many characters. After more than 15 years of VBA programming, I haven’t even come close to reaching that limit. And if I did, the solution is simple: Just insert a new module.

How you organize a VBA module is completely up to you. Some people prefer to keep all their VBA code for an application in a single VBA module; others like to split up the code into several different modules. It’s a personal choice, just like arranging furniture.

Getting VBA code into a module

An empty VBA module is like the fake food you see in the windows of some Chinese restaurants; it looks good but it doesn’t really do much for you. Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways:

✓ Enter the code directly.

✓ Use the Excel macro recorder to record your actions and convert them to VBA code.

✓ Copy the code from one module and paste it into another.

Entering code directly

Sometimes, the best route is the most direct one. Entering code directly involves . . . well, entering the code directly. In other words, you type the code via your keyboard. Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and do other things to the text.

Use the Tab key to indent some of the lines to make your code easier to read. This isn’t necessary, but it’s a good habit to acquire. As you study the code I present in this series, you’ll understand why indenting code lines is helpful.

A single line of VBA code can be as long as you like. However, you may want to use the line-continuation character to break up lengthy lines of code. To continue a single line of code (also known as a statement) from one line to the next, end the first line with a space followed by an underscore (_). Then con-tinue the statement on the next line. Here’s an example of a single statement split into three lines:

Selection.Sort Key1:=Range(“A1”), _

Order1:=xlAscending, Header:=xlGuess, _

Orientation:=xlTopToBottom

This statement would perform exactly the same way if it were entered in a single line (with no line-continuation characters). Notice that I indented the second and third lines of this statement. Indenting is optional, but it helps clarify the fact that these lines are not separate statements.

The white-coated engineers who designed the VBE realized that people like us would be making mistakes. Therefore, the VBE has multiple levels of undo and redo. If you deleted a statement that you shouldn’t have, use the Undo button on the toolbar (or press Ctrl+Z) until the statement shows up again. After undoing, you can use the Redo button to perform the changes you’ve undone. This undo/redo business is more complicated to describe than it is to

use. I recommend playing around with this feature until you understand how it works.

Ready to enter some real-live code? Try the following steps:

1. Create a new workbook in Excel.

2. Press Alt+F11 to activate the VBE.

3. Click the new workbook’s name in the Project window.

4. Choose Insert➪Module to insert a VBA module into the project.

5. Type the following code into the module:

Sub GuessName()

Msg = “Is your name “ & Application.UserName & “?” Ans = MsgBox(Msg, vbYesNo)

If Ans = vbNo Then MsgBox “Oh, never mind.” If Ans = vbYes Then MsgBox “I must be

psychic!”

End Sub

6. Make sure the cursor is located anywhere within the text you typed, and press F5 to execute the procedure.

F5 is a shortcut for the Run➪Run Sub/UserForm command. If you entered the code correctly, Excel executes the procedure, and you can respond to the simple dialog box shown in Figure. Unless your name happens to be the same as mine, the dialog box will be different from the one shown in the figure.

 When you enter the code listed in Step 5, you might notice that the VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, the VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, the VBE inserts the space for you. Also, the VBE changes the color and capitalization of some text. This is all per-fectly normal. It’s just the VBE’s way of keeping things neat and readable.

If you followed the previous steps, you just wrote a VBA Sub procedure, also known as a macro. When you press F5, Excel executes the code and follows the instructions. In other words, Excel evaluates each statement and does

what you told it to do. (Don’t let this newfound power go to your head.) You can execute this macro any number of times — although it tends to lose its appeal after a few dozen times.

For the record, this simple macro uses the following concepts, all of which are covered later in this series:

✓ Defining a Sub procedure (the first line)

✓ Assigning values to variables (Msg and Ans)

✓ Concatenating (joining) a string (using the & operator) ✓ Using a built-in VBA function (MsgBox)

✓ Using built-in VBA constants (vbYesNo, vbNo, and vbYes) ✓ Using an If-Then construct (twice)

✓ Ending a Sub procedure (the last line)