Excel VBA Basics : All about Debugging Excel VBA code

.

Dear All,

Debugging VBA Code

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

Excel Macro 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.

VBA - Debugging Mode

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

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

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

Break Point

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

Few Important Shortcut Keys

Description Shortcut Keys
Run code in Debug Mode (Step In) F8
Run to cursor Ctrl + F8
Toggle break point F9
To Clear all the break point Ctrl + F9

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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Convert PDF Tables to Excel with VBA | Step-by-Step Guide
Convert PDF Tables to Excel with VBA | Step-by-Step Guide

Convert PDF Tables to Excel with VBA | Step-by-Step Guide How to Convert PDF Tables to Excel Using Excel VBA (Macros) Extracting data from PDF tables into Excel can be tedious, but with Excel VBA (macros), you can automate this process and save time. This guide will...

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

1 Comment

  1. Subodh Tiwari

    Good work Vishwa!
    Keep it up.

    Reply

Trackbacks/Pingbacks

  1. Welcome to LearnExcelMacro.com Excel VBA Tips: How to comment a Block of Code in VBA - […] you write description in English language in between your VBA code? VBA compiler will start giving compile error. It…
  2. Complete VBA Guide to Interact with Text Files with Examples - […] Excel VBA Basics : All about Debugging Excel VBA code […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest