ExcelTips (Menu Interface)
Members
This Site
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our site focusing on the ribbon interface.
Helpful Links
ExcelTips FAQ
Ask an Excel Question
Make a Comment
Free Business Forms
Free Calendars
Subscribe
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2597) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
Related Tips:
Make Home Buying Less Stressful! Why make home buying harder than it needs to be? Put your mind at ease—discover all the questions you need to ask to make the best buying decision. Check out Buying a Home Checklist today!