Topics: Friendly and Informative Error Handling

Friendly and Informative Error Handling

Summary: When writing macros, you need to know how to convey error messages to the user should an error actually crop up. This tip examines one technique that makes the task fast and easy. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Other people use my workbooks and I have a simple technique that I use when writing error handlers that makes it easier to cope with problems if macros fail. Every procedure that is at all risky contains a local string variable, sOp, whose value is set during the macro code as follows:

Dim sOp As String
...

...
sOp = "opening target file"
...

...
sOp = "counting lines already filled"
...

...
sOp = "copying source data table"
...

...
sOp = "saving and closing"
...


These statements are nothing more than one might put in as remarks, but they have the advantage that when an error occurs, the user can be informed what was going on at the time. At its simplest the error handler just needs to contain a single statement like this:
MsgBox "Procedure MyMacro failed while " + vbCrLf + sOp

The value of sOp can also be used to determine the next action (resume, exit, etc). Using this technique in your own macros can make them easier to debug and more friendly for users.


				


				
					Tip #2597 applies to Microsoft  Excel versions: 97 | 2000 | 2002 | 2003 | 2007
				
				
					


				
				

				

    Save Time! ExcelTips has been published weekly since late 1998. Past issues of ExcelTips are available in convenient ExcelTips archives. Have your own enhanced archive of ExcelTips at your fingertips, available to use at any time!
 
Check out ExcelTips Archives today!

Friendly and Informative Error Handling

Macros Rock!

Helpful Links

Great Info!