Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

Module 4 - Variables, Parameters and Functions

Lesson 4.1 - Basic Data Variables

Topic 4.1.2 - Debugging With Variables

When you're running a procedure which contains variables there are some useful debugging tools you can use to see what's happening.

There are several ways to see the value stored in a variable when a program is running.

Files Needed

You can click here to download the file for this page.

Completed Code

We don't make any changes to the original file in this part of the lesson.

The Sample Workbook

Download and open the file linked to in the Files Needed section above. Open the VBE to find a copy of the subroutine you worked on in the previous part of this lesson:

The subroutine contains four variables whose values we'd like to observe when we run the procedure.

Entering Break Mode

In order to see the contents of a variable, you need to enter Break Mode. You can do this by clicking within a subroutine and pressing F8 on the keyboard:

Press F8 to enter Break Mode. You'll see the first line of the procedure highlighted in yellow and the word [break] in the title bar of the window.

You'll automatically exit Break Mode when the procedure ends. You can prematurely exit Break Mode by clicking the Reset tool on the toolbar:

Click this tool to exit Break Mode.

Observing Variables Using the Mouse

A simple way to check the value stored in a variable is to position the mouse cursor over the name of the variable. Begin stepping through the subroutine by pressing F8 and then position the mouse cursor over the name of a variable:

You can see the value of a variable displayed in a small tooltip when you position the mouse cursor over its name.

Continue pressing the F8 key to step through the procedure. After executing an instruction which assigns a value to a variable you can position the mouse cursor over its name to see that it has changed:

Position the mouse cursor over a variable name after its value has been altered to see the new value.

Using the Locals Window

When your procedure contains multiple variables, it can be time-consuming to position the mouse cursor over each variable name. You can view the contents of every variable in a procedure using the Locals Window. To display this window, choose View | Locals Window from the menu:

The Locals Window appears at the bottom of the VBE screen by default. The window will be empty unless you have entered Break Mode.

Press F8 to begin stepping through a procedure and see the Locals Window populated with a list of variables:

The Locals Window will be populated with a list of variables that you have declared in the procedure that you are stepping through. It also shows the current value and the data type of each variable.

As you continue pressing F8 to step through a procedure you'll see the values in the Locals Window changing automatically:

The values in the window will change automatically when you execute instructions which alter the values of variables.

Using the Watch Window

If your procedure contains many variables you may want to focus on a small number of them. You can watch the contents of specific variables using the Watch Window. To display this window, choose View | Watch Window from the menu:

The Watch Window appears at the bottom of the VBE screen and will be empty to begin with.

To see something useful in the Watch Window you need to add something to watch! One way to do this is to right-click the name of a variable in your code:

Right-click a variable name in your code and choose Add Watch...

You can configure the item you're watching in the dialog box that appears:

The Expression box should contain the name of the variable you right-clicked on. The Watch Type should be set to Watch Expression. Click OK to add the item to the list of watches.

After clicking OK the item will be added to the Watch Window:

The item will appear in the Watch Window. It won't show much useful infomation until you begin stepping through a procedure.

You can also add watches directly in the Watch Window:

Right-click in the Watch Window and choose Add Watch...

You can fill in the dialog box as previously:

You can type a variable name into the Expression box at the top of the dialog box.

Click the OK button to add the item to the list of watches. You can see the values of the watched items change as you use the F8 key to step through the procedure:

Step through the procedure to see the contents of the watched variables change.

You can remove items from the watch list by right-clicking on them and choosing to delete them:

Right-click an item in the Watch Window and choose Delete Watch to remove it from the list.

We'll use the techniques described in this part of the lesson in many of the later parts of the course. Feel free to experiment with the Locals and Watch windows before moving on to the next part of the lesson.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS