10 Handy Visual Basic Editor Tips
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? 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 here.
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 here.
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 here.
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 this figure 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 here. Doing so limits scrolling to only the procedure you’re in.
Stepping through your code
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.
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.
The first line of code is highlighted and a small arrow appears on the code window’s left margin, as shown here. 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.
To get out of debug mode, go up to the VBE menu and choose Debug→Step Out.
Stepping to a specific code line
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 here.
Stopping code at a predefined point
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. 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.
Here’s 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 here, 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. 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.