Hash and Stack in VBA for centralised configuration and error handling

Previously I wrote about an implementation for stacks and hashes** in VBA ~ these were used in an old Access project to give centralised error handling and access to variables (such as parameter based error strings) from a table.

** – As pointed out by Anon there isn’t really any significant benefit to rolling your own Hash – use the Scripting.Dictionary instead. Unless you need to avoid including extra libraries (Microsoft Scripting Runtime) or are working with something less than Office 2000 (apparently the runtime is also within windows 2000) in which case my sincere sympathy is with you.

Overview

It works a little (a lot?) like this, put a stack and hash in a VBA module.

Then the stack can assist the error handling. At the start of each function push the function name onto the stack and just before leaving pop itself back off.

When an error is encountered the centralised error handling routine is called ~ this reports the error message and steps through the preceding function calls to show the chain of events leading up to the problem. This makes tracing back through relevant code to find the underlying cause much easier.

The hash (or hash table) provides lazy access to configuration variables and various strings including error messages.

Stack ~ Error Handling

When debugging it is often helpful to know where in the code the error occurred (even if the issue started elsewhere). However because many sections of code are frequently re-used with different contexts and data it is additionally helpful to know not only the failing function, but the chain of preceding functions.

Adding a small section of code to the beginning and end of each function (plus an error handler) gives you this functionality and makes reporting runtime errors to your users more graceful.

Start function

On Error GoTo ErrorGoTo
  Util.PushStack "ThisFunctionName (" & Self & ")"

Note: The Self variable is the module or class name the function is from.

End function

ExitGoTo:
  Util.PopStack
  Exit Sub
ErrorGoTo:
  Util.ErrorHandler
  Resume ExitGoTo

See the code section at the end of this article for the full ErrorHandler code.

Hash ~ Configuration and Strings

Often it is useful to separate strings into a common location (a database or per language strings file). This allows easier options for modifying text and the possibility of multi-language support. However it’s slow to head out to the database catch and hog-tie your string and bring it back every time you need to use it.

The alternative used here is a lazy hash table in the middle. Now, when you’re planning a string hunting trip we ask the hash if it’s got the variety of string we’re hunting. If so the string is handed over and no trip to the database ensues. If not, the same function that requests the string from the hash (you call one function that deals with the hash and / or database without troubling you with the details) hunts down that string in the database, brings it back and gives you and the hash a copy. Next time you ask the hash, your string will be waiting.

That’s all well and good for a read-only environment. However you’ll need an additional function to handle adding or updating strings. This function directly updates the database and the hash with the new string.

Lazy or late

There are two ways we could populate the hash: when the program starts we could populate it with every string in existence (the ones we need anyway); or, the lazy / late way where we only populate it once it has been requested.

The choice is basically a memory size to speed trade-off.

Limitations

• You may also notice that if the underlying strings were changed by something or someone else we won’t get the update until we restart the application (clearing the hash). If this is a concern there are a variety of strategies for cache coherency that could be used.
• Hashes / caches can also be useful to limit the memory footprint ~ the hash in this article doesn’t have any mechanism for controlling (reducing) its size.

New project

I’m pulling out all this code again, because I think it’ll be useful for a little project I’m now doing in Excel. The main change I’ll need is different storage (previously used an Access table) for error message strings and configuration.

Code

It is worth pointing out that the stack functions can’t push and pop themselves onto and off of itself. But also that the error handling needs to be able to deal with an error occurring when getting the stack, otherwise we’ll end up in an infinite error handling loop.

This is also the same reason that the strings used in the ErrorHandling function are not looked up using the hash as we may already be trying to handle an error caused by the the hash.

Option Explicit

Private Const Self = "Util"

Private Hash As HashTable
Private Stack As Stack

Public Function GlobalHashTable() As HashTable
On Error GoTo ErrorGoTo
  Util.PushStack "GlobalHashTable (" & Self & ")"

  Set GlobalHashTable = Nothing
  If (Hash Is Nothing Or IsNull(Hash)) Then
    Set Hash = New HashTable
  End If

  Set GlobalHashTable = Hash

ExitGoTo:
  Util.PopStack
  Exit Function
ErrorGoTo:
  Util.ErrorHandler
  Resume ExitGoTo
End Function

Public Function GlobalStack() As Stack
On Error GoTo ErrorGoTo

  Set GlobalStack = Nothing
  If (Stack Is Nothing Or IsNull(Stack)) Then
    Set Stack = New Stack
  End If

  Set GlobalStack = Stack

ExitGoTo:
  Exit Function
ErrorGoTo:
  Util.ErrorHandler Util.GetVariable("GlobalStackFailure")
  Resume ExitGoTo
End Function

Public Function GetVariable(ByVal name As String) As String
On Error GoTo ErrorGoTo
  Util.PushStack "GetVariable (" & Self & ")"

  Dim qryDef As QueryDef
  Dim rs As Recordset

  GetVariable = ""
  If (Util.GlobalHashTable.Exists(name)) Then
    GetVariable = Util.GlobalHashTable.item(name)
	Else
		'This section reads from the configuration store (would need to be different for Excel)
	  Set qryDef = CurrentDb.QueryDefs("LocalSettingForVariable")
	  qryDef.Parameters("InputName") = name
	  Set rs = qryDef.OpenRecordset
	  If (Not IsNull(rs)) Then
	    If (Not IsNull(rs(0)) And Not rs.EOF) Then
	      GetVariable = rs(0)
	      Util.GlobalHashTable.item(name) = rs(0)
	    End If
	    rs.Close
	    qryDef.Close
	  End If
  End If

ExitGoTo:
  Util.PopStack
  Exit Function
ErrorGoTo:
  Util.ErrorHandler
  Resume ExitGoTo
End Function

Public Sub SetVariable(ByVal name As String, ByVal val As String)
On Error GoTo ErrorGoTo
  Util.PushStack "SetVariable (" & Self & ")"

	'This section writes to the configuration store (would need to be different for Excel)
  Dim qryDef As QueryDef

  Set qryDef = CurrentDb.QueryDefs("UpdateLocalSetting")
  qryDef.Parameters("InputName") = name
  qryDef.Parameters("InputValue") = val
  qryDef.Execute

  'Add or update in hash table
  Util.GlobalHashTable.item(name) = val

ExitGoTo:
  Util.PopStack
  Exit Sub
ErrorGoTo:
  Util.ErrorHandler
  Resume ExitGoTo
End Sub

Public Sub ErrorHandler(Optional ByVal Notes As String)
  Dim output As String

  output = "Error #: " & err.Number & ", Description: " & err.Description & ""
  If (Not IsNull(Notes) And Notes <> "") Then
    output = output & vbCrLf & "Notes: " & Notes
  End If
  'Special case: can't do a stack dump if the failure is in the global stack
  If (Notes <> Util.GetVariable("GlobalStackFailure")) Then
    output = output & vbCrLf & "Stack Dump: " & Util.GlobalStack.StackDump
  End If

  MsgBox output, vbOKOnly, "Error"
End Sub

Public Sub PushStack(ByVal item As String)
On Error Resume Next
  Util.GlobalStack.Push item
End Sub

Public Function PopStack() As Variant
On Error Resume Next
  PopStack = Util.GlobalStack.Pop()
End Function