<

Excel Error Handling Part 3 -- Run and Fix Bugs

Posted on
12,427 Points
727 Views
2 Endorsements
Last Modified:
Published
Experience Level: Beginner
7:53
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.
https://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

Part 2 went in depth on how the VBA  to copy values to blank cells works, and how to loop.
https://www.experts-exchange.com/videos/1498/Excel-Error-Handling-Part-2-VBA-to-Copy-Values-Down-to-Blank-Cells-in-an-Excel-Column.html

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.
2
1 Comment

Comment

by:Erika Anderson
Thank you!
1
Windows Explorer lets you open archive (tar and tgz) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating archive files takes a little more - and that you'll find here.
If you want to save changes made programmatically to a reports Printer property do not use this syntax: docmd.close acReport, Reportname, acSaveYes
Total Time: 17:46

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month