Chapter 10
Ten Handy Visual Basic Editor Tips
In This Chapter
Applying block comments
Copying multiple lines of code
Jumping between modules and procedures
Teleporting to your functions
Staying in the right procedure
Stepping through your code
Stepping to a specific code line
Stopping code at a predefined point
Seeing the beginning and end of variable values
Turning off Auto Syntax Check
If you’re going to be spending time working with macros in Visual Basic Editor, why not take advantage of a few of the built-in tools that will make your job easier? Whether you’re a fresh-faced analyst new to programming, or a jaded veteran living on Mountain Dew and sunflower seeds, these tips will greatly improve your macro programming experience.
Applying Block Comments
Placing a single apostrophe in front of any line of code tells Excel to skip that line of code. This technique is called commenting out code. Most programmers use the single apostrophe to create comments or notes in the code, as shown in Figure 10-1.
Figure 10-1: A single apostrophe in front of any line turns that line into a comment.
It’s sometimes beneficial to comment out multiple lines of code. This way, you can test certain lines of code while telling Excel to ignore the commented lines.
Instead of spending time commenting out one line at a time, you can use the Edit toolbar to comment out an entire block of code.
To activate the Edit toolbar, go to the VBE menu and choose View ⇒ Toolbars ⇒ Edit. Select the lines of code you want commented out and then click the Comment Block icon on the Edit toolbar, as shown in Figure 10-2.
Figure 10-2: Use the Edit toolbar to apply comments to a block of code.
You can ensure that the Edit toolbar is always visible by dragging it up to the VBE menu. It will anchor itself to the location you choose.
Copying Multiple Lines of Code
You can copy entire blocks of code by highlighting the lines you need, and then holding down the Ctrl key while dragging the block. This old Windows trick works even when you drag across modules.
You’ll know that you are dragging a copy when your cursor shows a plus symbol next to it, as shown in Figure 10-3.
Figure 10-3: Ctrl-drag to create a copy.
Jumping between Modules and Procedures
After your cache of macro code starts to grow, it can be a pain to quickly move between modules and procedures. You can ease the pain by using a few hot keys.
- Press Ctrl+Tab to quickly move between modules.
- Press Ctrl+Page Up and Ctrl+Page Down to move between procedures within a module.
Teleporting to Your Functions
When reviewing a macro, you may encounter a variable or a function name that is obviously pointing to some other piece of code. Instead of scouring through all modules to find where that function or variable name comes from, you can simply place your cursor on that function or variable name and press Shift+F2.
As Figure 10-4 illustrates, you are instantly teleported to the origin of that function or variable name. Pressing Ctrl+Shift+F2 will take you back to where you started.
Figure 10-4: Press Shift+F2 on a function or variable name to be taken to it.
Staying in the Right Procedure
When your modules contain multiple procedures, scrolling through a particular procedure without inadvertently scrolling into another procedure can be difficult. You will often find yourself scrolling up and then down, trying to get back to the correct piece of code.
To avoid this nonsense, click the Procedure View button at the lower-left corner of VBE, as shown in Figure 10-5. Doing so limits scrolling to only the procedure you're in.
Figure 10-5: Limit scrolling to the active procedure.
Stepping through Your Code
VBA offers several tools to help you debug your code. In programming, the term debugging means finding and correcting possible errors in code.
One of the more useful debugging tools is the capability to step through your code one line at a time. When you step through code, you are watching each line get executed.
To step through your code, you need to put your macro in debug mode. Simply place your cursor anywhere in your macro and then press the F8 key.
The first line of code is highlighted and a small arrow appears on the code window’s left margin, as shown in Figure 10-6. Press F8 again to execute the highlighted line of code and move to the next line. Keep pressing F8 to watch each line get executed until the end of the macro.
Figure 10-6: Press F8 to step through each line of your macro.
As a bonus, while stepping through the code, you can hover over any String or Integer variable to see the current value of that variable.
To get out of debug mode, go up to the VBE menu and choose Debug ⇒ Step Out.
Stepping to a Specific Code Line
In the last example, you saw how you can step through your code by placing the cursor anywhere in the macro and then pressing F8. Your macro goes into debug mode. The first line of code is highlighted and a small arrow appears in the code window's left margin.
This is great, but what if you want to start stepping through your code at a specific line? Well, you can do just that by simply moving the arrow!
When a line of code is highlighted in debug mode, you can click and drag the arrow in the left margin of the code window upward or downward, dropping it at whichever line of code you want to execute next, as shown in Figure 10-7.
Figure 10-7: Drag the arrow while stepping through your code.
Stopping Code at a Predefined Point
Another useful debugging tool is the ability to set a breakpoint in your code. When you set a breakpoint, your code will run as normal and then halt at the line of code where you defined as the breakpoint.
This debugging technique comes in handy when you want to run tests small blocks of code at a time. For example, if you suspect there may be an error in your macro but you know that the majority of the macro runs without any problems, you can set a breakpoint starting at the suspect line of code then run the macro. When the macro reaches your breakpoint, execution halts. At this point, you can then press the F8 key on your keyboard to watch as the macro runs one line at a time.
To set a breakpoint in your code, place your cursor where you want the breakpoint to start, and then press the F9 key on your keyboard. As Figure 10-8 demonstrates, VBA will clearly mark the breakpoint with a dot in the Code window's left margin, and the code line itself will be shaded maroon.
Figure 10-8: A breakpoint is marked by a dot and shaded text.
When your macro hits a breakpoint, it will effectively be placed into debug mode. To get out of debug mode, you can go up to the VBE menu and select Debug ⇒ Step Out.
Seeing Beginning and Ending Variable Values
If you hover over a String or Integer variable in VBA while in debug mode, you can see the value of that variable in a tooltip. This feature allows you to see the values that are being passed in and out of variables, which is useful when debugging code.
However, tooltips can hold only 77 characters (including the variable name), so if the value in your variable is too long, it gets cut off. To see beyond the first 77 characters, simply hold down the Ctrl key while you hover.
Figure 10-9 demonstrates what the tooltip looks like when hovering over a variable in debug mode.
Figure 10-9 The beginning and ending characters in a variable tooltip.
Turning Off Auto Syntax Check
Often times, while working on some code, you'll find that you need to go to another line to copy something. You’re not finished with the line; you just need to leave it for a second. But VBE immediately stops you in your tracks with an error message, similar to the one shown in Figure 10-10, warning you about something you already know.
Figure 10-10: An unfinished line of code results in a jarring error message.
These message boxes force you to stop what you’re doing to acknowledge the error by pressing the OK button. After a half-day of these abrupt message boxes, you’ll be ready to throw your computer against the wall.
Well, you can save your computer and your sanity by turning off Auto Syntax Check. Go up to the VBE menu and choose Tools ⇒ Options. The Options dialog box appears, displaying the Editor tab shown in Figure 10-11. Deselect the Auto Syntax Check option to stop these annoying error messages.
Figure 10-11: Prevent warning messages while coding.
Don’t worry about missing a legitimate mistake. Your code will still turn red if you goof up, providing a visual indication that something is wrong.