Debugging VBA Macro

Learn Excel from MrExcel Podcast, Episode 2096: Debugging of VBA Macro

Hey, welcome back to MrExcel netcast, I'm Bill Jelen.  Today's question: Someone had some code that I posted on an old YouTube video and they posted a comment saying, “Ah, that’s not working.  It doesn't save the duplicate invoice but it doesn't throw an error.”  I don't know what's wrong with the code.  Alright so, you know, look, there's great tools that are available when you're VBA Macro is not working.  So we have a button here that's supposed to run some code.  I'm going to Assign the Macro, it's called SaveInvoice.  I'll click Edit and we're over in VBA.  And normally when we press that button it’s just going to run this code.  BAM!  Like it all happened really quick but you can't watch what's happening.

So under the Debug tools, one of my favorite things here is Debug Step Into, which you'll see the shortcut key is F8, and that lets us run the code one line at a time.  So I pressed F8 there and this is- the line in yellow is the line that it's about to execute.  So if I press F8, it jumps over those two declarations and now we're about to do an ActiveSheet.Copy.  So what's really beautiful here is, you know, especially if you have a large monitor that the Podcast window is way too small but what you can do is you can watch the Macro run.  So right now it's about to do ActiveSheet.Copy.  Right now I'm in a workbook called Podcast 2096.  Here's a worksheet called Invoice and when I press F8, you'll see that I'm now on a brand new workbook called Book2 and we have just the Invoice, alright. 

And now we're about to assign this big, long thing to New FN.  Press F8.  Alright now, it didn't look like anything happened over here because nothing happened over here.  But here's the beautiful thing, I've now assigned something to that variable called New FN and if I take my mouse and hover over New FN, a little tool tip will appear that shows me what's stored in New FN.  So it's saving the File name, there's the folder where it's going to go.  It's called Invoice1234 because it took the value from F4 and then added PDF. 

Alright now, one of the most frustrating things about Excel is that if your memory starts to get low, this tool tip does not want to appear.  You'll hover there and nothing will happen.  Sometimes you have to click to make it up here and sometimes it just won't appear at all.  When it won't appear at all, what we can do is do Ctrl+G.  Ctrl+G is the immediate window and then we're going to use ? which is the shortcut for Debug.Print, NewFN.  So in other words, tell me what's in New FN and it will show you what's in New FN right there.

Alright so now, we're about to run this line of code which will create a PDF.  Alright, so I’ll press F8.  Alright, and at this point we should have a new PDF out there with 1234 and if I go look in the folder, sure enough Inv1234 at May 11th at 6:25 AM was just created.  Awesome, right?

Alright, now we're getting to the point where we're having the problem.  Alright, so we press F8 here and it shows up as Duplicate Copy.  Alright, that worked.  And then F8, and we can see what's in New FN.  Alright, so it has DupInv1234.pdf and you can even come back down here and just click after Print NewFN and then you'll see that we've changed the file name, alright.  So, everything is cool.  And then we press F8 to run Creating the PDF.  Awesome!  Everything looks great, right?

So come back to our Podcast files.  Alright, but instead of having something called DupInv1234, I just don’t know how something called Book2.  Alright, it was just created a minute ago.  This has to be the one but gee!  It seems to be the wrong name.  Alright, so we come back to VBA and I know that I just assigned the right value to New FN like that, alright.  And let's see what we're Saving It As.   We're Saving It As a NewFN1 which is empty; and since it's empty that means it's taking the file name which in this case is Book2 since I made a copy of it.  And changed it - Oh look!  So here, I was assigning the name to NewFN and then saving this NewFN1, and now because I'm debugging and hovering – Hovering is the greatest thing ever.  I hopefully will be able to figure out what's going on.  So I come back and change the NewFN1.  Now, here's the awesome thing.  Alright, so already passed this line in Macro but I can drag this back up and say, alright, let's run this again F8.  And now let's create the PDF.  Alright, and there it shows up with the right name and everything is cool. 

Alright, now that I know that I'm done, everything is great.  Everything is going to work from here.  I just click Run and it’ll run to the end of the code.  Alright, sometimes you'll have a long Macro with, you know, hundreds of lines of code that are working and then one particular part that's not working, alright?  So a couple of other tools were just mentioned here.  If you need to jump over a whole bunch of code and run everything up to that point, well, one way to do that is to click here and create a Breakpoint.  Alright, so now when I run this, it will run everything down to that point.  I can just click run and it will stop, or if you don't even want to set a Breakpoint, we’ll just click right here and say Debug, Run To Cursor.  Run To Cursor.  Now I know that this will cause a problem here because the code to change this invoice number, I did not hook up yet.  So I'll just put a new invoice number in and so I'm on that line right now.  Debug, Run To Cursor which is Ctrl+F8.  Alright, so now it ran everything down to that point and what we should have is we should be able to see that it just created Inv1235, alright.  And now it's about- we’re on this line of code.  I can just either press F8 to run one line or just run the rest of the way.  And there's our DupInv1235, alright?  So, the Debug Tools here in VBA are awesome.  Let's you run a code one line at a time, arrange your screen so that we can see both the code that's running and the results of the code over on the left.  And you know, hopefully, you can figure out what's going wrong with the code.

Alright, so episode recap: Have an Excel VBA Macro that is not working.  It has amazing debugging tools.  Rather than running your code, you can step through the code using F8.  The line in yellow is the line that is about to be run.  You can hover over variable to see the value of that variable, toggle back and forth to Excel to see what's happening.

Well, hey, I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.