Most of what you read on the internet about error handling in VBA is that every procedure should start with an
On Error Goto ... statement. Then, at the end of the procedure is the error handler. Before you get to the error handler, you throw in an
Exit Sub/Function line. Here's the basic concept:
Sub MySub() On Error Goto Err_MySub '... some code ... Exit Sub Err_MySub: MsgBox Err.Description End Sub
Addins--in particular MZTools--automate the process of adding this boilerplate to every routine in a project.
That said, there are still times when I use the above approach. vbWatchdog fully supports this kind of error handling. It can even be intermixed with routines that have no error handling at all. It truly is a "best of both worlds" approach: use customized error handling when necessary, but avoid all of that boilerplate code.
When I do use the
On Error Goto ... formulation, it is usually with a
Select ... Case statement for special handling of specific errors. I also include an
Exit_MySub: label where I place any cleanup code for the function. It's analogous to the Finally block if you are familiar with the Try...Catch...Finally paradigm from .NET and other programming languages. Here's an example:
Function ScanPageWIA(...) On Error Goto Err_ScanPageWIA '... WIA Scanning Code ... Exit_ScanPageWIA: 'Object cleanup code Exit Function Err_ScanPageWIA: Select Case Err.Number Case Else LogErr Err, Errors, "clsScan", "ScanPageWIA" 'Custom error logger End Select Resume Exit_ScanPageWIA End Function
Signal vs. Noise
The above is the bare boilerplate code. I used to include the code above in every single routine I wrote before I switched to vbWatchdog. That's nine lines of boilerplate. That was a major disincentive to writing short procedures. Why? It all goes back to the signal-to-noise ratio.
In case it's unclear, the boilerplate code is noise. It serves a purpose, but it says nothing about what our code does. It conveys no meaning. And if it conveys no meaning, then it's just noise.
The rest of our code is the signal. To boost our signal to noise ratio, we can only adjust two variables: signal and noise. Increase our lines of meaningful code or decrease the lines of boilerplate.
Lowering the noise
We could decrease our boilerplate by stripping out the Select Case statement unless we actually need it. That saves us 3 lines per routine. It also makes it more tedious to add those lines in when we need them. MZ-Tools only lets you define a single error-handling block. Before I switched over to vbWatchdog, I had settled on the nine-line error-handling block as my default in MZ-Tools. In other words, my noise rate was fixed at 9 LPR (lines per routine).
Boosting the signal
If our noise rate is fixed at 9 LPR, then the only way to reduce the total amount of noise is to reduce the number of routines. Let's do some quick math. Say we have 10 routines with 20 lines of meaningful code each. That's 20 LPR of signal to 9 LPR of noise, a 20:9 signal to noise ratio. But if we jam all those lines into a single monster routine, then we eliminate all but 9 lines of boilerplate. That's 200 LPR of signal to 9 LPR of noise, a 200:9 signal to noise ratio!
But why stop there? If all our code is in a single routine, then we can have a 10,000:9 or 250,000:9 signal to noise ratio.
This is silly for a few reasons. For one, procedures themselves convey a great deal of signal, far more than any single line of code. And, for another, packing that many lines of code into one routine might be violating the single-responsibility principle. Unless you subscribe to the notion that "Run the program" is a single responsibility.
Earlier I said that we could not lower our boilerplate LPR below 9, or possibly 6. But what if we could lower it to 0? What if we could eliminate our boilerplate code entirely? Our signal to noise ratio would be unfathomable. (And I mean that in the literal mathematical sense, seeing as division by zero is meaningless.)
Eliminating boilerplate error-handling code from VBA? What is this some kind of black magic? Yes. Yes it is.
I dug into the VbWatchdog code once. It’s black magic.— Chris McClellan (@Rubberduck203) September 24, 2020
Meme generation courtesy of imgflip.com (meme text my own)