Excel Error Handling Part 3 -- Run and Fix Bugs

Posted on
12,375 Points
2 Endorsements
Last Modified:
Experience Level: Beginner
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel.

Part 1 of this series discussed basic error handling code using VBA.

Part 2 went in depth on how the VBA  to copy values to blank cells works, and how to loop.

Although helpful, it is not necessary to watch parts 1 and 2 before this lesson.

This lesson runs code to see what it does and then breaks working code so we can explore errors.  We run and fix, debug, compile, use and not use Option Explicit, step through code while it is running, look at the watch window to see values of variables, set and clear breakpoints, stop, continue running, and learn how debugging and error handling work.

Video Steps

01. For a list of macros, press Alt-F8

   When you are in an Excel Workbook, press Alt-F8 for a list of Macros.

02. To go to VBA, press Alt-F11

   When you are in an Excel Workbook, press Alt-F11 to go to the Visual Basic Editor (VBE) where you can write Visual Basic for Applications (VBA).

03. To watch variable values, press Ctrl-W

   When you are in VBA code, press Ctrl-W to open the Watch window and set expressions to watch the value of.  If a variable name is highlighted when Ctrl-W is pressed, it will be filled in the Expression.

04. Stop

   Add a Stop statement to the code to cause the code to stop on that line when it runs.

05. To single-step, press F8

   Press the F8 shortcut key to single-step one statement at a time.  You can also use the Step Into icon on the Debug toolbar.

06. To step over, press Shift-F8

   Press the Shift-F8 shortcut key to single-step one statement at a time and step over calls to other procedures as if they were a single-step.

07. To continue running VBA, press F5

   To continue running VBA code, press F5.  Code will not stop unless there is a breakpoint, a stop statement, control passes to the error handler, or the procedure is done.

08. To add or clear a breakpoint at the cursor, press F9

   To add or clear a Breakpoint, where code will stop, press F9.  Breakpoints are reset when you close and open Access.

09. To clear all breakpoints, press Ctrl-Shift-F9

   To force all breakpoints to clear, choose Clear All Breakpoints from the Debug menu, or press Ctrl-Shift-F9.

10. To change which toolbars display, choose View, Toolbars

   To toggle the display of Toolbars on or off, choose View, Toolbars from the menu

11. Option Explicit

   To force variable declaration, add Option Explicit to the top of a module.

12. Debug, Compile

   To compile code, choose Debug, Compile from the menu.  Fix problems and keep compiling till all is good.  Then Save.

13. To break code that is running, press Ctrl-Break

   Press Ctrl-Break to break code that is running.

14. Set Next Statement to Resume

   If an error message happens, break the code.  Right-click on Resume and choose Set Next Statement from the shortcut menu.  Then press F8 to single-step to the line that caused the problem.

15. Comments start with '

   To comment a statement in VBA code, preface it with a single-quote mark ' and it will be ignored by the compiler.
1 Comment

Expert Comment

by:Erika Anderson
Thank you!

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Leaving sensitive information (like passwords) in clear text scripts is never a good practice, though it's sometimes unavoidable.  This set of VBScript functions can be used to obscure critical information making it at least a little more difficult …
This post is just to show case how to provide Guest access for Microsoft Teams, As the Office 365 admin, you must enable the guest feature before you or your organization's users (specifically, team owners) can add guests.
Total Time: 17:40

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month