I enjoy a good jigsaw puzzle as much as the next guy.  There's something satisfying about starting with a pile of seemingly random pieces and watching the picture slowly take life as you restore order to the chaos.

I stopped doing jigsaw puzzles, though.  It's been probably, oh, 13 years now.  Let me do the math.  I've got four kids; the oldest is 15.  Yep, two years old is right about the time that she was old enough to wander up to an unfinished puzzle, abscond with one of the pieces, and feed it to the dog or the heat register or the toilet.  

And as satisfying as it is to place the final piece into a jigsaw puzzle, it's just as soul-crushing to place the penultimate piece into the puzzle and realize the final piece is missing.  

That's how I used to feel about my error handling code.

vbWatchdog's Variables Inspector

If you use vbWatchdog for your error handling (you should), then you should be familiar with one of its most powerful features: the Variables Inspector. This object provides access to every variable in scope at each level of the call stack.  That level of detail is digital gold when it comes time to troubleshoot bugs.

Over the years, I've developed an advanced error handling module that logs all of this information.  As I fine-tuned my error handling, one blemish began to stand out.  While I could extract the values of most of my variables, all I could ever get out of object variables was either 'Nothing' or '{Object}'.

This is no knock on vbWatchdog.  An object can be anything.  What other value could it possibly show?  Still, this missing piece of the puzzle gnawed at me.  I could feel the universe laughing at me when I was troubleshooting some bug and the key to sorting it out was hidden behind that one maddeningly coy word, '{Object}'.

If only I had some way to know one or two of the identifying properties of that object, I could figure out exactly what was going on.

First attempt

My first attempt at solving the problem is every frustrated programmer's go-to tool: brute force.  In my global error handler, I added a Select...Case statement around the .TypeDesc.  

For example, I have a SQL builder class that I call clsSQL.  One of the properties in that class is .LastSQL.  That property contains the last SQL statement that the class built or executed.  It could be a SELECT statement or an INSERT/UPDATE/DELETE/etc.  (I borrowed the idea from web2py's DAL object.)

Here is a portion of my global error handler:

Select Case .TypeDesc
Case "clsSQL"
    If Not .Value Is Nothing Then
        ThisVar = .Name & ".LastSQL = " & .Value.LastSQL
    End If

Over time I began adding additional custom object types to this list.  With each custom type, I would need to fetch a different custom property.

I had my final piece to the puzzle.  The problem is I found it floating in the dog's water bowl, all chewed up on one side.  I guess you could say my puzzle was complete, but it was a Pyrrhic victory.

A cure that does more harm than good

I quickly realized that this solution was not going to scale.  There were many problems.  First, my global error handling code was going to get bloated.  I keep my error handling code in a single standard module within my code library.  That means anytime I wanted to add support for a class module, that code would get added to every one of my projects.  That was true even if the class module was only used in a single project.

The next problem is that I was introducing external dependencies into my error handling code.  What if I changed my clsSQL class someday and rename or remove the .LastSQL method?  What are the chances I would realize that such a dependency existed while I was working in my clsSQL class?  This approach would quickly collapse under its own weight unless I figured out an alternative.

Looking to Python for a solution

I realized that what I really wanted was some way to determine a canonical representation of an object from within that object.  I wanted to be able to implement this representation as simply or complexly as needed.  I wanted a way to guarantee that it wouldn't blow up at runtime.  I wanted it to be completely optional for every class module.

This seems like a long wish list, but I was able to satisfy every item on it with the solution I found.

Once again, I borrowed an idea from Python.  Python objects all have a special property known as ._repr.  This property is the string representation of the object.  By default, it will return the type name and memory address of the object instance.  However, Python programmers can define a .__repr__ method to override the default behavior.  This is the juicy bit that I wanted for my VBA classes.

I finally found my ideal solution.  Unfortunately, I found it in another language where the solution is actually a feature of the language itself.  How is that supposed to help me in VBA?  It turns out the idea was the important part; I just had to get a little creative with the implementation.

Interfaces to the rescue

To smuggle this Python concept into VBA, I turned to a rarely-used feature of the language: interfaces and the TypeOf operator.  Here's how it works.

I created a class module that I named iRepresentation.  Interfaces in most languages are named with a leading "i" by convention.  Of course, you can name your modules whatever you like.  Here is the full code for my iRepresentation class.

iRepresentation.cls

`--== iRepresentation ==-- class module
Option Compare Database
Option Explicit

Public Property Get Repr() As String
End Property

I should point out that there is nothing special about a class module that serves as an interface in VBA.  By that, I mean that there is no module level keyword or hidden attribute that we need to set.  We can even instantiate a new object using this type, though there wouldn't be much of a point (one exception is testing, but that's a topic for a different day).  For example, the following would be valid code:

Dim Representation As iRepresentation
Set Representation = New iRepresentation

Debug.Print Representation.Repr

Now, let's say I have a custom class module named oJigsawPuzzle.  The class module has several properties and methods, but we want one that will help us identify which JigsawPuzzle object we are dealing with when an error is raised.  One obvious candidate for such a job is the SKU, which uniquely identifies the puzzle as a product on store shelves.  Of course, depending on our situation, we may want to include other information in our representation, too.

oJigsawPuzzle.cls

'--== oJigsawPuzzle ==-- class module
Option Compare Database
Option Explicit

Implements iRepresentation   ' <-- We need this line...

Private mSKU As String
Private mPieceCount As Long
Private mDesigner As String
Private mTitle As String
Private mHeightInInches As Double
Private mWidthInInches As Double

'... and these three lines
Private Property Get iRepresentation_Repr() As String
    iRepresentation_Repr = mSKU
End Property

Here's where the magic comes in.  When we are working our way through the Variables Inspector object, we can now test each object variable to see if it implements this interface.  And, if it does, we can grab that value and log it along with the rest of our variable values.

Error handler excerpt

' --== Global Error Handler excerpt ==--

'Include Repr property value for classes that 
'        implement the iRepresentation interface
If TypeOf .Value Is iRepresentation Then
    Dim ObjWithRepr As iRepresentation
    Set ObjWithRepr = .Value
    ThisVar = .Name & ".Repr = " & ObjWithRepr.Repr
End If

And with that, my error handling puzzle is now complete.  All pieces are accounted for.  There are no bite marks.  None of the pieces is peeling apart.  There are no empty spaces.  

I have finally restored order to the chaos.

Image by Alexas_Fotos from Pixabay