Office uses Visual Basic for Applications (VBA), an application development language, to extend functionality beyond its standard features. You might write a simple printing macro or use more complex procedures to perform custom tasks or even apply business rules. To add VBA code to a project, you use the Visual Basic Editor (VBE), a built-in interface. It's adequate, but some tasks seem harder than necessary. Here are some tips that can help you fine-tune your coding sessions. You'll spend less time with routine tasks and more time actually coding.
The VBE, shown in Figure A, is essentially the same across all of the Office products, and there's been little to no change for several versions. I'll be using Office 2007 on a Windows 7 system, but most of these tips are supported by all versions. This article assumes you have a little experience working with VBA in the VBE; this isn't an introduction to VBA or its coding environment.
Press [Alt] + [F11] to launch the Visual Basic Editor.
1: Display a little or a lot
It's frustrating to scroll through a procedure and suddenly find yourself in a completely different procedure. It can happen before you know it. Fortunately, the VBE lets you limit access to the current procedure by using Procedure View. Simply click the Procedure View icon in the bottom-left corner of the (code) module. In this view, the VBE displays only the current procedure, so you can't accidentally scroll into another one. When you want to see all the procedures in the module, click Full Module View (selected in Figure A, although there's only one procedure visible). In this view, you can scroll through all the procedures.
One view isn't superior to the other; use them to suit your habits. For instance, you might work in Procedure View while writing code and Full Module View when testing and troubleshooting. It's up to you.
2: Access procedures
Regardless of which module view you're using, you can quickly access other procedures in the module by pressing [Ctrl] + Page Up and [Ctrl] + Page Down. Both shortcuts move up or down through the current module, procedure by procedure.
3: Access modules
If you have multiple modules open, you can move between them by pressing [Ctrl] + [Tab]. The access order defaults to the Project Explorer list and not the order in which you last accessed or saved a module.
4: Access procedures and variables
Quick access is helpful, but you might want to view a specific procedure or variable. For instance, you could encounter a call to another procedure and want to review it before continuing. To do so, click inside the variable reference or procedure call and press [Shift] + [F2]. The VBE will take you to the procedure or variable. After a quick review, you can return by pressing [Ctrl] + [Shift] + [F2].
5: Disable Auto Syntax Check
This feature displays a warning when the syntax for a statement isn't correct. It's helpful but often inconvenient. For instance, suppose you're in the middle of writing a statement when you decide to review another line. Because this feature is enabled by default, the VBE displays the error shown in Figure B. You know the statement isn't correct, so the interruption is unnecessary—but you still have to clear it to continue.
This error is helpful, but it can be annoying.
Fortunately, you can disable this feature as follows:
- Choose Options from the Tools menu.
- Uncheck the Auto Syntax Check option (Figure C) under Code Settings and click OK.
Disable Auto Syntax Check by deselecting this option.
You won't lose a thing by disabling the warning dialog because the VBE will still display the incorrect statement in red. All you'll lose is the interruption. Once you start testing, you can enable the option if you like.
6: Display a variable
While debugging, the VBE displays a variable's current value in a tooltip. It's a helpful feature and adequate most of the time. On the rare occasion when the value is over 70 characters, you'll see only the first 70. You can force the VBE to display characters 71 and beyond by clicking the variable and pressing [Ctrl]. To see the first 70 characters again, click without pressing [Ctrl].
7: Drag the Debug arrow
Debugging tasks usually start with a break point. When VBA encounters that break point, it stops and turns flow control over to you. Most of the time, you'll press [F8] to execute individual statements. To skip a line, press [Shift] + [F8]. When you want to skip several lines in the current procedure or return to a statement that's already been executed, you can move the yellow arrow. Simply drag it (Figure D) up or down and press [F8] to continue executing code from the new location.
Dragging the arrow is one way to temporarily reroute the flow.
8: Display items
As you write code, a feature known as Intellisense (similar to AutoComplete ) often displays help. You don't have to wait for it, though; you can get help by entering the first few characters of a function, property, method, or variable and pressing [Ctrl] + Spacebar to see a list of possible matches, as shown in Figure E. If the item you need is selected, press [Enter] and the VBE will complete your entry accordingly. If the item is in the list but isn't selected, double-click it to add it to complete the item. If there's only one possible match, Intellisense completes the entry without displaying anything. You can press [Ctrl] + Z to undo the completion if it's incorrect.
Display a list of items that match your keyboard input.
Your VBE tips?
You can't use the same VBA procedure across Office products, but the VBE is the same from product to product. These tips will work in all Office products that support VBA. Please share your favorite tips for working more efficiently in the VBE in the comments section below.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.