Excel Error Handling Part 3 -- Run and Fix Bugs

Posted on
9,957 Points
1 Endorsement
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.

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Join & Write a Comment

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Total Time: 17:40

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month