Writing Readable Code for VBA – Try* pattern
Lately, I’ve been finding myself using the
Try pattern more and more. I really like this pattern because it makes for much more readable code. This is especially important when programming in a mature programming language like VBA where the error handling is intertwined with the control flow. Generally, I find any procedures that relies on error handling as a control flow to be harder to follow.
Let’s start with an example. DAO object model is a perfect candidate because of how it works. See, all DAO objects has
Properties collection, which contains
Property objects. However, anyone is able to add custom property. In fact, Access will add several properties to various DAO objects. Therefore, we may have a property that might not exist and must handle both the case of changing an existing property’s value and the case of appending a new property.
Subdatasheet property as an example. By default, all tables created via Access UI will have the property set to
Auto, but we might not want that. But if we have tables that are created in code or some other way, it might not have the property. So we can start with an initial version of the code to update all tables’ property and handle both the cases.
Public Sub EditTableSubdatasheetProperty( _ Optional NewValue As String = "[None]" _ ) Dim db As DAO.Database Dim tdf As DAO.TableDef Dim prp As DAO.Property Const SubDatasheetPropertyName As String = "SubdatasheetName" On Error GoTo ErrHandler Set db = CurrentDb For Each tdf In db.TableDefs If (tdf.Attributes And dbSystemObject) = 0 Then If Len(tdf.Connect) = 0 And (Not tdf.Name Like "~*") Then 'Not attached, or temp. Set prp = tdf.Properties(SubDatasheetPropertyName) If prp.Value <> NewValue Then prp.Value = NewValue End If End If End If Continue: Next ExitProc: Exit Sub ErrHandler: If Err.Number = 3270 Then Set prp = tdf.CreateProperty(SubDatasheetPropertyName , dbText, NewValue) tdf.Properties.Append prp Resume Continue End If MsgBox Err.Number & ": " & Err.Description Resume ExitProc End Sub
The code will probably work. However, to understand it, we probably have to diagram some flow chart. The line
Set prp = tdf.Properties(SubDatasheetPropertyName) could potentially throw an error 3270. In this case, the control jumps to the error handling section. We then create a property and then resume at a different point of the loop using the label
Continue. There are some questions…
- What if 3270 is raised on some other line?
- Suppose that the line
Set prp =...doesn’t throw error 3270 but actually some other error?
- What if while we are inside the error handler, another error happens when executing the
- Should this function even be showing a
Msgbox? Think about functions that are supposed to work on something on behalf of forms or buttons. If the functions show a message box, then exit normally, the calling code has no idea that something has gone wrong and might continue doing things it shouldn’t be doing.
- Can you glance at the code and understand what it does immediately? I can’t. I have to squint at it, then think about what should happen under the case of an error and mentally sketch the path. That’s not easy to read.
Can we do better? Yes! Some programmers already recognize the problem with using error handling like I illustrated and wisely abstracted this out into its own function. Here’s a better version:
Public Sub EditTableSubdatasheetProperty( _ Optional NewValue As String = "[None]" _ ) Dim db As DAO.Database Dim tdf As DAO.TableDef Dim prp As DAO.Property Const SubDatasheetPropertyName As String = "SubdatasheetName" Set db = CurrentDb For Each tdf In db.TableDefs If (tdf.Attributes And dbSystemObject) = 0 Then If Len(tdf.Connect) = 0 And (Not tdf.Name Like "~*") Then 'Not attached, or temp. If Not HasProperty(tdf, SubDatasheetPropertyName) Then Set prp = tdf.CreateProperty(SubDatasheetPropertyName , dbText, NewValue) tdf.Properties.Append prp Else If tdf.Properties(SubDatasheetPropertyName) <> NewValue Then tdf.Properties(SubDatasheetPropertyName) = NewValue End If End If End If End If Next End Sub Public Function HasProperty(TargetObject As Object, PropertyName As String) As Boolean Dim Ignored As Variant On Error Resume Next Ignored = TargetObject.Properties(PropertyName) HasProperty = (Err.Number = 0) End Function
Instead of mixing up the execution flow with the error handling, we now have a function
HasFunction which neatly abstracts out the error-prone check for a property that may not exist. As a consequence, we don’t need complex error handling/execution flow that we saw in the first example. This is a big improvement and makes for somewhat readable code. But…
- We have one branch that uses the variable
prpand we have another branch that uses
tdf.Properties(SubDatasheetPropertyName)that in fact refers to the same property. Why are we repeating ourselves with two different ways to reference the same property?
- We are handling the property quite a lot. The
HasPropertyhas to handle the property in order to find out if it exists then simply returns a
Booleanresult, leaving it up to the calling code to again try and get the same property again to change the value.
- Similarly, we are handling the
NewValuemore than necessary. We either pass it in the
CreatePropertyor set the
Valueproperty of the property.
HasPropertyfunction implicitly assumes that the object has a
Propertiesmember and calls it late-bound, which means it’s a runtime error if a wrong kind of object is provided to it.
Can we do better? Yes! That’s where we need to look at the Try pattern. If you’ve ever programmed with .NET, you probably have seen methods like
TryParse where instead of raising an error on failure, we can set up a condition to do something for success and something else for failure. But more importantly, we have the result available for success. So how would we improve on the
HasProperty function? For one thing, we should return the
Property object. Let’s try this code:
Public Function TryGetProperty( _ ByVal SourceProperties As DAO.Properties, _ ByVal PropertyName As String, _ ByRef OutProperty As DAO.Property _ ) As Boolean On Error Resume Next Set OutProperty = SourceProperties(PropertyName) If Err.Number Then Set OutProperty = Nothing End If On Error GoTo 0 TryGetProperty = (Not OutProperty Is Nothing) End Function
With few changes, we’ve scored few big wins:
- The access to
Propertiesis no longer late-bound. We do not have to hope that an object has a property named
Propertiesand it’s of
DAO.Properties. This can be verified at the compile-time.
- Instead of just a
Booleanresult, we can also get the retrieved
Propertyobject, but only on the success. If we fail, the
OutPropertyparameter will be
Nothing. We still will use the
Booleanresult to help with setting the up flow as you will see shortly.
- By naming our new function with
Tryprefix, we are indicating that this is guaranteed to not throw an error under normal operating conditions. Obviously, we can’t prevent out of memory errors or something like that but at that point, we have much bigger problems. But under the normal operating condition, we’ve avoided tangling up our error handling with the execution flow. The code now can be read from top to bottom without any jumping forth or back.
Note that by convention, I prefix the “out” property with
Out. That helps make it clear that we are supposed to pass in the variable to the function uninitialized. We are also expecting that the function will initialize the parameter. That will be clear when we look at the calling code. So, let’s set up the calling code.
Revised calling code using
Public Sub EditTableSubdatasheetProperty( _ Optional NewValue As String = "[None]" _ ) Dim db As DAO.Database Dim tdf As DAO.TableDef Dim prp As DAO.Property Const SubDatasheetPropertyName As String = "SubdatasheetName" Set db = CurrentDb For Each tdf In db.TableDefs If (tdf.Attributes And dbSystemObject) = 0 Then If Len(tdf.Connect) = 0 And (Not tdf.Name Like "~*") Then 'Not attached, or temp. If TryGetProperty(tdf, SubDatasheetPropertyName, prp) Then If prp.Value <> NewValue Then prp.Value = NewValue End If Else Set prp = tdf.CreateProperty(SubDatasheetPropertyName , dbText, NewValue) tdf.Properties.Append prp End If End If End If Next End Sub
The code is now a bit more readable with the first Try pattern. We’ve managed to reduce the handling of the
prp. Note that we pass the
prp variable into the <codeTryGetProperty. If it returns
prp will be initialized with the property we want to manipulate. Otherwise, the
Nothing. We can then use the
CreateProperty to initialize the
We also flipped the negation so that the code becomes easier to read. However, we haven’t really reduced the handling of
NewValue parameter. We still have another nested block to check the value. Can we do better? Yes! Let’s add another function:
Public Function TrySetPropertyValue( _ ByVal SourceProperty As DAO.Property, _ ByVal NewValue As Variant_ ) As Boolean If SourceProperty.Value = PropertyValue Then TrySetPropertyValue = True Else On Error Resume Next SourceProperty.Value = NewValue On Error GoTo 0 TrySetPropertyValue = (SourceProperty.Value = NewValue) End If End Function
Because we are guaranteeing that this function won’t throw an error when changing the value, we call it
TrySetPropertyValue. More importantly, this function helps encapsulate all the gory details surrounding changing the property’s value. We have a way to guarantee that the value is the value we expected it to be. Let’s look at how the calling code will be changed with this function.
Updated calling code using both
Public Sub EditTableSubdatasheetProperty( _ Optional NewValue As String = "[None]" _ ) Dim db As DAO.Database Dim tdf As DAO.TableDef Dim prp As DAO.Property Const SubDatasheetPropertyName As String = "SubdatasheetName" Set db = CurrentDb For Each tdf In db.TableDefs If (tdf.Attributes And dbSystemObject) = 0 Then If Len(tdf.Connect) = 0 And (Not tdf.Name Like "~*") Then 'Not attached, or temp. If TryGetProperty(tdf, SubDatasheetPropertyName, prp) Then TrySetPropertyValue prp, NewValue Else Set prp = tdf.CreateProperty(SubDatasheetPropertyName , dbText, NewValue) tdf.Properties.Append prp End If End If End If Next End Sub
We’ve eliminated an entire
If block. We now can simply read the code and immediately that we are trying to set a property value and if something goes wrong, we just keep moving on. That is much easier to read and the name of the function is self-describing. A good name makes it less necessary to look up the definition of the function in order to understand what it is doing.
The code is more readable but we still have that
Else block creating a property. Can we do better still? Yes! Let’s think about what we need to accomplish here. We have a property that may or may not exist. If it doesn’t, we want to create it. Whether it existed already or not, we need it to be set to a certain value. So what we need is a function that will either create a property or update the value if it already exists. To create a property, we must call
CreateProperty which unfortunately is not on the
Properties but rather different DAO objects. Thus, we must late bind by using
Object data type. However, we can still provide some runtime checks to avoid errors. Let’s create a
Public Function TryCreateOrSetProperty( _ ByVal SourceDaoObject As Object, _ ByVal PropertyName As String, _ ByVal PropertyType As DAO.DataTypeEnum, _ ByVal PropertyValue As Variant, _ ByRef OutProperty As DAO.Property _ ) As Boolean Select Case True Case TypeOf SourceDaoObject Is DAO.TableDef, _ TypeOf SourceDaoObject Is DAO.QueryDef, _ TypeOf SourceDaoObject Is DAO.Field, _ TypeOf SourceDaoObject Is DAO.Database If TryGetProperty(SourceDaoObject.Properties, PropertyName, OutProperty) Then TryCreateOrSetProperty = TrySetPropertyValue(OutProperty, PropertyValue) Else On Error Resume Next Set OutProperty = SourceDaoObject.CreateProperty(PropertyName, PropertyType, PropertyValue) SourceDaoObject.Properties.Append OutProperty If Err.Number Then Set OutProperty = Nothing End If On Error GoTo 0 TryCreateOrSetProperty = (OutProperty Is Nothing) End If Case Else Err.Raise 5, , "Invalid object provided to the SourceDaoObject parameter. It must be an DAO object that contains a CreateProperty member." End Select End Function
Few things to note:
- We were able to build up on the earlier
Try*function we defined, which helps cut down on the coding of the function’s body, allowing it to focus more on the creation in the case there is no such property.
- This is necessarily more verbose due to the additional runtime checks, but we are able to set it up so that errors do not alter the execution flow and we can still read from top to bottom with no jumping.
- Instead of throwing a
MsgBoxout of nowhere, we use
Err.Raiseand return a meaningful error. The actual error handling is delegated to the calling code which can then decide whether to show a messagebox to the user or do something else.
- Because of our careful handling and providing that the
SourceDaoObjectparameter is valid, all the possible path guarantees that any problems with creating or setting an existing property’s value will be handled and we will get a
falseresult. That affects the calling code as we will see shortly.
Final version of the calling code
Let’s update the calling code to use the new function:
Public Sub EditTableSubdatasheetProperty( _ Optional NewValue As String = "[None]" _ ) Dim db As DAO.Database Dim tdf As DAO.TableDef Dim prp As DAO.Property Const SubDatasheetPropertyName As String = "SubdatasheetName" Set db = CurrentDb For Each tdf In db.TableDefs If (tdf.Attributes And dbSystemObject) = 0 Then If Len(tdf.Connect) = 0 And (Not tdf.Name Like "~*") Then 'Not attached, or temp. TryCreateOrSetProperty tdf, SubDatasheetPropertyName, dbText, NewValue End If End If Next End Sub
That was quite an improvement in the readability. In the original version, we would have to scrutinize over a number of
If blocks and how error handling alters the flow of execution. We would have to figure out what exactly the content was doing to conclude that we are trying to get a property or create it if it doesn’t exist and have it be set to a certain value. With the current version, it’s all there in the name of the function,
TryCreateOrSetProperty. We now can see what the function is expected to do.
You might be wondering, “but we added much more functions and much more lines. Isn’t that a lot of work?” It is true that in this current version, we defined 3 more functions. However, you can read each single function in isolation and still easily understand what it should do. You also saw that the
TryCreateOrSetProperty function could build up on the 2 other
Try* functions. That means we have more flexibility in assembling together the logic.
So if we write another function that does something with the property of objects, we don’t have to write it all over nor do we copy-paste the code from the original
EditTableSubdatasheetProperty into the new function. After all, the new function might need some different variants and thus require a different sequence. Finally, keep in mind that the real beneficiaries are the calling code that needs to do something. We want to keep the calling code fairly high-level without being mired in details which can be bad for maintenance.
You can also see that the error handling is significantly simplified, even though we used
On Error Resume Next. We no longer need to look up the error code because in the majority of the case, we are only interested in whether it succeeded or not. More importantly, the error handling did not change the execution flow where you have some logic in the body and other logic in the error handling. The latter is a situation we definitely want to avoid because if there is an error within the error handler, then the behavior can be surprising. It’s best to avoid that from being a possibility.
It’s all about abstraction
But the most important score we win here is the level of abstraction we can now attain. The original version of
EditTableSubdatasheetProperty contained a lot of low-level details about the DAO object really isn’t about the core goal of the function. Think about days where you’ve seen a procedure that are hundreds of line long with deeply nested loops or conditions. Would you want to debug that? I don’t.
So when I see a procedure, the first thing I really want to do is rip out the parts into their own function, so that I can raise the level of abstraction for that procedure. By forcing ourselves to push the level of abstraction, we also can avoid large classes of bugs where the cause is to one change in part of the mega-procedure has unintended ramifications for the other parts of the procedures. When we are calling functions and passing parameters, we also reduce the possibility of unwanted side-effects interfering with our logic.
Hence why I love the “Try*” pattern. I hope you find it useful for your projects, too.