Throwing Errors in VBA
Introducing a frictionless alternative to Err.Raise.
Not all errors are bad. Many young programmers look at errors as something to be avoided at all costs. That's a shame because there are many times when raising an error is the best way to convey intent. This technique works especially well with the global error-handling capabilities of vbWatchdog.
Something Borrowed
One of the best ways to improve as a programmer is to write code in different languages. The reason is that it gives you a chance to see a variety of ways to deal with common problems. Oftentimes, you can import the concepts from those other languages into VBA. I've done this in the past with Python's doc tests feature.
This time I'm taking a page from C#. In C#, one can raise errors (exceptions, in C#) via the throw statement. In fact, Visual Studio will automatically generate such statements when it creates new methods. For example:
public void NewMethod()
{
throw new NotImplementedException();
}
Problems with Err.Raise
Of course, VBA has a way to do this using the Err.Raise method. It's fine to use once in awhile, but I found myself avoiding using it because of the extra friction it introduced into my programming flow.
You see, Err.Raise requires passing an error number. As far as I can tell, this is mainly for providing a unique way to refer to errors. The error number must be a number within the range 513–65,535. Thus, according to the pigeonhole principle, we cannot be guaranteed a truly unique error number. Given a large enough project, it would be possible (even if highly unlikely) to exhaust the entire pool of error numbers.
That said, if you are using any sort of bug tracking database (you should), then you probably want to be able to keep track of the number of instances of a particular error. This is easier to do if you are assigning relatively unique error numbers. That's easier said than done, though.
We could keep an Enum type with a different enum member for every kind of error. That introduces friction. I tried this early in my programming journey, but I didn't consistently use it because it would break me out of my flow. Then there's the question of how best to initialize the numbering of the Enum types. Does each module get its own Enum type? What about modules from our code library? How do we keep the error numbers unique among multiple projects?
Keeping track of unique error numbers in some sort of systematic way never worked for me. I could have made it work, but it was so annoying that I didn't bother. I started just randomly typing numbers whenever I would call the method:
Err.Raise vbObjectError + 31257, , "Not implemented yet."
Err.Raise vbObjectError + 4412, , "Object not initialized."
Bug Database Integration Issues
Early on, I adopted FogBugz as a bug-tracking database. One of the features I like best is their BugzScout tool. It's a REST API for submitting bug reports. The best part of this tool is that it will track the number of occurrences of a single bug based on the bug title. I used the Err.Description as the bug title. If it was a unique string, FogBugz would assign it a new case number. Otherwise, it would append it to an existing case and increment the occurrence count.
This worked great for most errors, but it had a fatal flaw. If the error message contained some sort of variable string, then the same error would generate many unique cases. For example, a common problem would be a missing file. The error message would be something like, "Cannot find file: 'C:\Temp\Readme.txt'". The same exact error would generate a second case number with a message like, "Cannot find file: 'C:\Temp\Readme (1).txt'". It was the same error, but FogBugz treated it as unique because of the differing string values.
To work around this issue, I adopted the naming convention of many Access errors:
?AccessError(2102)
The form name '|' is misspelled...
Builtin Access errors that support variables always surround those variables with single quotes. Thus, to avoid the problem of generating too many unique error cases, I started replacing the contents of instance-specific text with pipe characters before submitting the error message to FogBugz. (NOTE: The RegExReplace function below is a wrapper around the VBScript's regex functionality.)
?RegExReplace("($|[^a-zA-Z])'[^']+'", "Can't load file: 'C:\Users\Mike\Temp.txt'. File not found.", "$1'|'")
Can't load file: '|'. File not found.
This little trick helped avoid bug case proliferation. If you are using a local RDBMS as a bug tracking database, you may have run into a similar problem. I liked this solution, so I wanted a way to incorporate it into my Err.Raise replacement function.
Throw in VBA
I tried many times to make Err.Raise work for me. It never did. I still liked the concept of throwing errors, but I needed a better way to achieve that goal. I needed a solution that met the following requirements:
- Easy to use ("frictionless")
- Automatically creates relatively unique error numbers
- Allows for instance-specific error information
- Generates a generic message stripped of instance-specific strings
Enter the Throw() function. Here's the overview of how it works:
The procedure automatically generates an error number within the required range by calculating a simple hash of the generic error message. Instead of passing instance-specific strings in the error message, you would use placeholders like {0}, {1}, etc. The corresponding values would be passed as a ParamArray to the function.
To increase readability of the calling code, the error message may contain \t and \n escape sequences, which the Throw procedure will replace with Tab and Newline characters, respectively.
For example, the following line of code will generate the vbWatchdog screenshot below:
Throw "Could not find file {0} for account:\n\n{1}", "C:\Temp\MyFile.txt", "123456"
Here's another example:
Throw "Not implemented yet"
Frictionless Integration
As you can see, the Throw() procedure meets all the requirements from our list above. The one major drawback to the approach is that the error ends up being thrown from the Throw() procedure itself and not from the calling procedure. This is not a big deal with vbWatchdog, since the entire call stack is available to us. We can simply pull the calling procedure off of the vbWatchdog live call stack.
Now that I have a frictionless way to throw errors in VBA, I use the technique regularly. I hope you'll consider using this technique, as it's an important line of defense when programming defensively.
Sample Code
Private Const mDO_NOT_REPORT_ERR_NUM As Long = 65535
'---------------------------------------------------------------------------------------
' Procedure : Throw
' Author : Mike
' Date : 7/24/2014 - 12/7/2016
' Purpose : Throws a new error, calculating a unique error number by hashing the Msg.
' Notes - Msg may contain one or more placeholders which will be substituted
' with contents of the Notes() array.
' - Contents of the Notes array will be enclosed in single quotes when they are
' added to show that they are custom for a particular instance.
' - The placeholders may contain quotes around them but it is not necessary.
' - Tabs and newlines can be passed using escape sequences, \t and \n
' Usage : Throw "Could not find file {0} for account:\n\n{1}", FName, AcctNum
' Results : Could not find file 'C:\Temp\MyFile.txt' for account:
'
' '123456'
'---------------------------------------------------------------------------------------
'
Public Sub Throw(Msg As String, ParamArray Notes() As Variant)
Dim s As String, i As Integer
s = Msg
s = Replace(s, "\t", vbTab)
s = Replace(s, "\n", vbNewLine)
For i = LBound(Notes()) To UBound(Notes())
Dim ReplTxt As String
ReplTxt = "'" & Notes(i) & "'"
s = Replace(s, "'{" & i & "}'", ReplTxt)
s = Replace(s, "{" & i & "}", ReplTxt)
Next i
Err.Raise ErrNumFromMsg(Msg), , s
End Sub
'---------------------------------------------------------------------------------------
' Procedure : Alert
' Author : Mike
' Date : 12/29/2015 - 8/18/2017
' Purpose : Throws a new error using a reserved error number to prevent FogBugz reporting.
' Notes - This is exactly the same as the above Throw() procedure, but it will not
' generate a FogBugz report.
' - Use this when you want to inform the user of an error and have it bubble up the
' call stack, but you're not interested in seeing it show up in a FogBugz error report.
' - These errors will still be logged to text files or local databases if that
' is set up; only the FogBugz reporting is affected.
'---------------------------------------------------------------------------------------
'
Public Sub Alert(Msg As String, ParamArray Notes() As Variant)
Dim s As String, i As Integer
s = Msg
s = Replace(s, "\t", vbTab)
s = Replace(s, "\n", vbNewLine)
For i = LBound(Notes()) To UBound(Notes())
Dim ReplTxt As String
ReplTxt = "'" & Notes(i) & "'"
s = Replace(s, "'{" & i & "}'", ReplTxt)
s = Replace(s, "{" & i & "}", ReplTxt)
Next i
'--== Uncomment for use with vbWatchdog ==--
'With ErrEx.LiveCallstack
' .FirstLevel
' If .NextLevel Then
' mAlertCaller = " (" & .ModuleName & "." & .ProcedureName & ")"
' Else
' mAlertCaller = vbNullString
' End If
'End With
'---------------------------------------------
Err.Raise mDO_NOT_REPORT_ERR_NUM, , s
End Sub
'---------------------------------------------------------------------------------------
' Procedure : ErrNumFromMsg
' Author : Mike
' Date : 9/22/2015
' Purpose : Converts a msg into a somewhat-unique hash. This is a public convenience
' function primarily made available for testing.
'---------------------------------------------------------------------------------------
'
Public Function ErrNumFromMsg(Msg As String) As Long
ErrNumFromMsg = vbObjectError + GenerateErrNumFromMsgHash(Msg)
End Function
'---------------------------------------------------------------------------------------
' Procedure : GenerateErrNumFromMsgHash
' Author : Mike
' Date : 7/24/2014
' Purpose : Given a string of text, reliably reproduces a number between 513–65535
' to use when generating user-defined errors.
' Notes - This is an overly simplistic hash method. It is likely rife with potential
' collisions. However, we are not too concerned with the occasional
' collision, so it seems like a good trade-off.
' Usage :
'>> GenerateErrNumFromMsgHash("Could not find file {0} for account {1}")
' 41945
'---------------------------------------------------------------------------------------
'
Private Function GenerateErrNumFromMsgHash(Msg As String) As Long
Const ValRange As Long = 65535 - 513
Const ShiftVal As Long = 513
Dim i As Long, Val As Long
For i = 1 To Len(Msg)
Val = (Val + (Asc(Mid(Msg, i, 1)) ^ 2)) Mod ValRange
Next i
GenerateErrNumFromMsgHash = Val + ShiftVal
If GenerateErrNumFromMsgHash = mDO_NOT_REPORT_ERR_NUM Then
'make sure we don't accidentally prevent a reportable message from being reported
GenerateErrNumFromMsgHash = mDO_NOT_REPORT_ERR_NUM - 1
End If
End Function
Image by Sarah Richter from Pixabay
