Dear All,

Debugging VBA Code

In Tutorial section for Beginners, I have written many basic articles like WHAT IS EXCEL MACRO, HOW TO WRITE IT, WHERE TO WRITE IT etc. As you know Errors are common while writing your code in VBA or in any other programming language for that matter. This is not a problem. As long as you know how to fix it. But fixing is again not the real problem. In order to fix the problem, first, you need to identify, where exactly the problem is. Identifying the error in your code is termed as “Debugging”. Debugging is not about fixing the problem, but identifying the problem. Identifying the root cause of the problem.

This article should have been written for you long back.. right after your first excel Macro. However.. better late than never 🙂

Before I jump to debugging topic, I would like to tell you about the most common errors in excel vba (at a high level). I would say, mainly there are two types of errors (though there are many other categories and subcategories of errors):

1. Compile Error

VBA Compile Error

In simple language these are errors which occurs due to wrong syntax. In such case, Excel itself highlights the line where there is a Syntax problem. In such case, you don’t need to worry much to find out the root cause of the error.

For example: While writing the If-Else-End If condition, I forgot to type the Keyword “THEN”. That particular statement is highlighted with an error message box displayed.

2. Functional/logical Error

These are the errors, for which VBA/Excel Macro will not give you any error message. They are logical errors due to which you are not able to get the expected outcome which your code is intended to do.

Example:You have written a code to calculate factorial of an integer provided by the user. While running the program user provided input as 5 and program ran successfully. After successful run, Factorial of 5 is received as 24. Though program was able to run successfully but the outcome is not same as expected which is 5! = 120.

Now how will you debug your program to find out this logical error in it.

In order to debug your program you need a mode where you can execute every line of code as and when you want and verify the outcome of each statement. This mode of running the program in called Debug Mode. Therefore now you know, what does it mean, when someone asks you to run your program in debug mode. Now we will see how to run your VBA code in debug mode.

How to run VBA code in Debug mode

To run your program in debug mode is very simple.

1. Place your cursor anywhere within your function which you want to run it in debug mode
2. Press function Key F8 or go to Debug –> StepIn as shown in below picture.
3. As soon as you do so, your function/procedure name would get highlighted in Yellow color as shown in below image

4. Now you keep pressing F8 and program will start executing your code one by one. On every F8 press, program control moves to the next line and that line gets highlighted. It will remain there until you again press F8.

Debugging Mode

5. See the values of variables, statements etc runtime – if you roll your cursor over them, the latest value of that variable will get highlighted as shown in below picture:

Variable Value

6. How to add watch for a particular variable, object, statement etc. :You can also add a watch for such variables/objects/statements for which you want to observe the value.

Variable value Watch

This way you will be able to observe the output of fact at every iteration while loop is running. Then at 4th iteration loop will come out and display the result of fact. While as per your expectation loop should have run once more and then you know where is logical error and how to fix it.

You can not start running your function in debug mode if it requires any input parameters to run. It means you need to start running your code in debug mode from where this function is being called. If a function does not require any input parameter then you can run it in debug mode by following the above steps.

What is Break Point and how is this relevant here?

Above example was a very simple and small loop. Let’s assume you have a function having multiple loops, nested loops, hundreds of lines of code. Out of this complete function few loops are logically fine. Only one statement or set of statements might have some logical error. In such case as you know that F8 just takes you to next statement, you will have to press thousands times F8 just to reach to the place where actually the logical error exists. and By mistake if you press F5 then it will run the whole program.

This is where BreakPoint comes in to picture. Break Point is a mechanism which if you put at any statement of your program then your program will automatically stop at that statement. It means if you put the breakpoint on 450th line of your code and press F5 (which is used to run the program) then all 449th statements will be executed and control will stop at 450th statement (highlighted as yellow). Now from here again either you can proceed by pressing F5 or F8 whichever you want.

How to add Break Point in VBA Code:

You can add break point at any statement of your code (which performs some actions… not on defining variables etc.) by doing following:

1. Place your cursor anywhere on the line where you want to place the break point and Press F9
2. That particular line will get highlighted as below:

Break Point

You can add as many break points as you want within a program.

Few Important Shortcut Keys

You may also like to read these articles

VBA GUIDE TO INTERACT WITH TEXT FILES – PART – 1 OF 2
WHAT IS IMMEDIATE WINDOW AND HOW TO USE IT IN EXCEL VBA
IMAGE IN SIGNATURE NOT DISPLAYED – MAIL SENT BY VBA
HOW TO SAVE AND CLOSE AN EXCEL SHEET USING MACRO
COMPLETE VBA TUTORIAL TO INTERACT WITH POWER POINT PRESENTATIONS – PART – 2 OF 2