Excel Error Handling Part 3 -- Run and Fix Bugs

Posted on
12,421 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!
This is a rather simple but effective way of searching while typing on a Microsoft Access Combobox.
Callback functions are a hidden gem in Microsoft Access. With these, you can dynamically fill a combobox or listbox entirely from code with a versatility way beyond what a simple static value list can offer. To get started, learn the basics.
Total Time: 17:40

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month