MOCKSTACKS
EN
Questions And Answers

More Tutorials









VBA Debugging


Debugging is a very powerful way to have a closer look and fix incorrectly working (or non working) code.

Run code step by step


First thing you need to do during debugging is to stop the code at specific locations and then run it line by line to see whether that happens what's expected.

.Breakpoint ( F9 , Debug - Toggle breakpoint): You can add a breakpoint to any executed line (e.g. not to declarations), when execution reaches that point it stops, and gives control to user.

.You can also add the Stop keyword to a blank line to have the code stop at that location on runtime. This is useful if, for example, before declaration lines to which you can't add a breakpoint with F9

.Step into ( F8 , Debug - Step into): executes only one line of code, if that's a call of a user defined sub / function, then that's executed line by line.

.Step over ( Shift + F8 , Debug - Step over): executes one line of code, doesn't enter user defined subs / functions.

.Step out ( Ctrl + Shift + F8 , Debug - Step out): Exit current sub / function (run code until its end).

.Run to cursor ( Ctrl + F8 , Debug - Run to cursor): run code until reaching the line with the cursor.

.You can use Debug.Print to print lines to the Immediate Window at runtime. You may also use Debug.? as a shortcut for Debug.Print


Watches window


Running code line by line is only the first step, we need to know more details and one tool for that is the watch
window (View - Watch window), here you can see values of defined expressions. To add a variable to the watch
window, either:

.Right-click on it then select "Add watch".

.Right-click in watch window, select "Add watch".

.Go to Debug - Add watch.

When you add a new expression you can choose whether you just want to see it's value, or also break code execution when it's true or when its value changes.

Immediate Window


The immediate window allows you to execute arbitrary code or print items by preceeding them with either the
Print keyword or a single question mark "?"

Some examples:

.? ActiveSheet.Name - returns name of the active sheet

.Print ActiveSheet.Name - returns the name of the active sheet

.? foo - returns the value of foo*

.x = 10 sets x to 10*
* Getting/Setting values for variables via the Immediate Window can only be done during runtime

Debugging best practices


Whenever your code doesn't work as expected first thing you should do is to read it again carefully, looking for mistakes.

If that doesn't help, then start debugging it; for short procedures it can be efficient to just execute it line by line, for longer ones you probably need to set breakpoints or breaks on watched expressions, the goal here is to find the
line not working as expected.

Once you have the line which gives the incorrect result, but the reason is not yet clear, try to simplify expressions, or replace variables with constants, that can help understanding whether variables' value are wrong.

If you still can't solve it, and ask for help:

.Include as small part of your code as possible for understanding of your problem

.If the problem is not related to the value of variables, then replace them by constants. (so, instead of Sheets(a*b*c+d^2).Range(addressOfRange) write Sheets(4).Range("A2"))

.Describe which line gives the wrong behaviour, and what it is (error, wrong result...)


Conclusion

In this page (written and validated by ) you learned about VBA Debugging . What's Next? If you are interested in completing VBA tutorial, your next topic will be learning about: VBA First Module and Hello World.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.