Hi. In this Excel tutorial, we’re going to go over how you can debug a macro in Excel.
On my screen is the example we used for the tutorial on filtering a chart using radio buttons. Each of the radio option buttons is linked to a
macro that, as we click on the button, it updates the chart to show the data it’s labeled for.
All except for this one. The macro for Week3 seems to be broken.
And, while they usually seem cryptic, Excel is trying to give us information about the problem it ran into. The message here, that "Object
Variable or With Block Variable Not Set” points to an issue with the Object it was expecting to find
in the macro. So, let’s click Debug to step into the macro.
And right away it takes us to the line with the problem in the macro.
So, this macro is pretty short. It starts here with Sub Week3 and ends at End Sub – where Sub means sub procedure. We have the
macro name. In the line with the error, it says that we selected the Plot Area of a chart. We next see the data range chosen,
and, lastly, Range (A1) .Select, which means that we’ll end with the cursor in cell A1. So, back to our error. Remember that the error
message we received mentioned an Object error.
We can compare this to the other macros since they're all pretty much the same. Each one just updates the chart from a different column. And
notice that these say Chart Objects, they reference the chart by its name, and then they Activate the
chart, where ours says PlotArea and selects it.
This is a common mistake that has to do with where we clicked when we selected the chart to update it. It looks like we clicked on the Plot
Area of the chart when we recorded the macro. So, let's close out of the debugger for a second so I can show you.
And, by the way, if you find this tutorial helpful, don’t forget to Like the video and subscribe to the channel for even more tips. So, the Plot
Area is here, where the Line Chart sits. Notice when I double-click on it, it brings up the Format Plot Area box. And, if we look over here
in the name chart, we see that our chart is referenced as Chart 1. So let's close out of this and we'll go back to the Visual Basic Editor.
So, it’s hard for Excel to update the chart just by referencing the Plot Area. It should reference the ChartObject of the selected
chart in the active sheet, like the others. Think of the chart as being made up of a bunch of parts -
your plot area, axis, etc – all are neatly housed together inside a shape. And that shape is the Object and is what Excel is looking for when it
tries to update it. All we need to do is update the command to Active Sheet because the chart and data are in the same active
worksheet, ChartObjects and reference Chart 1, and Activate it. Or we can just
copy from any of the other macros, since we know that those already work, and paste it.
And we'll click save. So, our macro should be fixed now, but rather than just to run the macro, let me show you how you can test your
macro by stepping through each step of the process right from the Visual Basic Editor.
By the way, to do this, it'll be helpful to resize your windows so that you can see your spreadsheet and your Visual Basic Editor like I
did on my screen. So, you'll find this option under Debug, it's called Step Into or you
can just press F8 through each step. Which is what we're going to be doing.
We already have Week3 selected here, so it automatically goes to that macro. So, each time we press F8, it moves us through a step
in the process as it would execute. So, right now we're in step 1, where we should
select the chart object. So, when we click F8 again,
it would execute that command and you can see
here where it's selected
the full chart object and then, now, when we press F8 again,
it shows us where it's now selected the data for Week3. We press F8 again, and it takes us to cell A1 and that ends our macro. So, if
an error had still been present, it would have alerted us as it did when we first tried to run the macro from the option button. And since we
didn't see that, this means that our macro is fixed. So, let me close out of this
and now when I click through each week, our chart is successfully updating without any macro errors. I hope you enjoyed this tutorial
on how to debug a macro in Microsoft Excel. If you did, please make sure to Like the video and don’t forget to subscribe to this channel.
Thanks!