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.

Scenario

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.

Let’s use 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 Append or CreateProperty?
• 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.

Add a HasProperty procedure

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 prp and 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 HasProperty has to handle the property in order to find out if it exists then simply returns a Boolean result, 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 NewValue more than necessary. We either pass it in the CreateProperty or set the Value property of the property.
• The HasProperty function implicitly assumes that the object has a Properties member and calls it late-bound, which means it’s a runtime error if a wrong kind of object is provided to it.

Use TryGetProperty instead

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 Properties is no longer late-bound. We do not have to hope that an object has a property named Properties and it’s of DAO.Properties. This can be verified at the compile-time.
• Instead of just a Boolean result, we can also get the retrieved Property object, but only on the success. If we fail, the OutProperty parameter will be Nothing. We still will use the Boolean result to help with setting the up flow as you will see shortly.
• By naming our new function with Try prefix, 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 TryGetProperty

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 true, the prp will be initialized with the property we want to manipulate. Otherwise, the prp remains Nothing. We can then use the CreateProperty to initialize the prp variable.

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:

Adding TrySetPropertyValue procedure

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 TryGetProperty and TrySetPropertyValue

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.

Creating TryCreateOrSetProperty procedure

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 TryCreateOrSetProperty function:

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 MsgBox out of nowhere, we use Err.Raise and 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 SourceDaoObject parameter 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 false result. 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.

Conclusion

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.