Excel Debugging Discoveries: Debugging VBA Macro - Episode 2096

Microsoft Excel Tutorial: Debugging tools in Excel VBA.

Welcome to MrExcel Podcast, where we help you learn Excel through practical examples and tips. In this episode, we will be discussing how to debug a VBA Macro. Recently, a viewer commented on one of our old YouTube videos saying that the code I had posted was not working. It was not saving the duplicate invoice and was not throwing an error. I was not sure what was wrong with the code, so I decided to use some of the great debugging tools available in VBA.

In the video, I demonstrate how to use the Debug Step Into tool, which allows you to run the code one line at a time. This is a great way to watch what is happening in your code and identify any errors. By pressing F8, you can jump over declarations and see which line of code is about to be executed. You can also hover over variables to see their values and use the immediate window to print out the value of a variable.

One of the most frustrating things about Excel is when the memory starts to get low, the tool tip does not appear when you hover over a variable. In this case, you can use the immediate window to print out the value of the variable. Another useful tool is the Debug Run To Cursor, which allows you to jump over a section of code and run everything up to that point. This is helpful when you have a long macro with hundreds of lines of code and only one part is not working.

The debugging tools in VBA are incredibly useful and can help you identify and fix any issues in your code. By stepping through the code and watching the results in Excel, you can easily pinpoint where the problem is occurring. So, the next time you encounter a VBA Macro that is not working, remember to use these debugging tools to help you troubleshoot and fix the issue. Thank you for watching this episode of MrExcel Podcast, and we'll see you next time for more Excel tips and tricks.

#excel
#microsoft
#microsoftexcel
#exceltutorial
#evergreen
#excelvba

This video answers these common search terms:
how can i debug mocro in excel
how to debug a macro excel
how to debug a macro in excel
how to debug an excel macro
how to debug excel
how to debug excel macro
how to debug excel spreadsheet
how to debug excell formulas
how to debug in excel macro
how to debug in excel vba
how to debug macros in excel
how to debug vba code in excel
how to open debugger in excel
how to use debug excel
how to use excel macro debugger

Table of Contents:
(00:00) Debugging of VBA Macro in Excel
(00:15) Debugging tools available for VBA Macro
(00:25) Using Debug Step Into to run code one line at a time
(01:01) Watching Macro run on a large monitor
(01:20) Using Ctrl+G to access immediate window
(02:00) Checking variable values with Debug.Print
(03:02) Identifying problem with code
(03:27) Using Breakpoints and Run To Cursor for debugging
(04:22) Hover to see values stored in variables
(04:50) Run to end of code
(05:12) Breakpoints in VBA
(06:09) Recap of debugging tools for VBA Macro
(06:26) Clicking Like really helps the algorithm

You have an Excel VBA Macro that is not working
VBA has amazing debugging tools
Rather than run your code, you can step through the code using F8
The line in yellow is the line that it about to be run
Hover over any variable to see the value of that variable.
Toggle back and forth to Excel to see what is happening

[excelisfun]

Thanks for the F8, Ctrl + G, Debugging fun : )

[krn]

Thanks Mr. Excel. Another great debug tool is Compile VBA Project. Run this before you attempt to walk through code. It checks your entire project for variables not being assigned or objects not set correctly and other stuff. Almost like a spell check lol.

[joem]

Here are a few shortcuts I have picked up over the years that might be useful:
Bill showed us Control +G to get from the Code window to the Immediate window, to return to the code window press F7.

Sometimes a variable is so long you can not see the entire value in the "hover" field, move away from the variable, hold down the Control key and then hover over the variable again and you will see the "back end".

One other shortcut I like is Control + Shift F9 to clear all break points, always a good idea after exiting code you have set many break points during a debugging session.

[TonyAllenAT]

OMG, the truly "awesome thing" at 4:31 just made my day.
Simply drag the yellow arrow back up in order to rerun a line of code, instead of starting over? Why did I never know this?
At first I was like, "What? You can't do that!" LOL
If only I would've known That before, could've saved me a ton of time over the years. smh
Anyway, thanks again for a great podcast.

[automationguide]

Thanks for the shortcuts using ? instead of writing debug.print is time saving.

[Reduce_Scan]

Hi Bill
please I need code take this action
I have a range of suppose from A1 to A100
I want to place the cursor inside each cell
Just as if I was working manually, double all in cell A1 then enter then double all in cell A2 and so on
  on condition
  Do not affect the contents of the cell

[djh]

Hello MrExcel & ExcellsFun i have a big problem with the indirect function :
1/ i used this great video to solve my problem ==>Mr Excel & excelisfun Trick 40: VLOOKUP to 3 Different Lookup Tables (3 table_arrays) and i chose the 3rd solution (MrExcell's)
2/ the problem is i want to do Hlookup+ Indirect (TABLE1) and Vlookup + indirect (TABLE1) the same table but not the same lookup value, ,, is this possible ? or is there any solution ??
3/ Finally i divided the table to two small tables 1 and 2 but i had to create another cell with the second name of the table

[Sal_A]

Great tips for learning. I did not know that the ? character was a shortcut for debug.print. What is the Quick Watch (Shift + F9)?

[moibrahim]

Hi Bill, I have a problem with calculating time in pivot table and I hope you can help, I have a large spreadsheet with 8 hours working time in different store, I like to see if there is a formula can calculate how much each person spent time in each store, and I love to know if by anyway can be done automatically. Please let me know how to send data sheet to you.Thank you

[rekhapons]

Hi Bill, please. I have a workbook with a multiple worksheets which have external links and formulas. I need to Split the work sheets and sent to various recipients with other documents eg, one work book has c30 worksheets named as 1, 2, 3.4.5.6....30. And I need to send sheets 1&2 as one workbook to one recipient via mail and 2, 3&:7 sheets as one workbook to another mail recipient and so on. And also I need to attach another document with these workbook as well before I mail to the relevant recipients every month. Could you please kindly post a video to automate this process via VBA solution on you channel please . It’s very urgent. Thank you so much

[mattmatt]

Bill, would you help me write
a macro that shows all possible combinations (from a given set) of
numbers whose sum is less or equal a given number. So, given a set of
numbers 2, 3, 4, 7, 8 and sum <=15, macro would spit out such
combinations:
234782, 4; 4, 3; 3, 7; 2, 3; 4, 7; 7, 8; 2, 7; 4, 8; 2, 8
2, 4, 3; 2, 4, 7; 2, 4, 8; 4, 3, 7
Thank in advance
Matt