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.
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:
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.
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.
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.
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.