DRY: Don't Repeat Yourself in VBA

Join me on Thursday, May 6, 2021 at 9:30 PM EDT (May 7, 01:30 UTC) for my presentation, Navigating Continuous Subforms using WithEvents, to the newly-minted Access Pacific User Group.

One of the fundamental principles in programming is DRY: Don't Repeat Yourself. Even new developers learn this concept rather quickly. Let me take you along on my own personal journey to stop repeating myself.

Copy Paste

While others may disagree, I actually consider copy-paste to be the first step on the path to not repeating yourself. Think of it as the DRY brute force approach.

I think this falls under DRY because it's the conceptual recognition of a repeating pattern. Recognizing a pattern is the prerequisite to any of the more advanced techniques that follow.

Cut Paste: Functions and Subroutines

Before long, most developers recognize the problems with simply copying and pasting code. If there is a problem with the pattern itself (i.e., the algorithm), you need to go find all the places you pasted the code and update it in every location. This is inefficient. It's also very likely that you will miss one or more instances of the copied code.

A far better approach is to cut the code from its original location; paste it into a standalone Function or Subroutine; and then call the new function or subroutine from the original location.

Many VBA users without a development background never get past this point (if they get here at all!). And, truth be told, this step in the journey probably represents the biggest single performance boost from one step to another. So, if this is as far as you've gotten, that's okay. But there are some limitations to this approach, which should become apparent as you read on.

User Defined Types

One limitation of functions is that they only return a single value.

Of course, this is not technically true, as one can "return" values by assigning values to arguments passed by reference. With few exceptions, though, you should generally avoid this technique is it tends to make code less readable.

So, how should you return multiple values from a function? One simple approach is to use a user-defined type. Here's an example from before I wrote my more general Parse() function:

Type CnInfo
    IsODBC As Boolean
    Server As String
    Database As String
    DSN As String
End Type

'---------------------------------------------------------------------------------------
' Procedure : ParseConnect
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/the-dry-journey/
' Date      : 3/6/2009
' Purpose   : Parses a Connection String into usable chunks of information.
' Usage     : With ParseConnect(CurrentDB.TableDefs("MyTableName").Connect)
'                 DBName = .Database
'                 If .IsODBC Then ServerName = .Server
'             End With
'---------------------------------------------------------------------------------------
'
Private Function ParseConnect(ConnectionString As String) As CnInfo 'vv
Dim cn As String
Dim PropName As String, PropVal As String, Pair As String
Dim scPos As Integer, eqPos As Integer

    cn = ConnectionString
    Do While Len(cn) > 0
        scPos = InStr(cn, ";")
        If scPos = 0 Then
            Pair = cn
            cn = vbNullString
        Else
            Pair = Left(cn, scPos - 1)
            cn = Mid(cn, scPos + 1)
        End If
        
        eqPos = InStr(Pair, "=")
        If eqPos = 0 Then
            ParseConnect.IsODBC = (Pair = "ODBC")
        Else
            PropName = Left(Pair, eqPos - 1)
            PropVal = Mid(Pair, eqPos + 1)
            'Debug.Print PropName, PropVal
            Select Case PropName
            Case "SERVER": ParseConnect.Server = PropVal
            Case "DATABASE": ParseConnect.Database = PropVal
            Case "DSN": ParseConnect.DSN = PropVal
            End Select
        End If
    Loop
End Function

Unfortunately, user defined types (UDTs) exist in a sort of VBA limbo: they're not scalar data types but they're also not full-blown objects. This hobbles them in a variety of ways, most notably in that you can't add them to collections by simply defining them in a standard module:

Class Modules

The next big jump in my DRY journey was to begin using class modules.

I have to confess, it took me months (if not years) to begin using class modules to their full potential. In the beginning, I could not figure out what the big deal was. As far as I could tell, a class module was just the standard module's more inconvenient cousin.

If I could sum up the main advantage of class modules, it would be this (this is an intentional technical simplification):

Class modules can act like mini programs (complete with state) that are independent from the rest of the program.

If you're not familiar with the term, think of "state" as simply the values currently assigned to the variables. As you write more complex programs, you quickly realize the importance of minimizing side effects of your code. Well-written classes encapsulate complexity, without affecting the state of the rest of the program.

For a couple practical examples of the kinds of class modules to which I'm referring here, read these articles on my status bar class and a registry operations class that I modified for 64-bit compatibility.

WithEvents

For a long time, I struggled with how to write generic code that I could apply to events on forms, reports, and the controls thereon. Eventually, I came across the WithEvents keyword and its seemingly magical powers. I'll be presenting a class module for implementing Excel-style arrow key nagivation on a continuous form with only two lines of code on Thursday, May 6, 2021. Check back with this blog between now and then, as I will be writing several posts breaking down the more technical aspects of the talk.

Interfaces

We're starting to get into pretty advanced VBA territory at this point. But using interfaces allows you to do some pretty cool things.

Code Generation

Sometimes there's no way to avoid large amounts of boilerplate code. In times like these, I use what I like to think of as a more elegant brute force approach to DRY than simply copy paste: code generation.

You probably want an example of when this would be a good idea, don't you? How about creating a strongly-typed collection class? Good enough for you?

If generating code feels ... icky... to you, then I agree. But until we get generics in VBA, it will have to do.

Other Techniques

I'm sure I've left out other techniques to avoid repeating yourself when writing VBA, but I think this list is a pretty good start. Let me know on Twitter or via email (mike at nolongerset dot com) if you have any techniques you prefer that I left out of my list here.

Happy coding!

Photo by Yarenci Hdz on Unsplash