Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Friendly and Informative Error Handling.
Written by Allen Wyatt (last updated February 6, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
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 ... [your code] ... sOp = "opening target file" ... [your code] ... sOp = "counting lines already filled" ... [your code] ... sOp = "copying source data table" ... [your code] ... sOp = "saving and closing" ... [your code]
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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9577) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Friendly and Informative Error Handling.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2019 For Dummies today!
Place a cell reference in a macro, modify the structure of your worksheet, and you may soon find that the cell reference ...
Discover MoreNeed a quick way to change the default drive and directory in a macro you are writing? Here's the commands to do it and a ...
Discover MoreEven if you do nothing but record macros, sooner or later you will have a need to edit what you record. Here's how to get ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-02-06 06:22:38
jamies
I frequently setup Global variables ( well within the VBA "Project")
And set things of relevance in them -
Starts with the name of the module that is currently doing the (sub) process.
Maybe some key data or at least as the row/column being processed,
Folders - or fullnames of files
The one the macro script is being run from,
the default one.
the one the current workbook was opened from.
the current one
The location of the Excel recovery files.
userid
For "batch processing activities:
Error reporting detail level -
as in a file to be used to log progress - and the detail of logging -
( note - if the specified file does not exist on the "user" system - then no logging
If it does exist - then first, and only entry should be an identifier indicating it is the log for this script's output.
that stops overwriting "user" data, and is an easy to manage logging switch - no file = no log from that user's environment.
First entries to be added to that being a timestamp and the contents of the Global variables.)
- Basically things it is helpful to have accessible when you enter Debug to try to figure out what the "user" session encountered !
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments