Debugging Techniques in VBA

Posted: May 23, 2013 by MaxFraudy in VBA

Different people use different debugging tool/techniques to fix errors in their codes. All of these techniques are extremely handy and can be used in different situations. Today I’m going to discuss basic debugging features.

1. Stepping Through the Code
  1. Perhaps the most basic method of checking where the error is arising is to run your code line by line, see which line generates the error and fix it accordingly. You can use F8 key and execute each line, one after the other until you reach the point where error is generated.

    VBA Debugging – Using F8

  2. Putting Breaks – This can be used when you are sure that your code is running fine upto a certain point and you don’t want to waste time going through each and every line of the code. You can just put a break at the point from where you want to watch your code. This can be done by clicking on the margin of the Code Window (clicking it again will remove the break). The code stops at wherever it encounters a break. There can be multiple breaks in your code.

    Putting Breaks in VBA Code Window

  3. Executing parts – Suppose you have a main procedure and you are calling other procedures from within it. When you want some of the intermediate procedures to run and return to the main procedure, you can simply jump over it using the shortcut [CTRL]+[SHIFT]+F8
2. Immediate Window

The Immediate Window can be used in two ways:

  1. Using ‘?’ before the expression prints the value of the expression
  2. Without using a ‘?’ you can execute statements while your code is paused. e.g. to jump ahead in a loop, increase the counter
3. Watch Window

This window can be used to see variable/object value/property. Just select the item that you want to watch, rightclick and choose ‘Add Watch‘. There are three options that you can choose from:

Watch Window Options

  1. Watch Expression – the window will show you the item evaluation at all times, without affecting the running of your code
  2. Break When Value is True – The code will pause when the value of the watched item becomes true.
  3. Break When Value Changes – The code will pause when the value of the watched item changes. This is handy in cases where you want to check if the error is arising because of some value that the item is taking during execution.
4. Locals Window

This is one of the less commonly used VBA Debugging features . This window shows the values of all constants and variables that are in scope at any given point during code execution. You can pause your code at any time and this window will show you which all variables are active at that time and what values they hold.

VBA Debugging – Locals Window

5. Call Stack

Call Stack is especially useful if you are debugging someone else’s program or a very old program of your own, the kind in which one sub calls the other, and then another from within that and so on. In such cases it is virtually impossible to track where we are in the ‘nested calls’. Call Stack maintains a stack of all function calls, the recent one being at the top.

VBA Debugging – Call Stack Window

More to come…..
  1. Sujoy says:

    Good. Where is “On error goto 0: On error Goto -1”

  2. Transformer says:

    Coming Soon ………… 🙂

Share your thoughts/feedback

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s