A Treatise on Types of VBA Procedures

hierarchy chart

To support modular programming, at least three different types of VBA procedures should be recognized. I'll call them first-level, second-level and third-level procedures to distinguish them. I humbly put these concepts forward as best practice (if they weren't already). Let's take some time to discuss the difference.

First-Level Procedures

In general, first-level procedures

  • provide facts
  • do not evaluate any expression
  • perform a single, discrete operation
  • are usually, but not always, functions that return values to the calling procedure

This is the lowest level in the procedure hierarchy. A first-level procedure must not evaluate the facts in any way, but instead only mindlessly retrieve them for use by other procedures or perform some simple action. For example, a function that opens a recordset, retrieves values from a database and returns them as an array to whatever procedure calls the function, would be considered a first-level procedure. A function that returns the state of an environment variable would also be a first-level procedure.

Second-Level Procedures

In general, second-level procedures

  • cannot exist without first-level procedures
  • imply the existence of at least one first-level procedure
  • do not perform the actions of a first-level procedure
  • take the output of first-level procedures and evaluate them according to programmer logic (value judgments), or outputs them
  • perform only one evaluation
  • are usually, but not always, functions that return values to the calling procedure

A second-level procedure does not retrieve facts, but mindlessly takes what is passed to it and evaluates it according to whatever criteria is needed by the programmer. For example, a function that takes an integer input and returns grade level is a second-level procedure. Most importantly, a second-level function that tries to perform more than one action should be broken into separate functions.

Third-Level Procedures

In general, third-level procedures

  • can perform multiple evaluations
  • do not perform actions of first or second-level procedures
  • imply the existence of first-level and second-level procedures
  • include other program logic such as error handling, interacting with the end user and modifying the UI

The hierarchy is as follows:

procedure levels

A VBA program might have several first-level procedures called by second-level procedures, and both may be called from third-level procedures.

Examples

Here's an example of a first-level procedure:

Function GetUsername() As String
  GetUsername = Environ("Username")
End Function

This procedure returns the environment variable "Username". Note there is no evaluation or application of logic to the function; it blindly returns the username without regard for what that value may be. This function can be copied and pasted to any VBA project as-is, and it performs only one operation.

Here's an example of a second-level procedure:

Function IsUserNameBlank() As Boolean
  IsUserNameBlank = (Len(GetUsername) = 0)
End Function

This procedure calls a first-level procedure, then proceeds to evaluate the result of that procedure according to whatever criteria the programmer has set. In this case, whether the environment variable for username has been set. It only performs one evaluation and returns the result of that evaluation to the calling procedure. It satisfies the criteria for a second-level procedure.

You might write the function this way if you wanted to 'hide' GetUsername from the outside world (i.e. make it private) and only call it via this function.

You may want to write it differently, for example:

Function IsUserNameBlank(username As String) As Boolean
  IsUserNameBlank = (Len(username) = 0)
End Function

This takes whatever username is passed and determines if it is blank. This function would need to be called separately after GetUsername.

We could also combine both functions using the Optional keyword. If we pass a username to the function, we evaluate that username, otherwise we fall back to using the first-level procedure to grab the username.

Function IsUserNameBlank(Optional username As String) As Boolean
  If (Len(username) = 0) Then
    ' check local username
    IsUserNameBlank = (Len(GetUsername) = 0)
  Else
    ' check whatever username was passed
    IsUserNameBlank = (Len(username) = 0)
  End If
End Function

The best part is that we could paste this code over either of the previous two functions in a fully operational VBA project and it would work exactly the same without disturbing the existing code, but any future code could take advantage of the new feature!

Regarding third-level procedures, the code behind userforms is often third-level because it must perform multiple checks or validation procedures while hiding or logging error messages. For example, a Submit button that validates input fields before processing input data and outputting a result. An example of a third-level procedure:

Private Sub SubmitButton_Click()

  On Error GoTo ErrorHandler

  Dim clientId As String
  Dim txtbox As MSForms.TextBox
  Dim colorToSet As Long

  Const BAD_TEXTBOX_BACKCOLOR As Long = -2147483643

  Set txtbox = Me.TextBox1
  clientId = GetValue(txtbox)

  If ((HasValue(clientId)) And (Not IsNumeric(clientId))) Then
    colorToSet = vbRed
  Else
    colorToSet = BAD_TEXTBOX_BACKCOLOR
  End If

  SetBackColor txtbox, colorToSet

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub
Function GetValue(txtbox As MSForms.TextBox) As String
  GetValue = txtbox.value
End Function
Function HasValue(stringtoCheck As String) As Boolean
  HasValue = (Len(stringtoCheck) > 0)
End Function
Sub SetBackColor(txtbox As MSForms.TextBox, bc As Long)
  txtbox.BackColor = bc
End Sub

The code behind this button uses a first-level procedure to return the value of a given textbox, then uses a second-level procedure to request a value judgment about that value. Another first-level procedure is used to change the backcolor of the textbox.

An example of an improperly modularized procedure would be:

Function IsUserNameBlank() As Boolean
  Dim username As String
  
  username = Environ("Username")
  IsUserNameBlank = (Len(username) = 0)
End Function

This looks like it could be either a first-level or second-level procedure, but it does both the retrieval and the evaluation within the same procedure instead of using a first-level procedure to do the retrieval and a separate one to evaluate. By trying to perform the action of both a first-level and second-level procedure it violates the criteria for both. This procedure should be split into two discrete procedures as described above.

Rationale

I see a lot of procedures that simply try to do too much, localizing them for a specific situation. And I see procedures criticized for not doing enough error checking. It aggravates me when a simple function that takes an input and returns an evaluation (a second-level procedure) is criticized on the grounds that it doesn't check for zero-length strings or nulls or some other obscure issue that could possibly throw an error.

This is a pseudo-problem: Not every function needs to account for every possible contingency. If your code can be criticized by someone saying "it doesn't account for X" then you haven't done enough work at the second or third level to show how your code handles input.

I do not classify this as a rant per se, but a suggestion that better modularization would provide a scalable framework for problem solving.

Conclusion

I recognize that for the sake of brevity, it may sometimes be necessary to "procedure-stuff", that is, to put everything into a single procedure. I do this all the time when posting code in public forums, simply because it's easier.

I realize this is well-traveled ground. I may have missed some criteria, or put criteria into the wrong level. Despite any shortcomings I hope this is a good starting point for developing well-formed VBA code for anyone who stumbles across this post.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 1 Comment(s) on A Treatise on Types of VBA Procedures:

  1. What an enjoyable post! I'm always surprised by how much code is repeated between procedures–when one procedure with the same code and an object passed to it–would suffice. As you point out, modularization is an important, if overlooked, consideration when programming in VBA.

This article is closed to any future comments.
learn excel dashboards