Excel VBA : Debugging Errors
Debugging VBA Errors
When creating macros in VBA, even if you just record one and amend the code you are almost certain at some stage to come across an error.
When you run the macro you get a dialog box with an error message, it’s not as helpful as my example above, but at least it points you in the right direction.
Debugging Demo Code
Below is a simple code that goes to sheet 2 of a file, then cell B5 and adds a thin bottom border to the cell.
- Sub jtDebugDemo()
- Sheets(“Sheet2”).Select
- Range(“B5”).Select
- With Selection.Borders(xlEdgeBottom)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- End Sub
We are going to mess with this code to demonstrate how you start working out how to debug error messages.
Demo #1
Since the macro goes to Sheet2 and make changes, we will mess with this and delete Sheet2.
Now run the macro and you get this message
Clear as mud right?!?
This message doesn’t tell you a whole lot, note the message though, if the next step doesn’t help you figure out your problem, then you can search this error message online.
Click the “Debug” button and the line of code with the error will be highlighted. This is your starting point for understanding your error.
If this was a real example, the first thing I would do would be to check that I had a sheet called “Sheet2”, and then problem solved!! I will either change the sheet name in the code or add a sheet called sheet 2.
If the error wasn’t as obvious as this, then I would google the message that was in the dialog box and compare that to the part of the code highlighted.
Now put Sheet2 back and run the code to make sure this problem has been resolved.
Demo #2
This time, comment out the second last line of our code (End With). To comment out the code just add a single apostrophe before “E”.
Run the code again.
This message is a bit clearer, it is telling you that VBA is expecting an “End With”.
Other Error Messages
There are too many possible error messages to cover, but if you do get an error message, note it and if it gives you the option to “Debug” then use it.
After you figure out the problem, make sure to stop the macro using the above buttons.
Note that your macro may have made changes up to the point that it stopped running. If you had applied filters for example and the code had not reset the filters before the error occured, then the filters will still be applied.
Be sure to check what changes have been made to your files, some things may need to be undone or reset before running the macro again.
The Next Post
The next post will give an example of a file that will take one big report and split it out to multiple files saved by person’s name.
This will give you a good example of how VBA can help save you time and boredom from repetitive tasks.
This will also be the last post in this series of VBA posts.
