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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.