Single Stepping and Watches are 2 very powerful tools to use when writing and debugging code.
Lesson 1 – Single Stepping
– Open the VBA editor (Alt-F11, or right-click a sheet tab and choose View Code)
– Place the cursor anywhere within the code.
– Hit F8 to begin Single Stepping through the code.
The next line to be executed will be highlighted, and execution will take place the next time you hit F8.
You can make changes to the code while in Single Step mode. Some changes will alert you that the Debugger will stop if you make that change, other changes will be accepted without interruption to the code.
You can quickly Single Step through the code by holding down the F8 key.
You can drag the step arrow down to an executable line and begin Single Stepping from there. You can also skip lines by dragging the arrow down at any time and re-run lines by dragging the arrow up. Keep in mind that you might get errors if the line you are trying to execute needs a value from a line that you skipped. If you skipped a line that set myVariable = 8 but you don’t want to run all the rest of the code around that line, simply type in a line that says myVariable = 8 above the section that needs that value, place the Step arrow next to that line and begin stepping.
You can exit Single Step mode by clicking on the blue square in the tool bar.
You can click on the Run arrow at any time to let the macro finish on its own.
TIP: If you size and place your VBA editor window so that you can see your spreadsheet behind it you should be able to see your spreadsheet change as the code is executing. Fun and entertaining, yes…but also very valuable for troubleshooting.
Lesson 2 – Run To Cursor
A particularly useful item found under the Debug menu is Run To Cursor.
Let’s say you know the first half of your code works fine, but something in the last half is giving you an error. This is where Run To Cursor comes in very handy.
– Click on an executable line in the code – specifically the line where you want the code to stop running.
– Pull down the Debug menu and choose Run To Cursor, or press Ctrl-F8.
The macro will run at full speed until it reaches the line with the cursor.
It will then highlight that line and you can begin Single Stepping from there. Very useful for getting past long loops that you know are OK.
Lesson 3 – Pop Up Values
As you single step through your code, hold your cursor over a variable. The current value of that variable should pop up. Hold your cursor over things like Range(“A1”).Value. The current value that is in that cell should pop up. There are certain items that will not pop up a value and the method used to obtain those values (Watches) will be covered in Lesson 4. Looking at the Pop Up values is great way to see what your variables are getting set to as the code is running.
Lesson 4 – Adding Watches
– Highlight a variable or any other value-producing entity that you are interested in “watching” as you single step through the code.
– Hit Shift-F9.
– Hit Enter or click OK.
The name of the item will appear in a window in the bottom portion of the VBA editor. Add as many items as you would like. As you Single Step through your code, the current value of those items will appear next to the name.
Note: You can also highlight and drag the item into the Watch window if the window is already open.
In many cases, an item that would have produced an error if executed will show that error in the Watch window as soon as you enter Single Step mode.
There are items that won’t pop up their values with the cursor held over them, but they will show their value in the Watch window.
TIP: If you are single stepping through a long loop, add a Watch on the counting variable (e.g. the myCount in For myCount = 1 to 100). Hold down the F8 key and watch as myCount in the Watch window increments. Release the F8 key as myCount approaches 100 and then use F8 at your leisure to finish the loop and continue stepping through your code.
Lesson 5 – The Debug Menu
Pull down the Debug menu and investigate the other items found there.
For example, Toggle Breakpoint. If you set a breakpoint at any line, the code will run until it reaches the breakpoint and then exit the macro. There are many other items under the Debug menu to help debug your code.
