MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Debugging VBA Macro


May 11, 2017 - by Bill Jelen

Debugging VBA Macro

How to debug an Excel VBA macro that is not working. There are amazing tools in Excel VBA that let you see the current value stored in variables as you step through code one line at a time. If you have a macro that is not working,

Watch Video

  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2096 debugging of VBA macro hey welcome
  • back to MrExcel NetCast I'm Bill
  • Gellin today's question someone had some
  • code that I posted on an old YouTube
  • video and they they posted a comment
  • saying ah I was 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
  • save invoice I'll click Edit and we're
  • over in VBA and normally when we press
  • that button is 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 press f8 there and
  • this is the line in yellow is the line
  • that it's about about to execute so if I
  • press f8 it jumps over those two
  • declarations and now we're about to do
  • an active sheet copy and so what's
  • really beautiful here is especially if
  • you have a large monitor that the
  • podcast windows way too small but what
  • you can do is you can watch the macro
  • run so right now it's about to do active
  • sheet 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 in a brand new
  • workbook called book 2 and we have just
  • the invoice alright and now we're about
  • to assign this big long thing to new FN
  • press f8 right 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 a
  • something to that variable called new FN
  • and if I take my mouse and hover over
  • new FN a little tooltip will appear that
  • shows me what's stored in new of em so
  • it's saving the file name
  • there's the folder where it's going to
  • go it's called invoice 1 2 3 4 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
  • tooltip 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 question mark which is the
  • shortcut for debug print new FN so in
  • other words tell me what's in new FN and
  • it will show you what's a new oven right
  • there alright so now we're about to run
  • this line of code which will create a
  • PDF alright so I press f8 alright and at
  • this point we should have a new PDF out
  • there with one two three four and if I
  • go look in the folder
  • sure enough invoice one two three four
  • at May 11th at 625 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 copyright that
  • worked and then f8 and we can see what's
  • in new FN alright so it has dupe invoice
  • one two three four PDF and you can even
  • come back down here and just click after
  • print new FN and 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 dupe
  • in voice 1 2 3 4 I just that now have
  • something called book - 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 new FN one which is empty
  • and since it's empty
  • that means it's taking the the filename
  • which in this case is book 2 since i
  • made a copy of it and changed it look so
  • here i was assigning the the name to new
  • FN and then saving his new FN one and
  • now because i'm debugging and hovering
  • the greatest thing ever I hopefully will
  • be able to figure out what's going on so
  • I come back and change this new FN one
  • now here's the awesome thing all right
  • Sam already passed this line in macro
  • but I can drag this back up and say all
  • right let's run this again f8 and now
  • let's create the PDF all right and there
  • it shows up with the right name and
  • everything is cool all right now that I
  • know that I'm done everything is great
  • everything is going to work from here I
  • can just click one and a run to the end
  • of the code all right sometimes
  • 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 all right so a couple
  • of other tools we'll just mention 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
  • all right so now when I run this it will
  • run everything down to that point I can
  • just click run and it will stop or or if
  • you don't 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 the cursor
  • which is control f8 all right so now it
  • ran everything down to that point and
  • what we should have is we should be able
  • to see our we should be able see that is
  • just great invoice one two three five
  • all right and now it's about four on
  • this line of code I can just eat a press
  • f8 to run one line or just run the rest
  • of the way and there's our doop invoice
  • one two three five all right so the
  • debug tools here in VBA are awesome
  • let's run it 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
  • all right 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
  • 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 why hey I wanna thank you for
  • stopping by we'll see you next time for
  • another net cast from MrExcel

Download File

Download the sample file here: Podcast2096.xlsm

Title Photo: sign/post/b1ae9fa6-f05e-4252-a2d3 / Pixabay


Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.