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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
There are two ways to create macros: recording them or writing them from scratch. Some things cannot be done in a macro ...
Discover MoreNeed to put together a bunch of characters to create a text string? You can do it in your macros by using the String ...
Discover MoreMacros often need to select different cells in a worksheet. Here's how you can use macro commands to change which cell is ...
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