Create Standalone Functions to Return User Input Values in Microsoft Access

In a previous article, I introduced the GetInfoForm() function, which can be used to prompt a user for data using a custom dialog form and retrieve the user-entered values directly from the form–without having to temporarily stash them on a hidden global form:

I concluded that article with the following sample code:

Sub TestGetDeedInfo()
    Dim Frm As Form_GetDeedInfo
    Set Frm = GetInfoForm("GetDeedInfo")
    If Frm Is Nothing Then Exit Sub
    
    Dim RecBook As String
    RecBook = Frm.tbRecordBook.Value
    
    Dim RecPage As String
    RecPage = Frm.tbRecordPage
    
    CloseForm Frm.Name
    
    Debug.Print RecBook, RecPage
End Sub

While this is an improvement over using a hidden global form to pass values around, we still end up mixing user interaction code with business logic.  To reduce complexity and make our code easier to debug and test, we should strive to keep as much business logic as we can isolated within pure functions.

Thus, we need to extract the user interaction code into a separate procedure.

"Extract Method"

Modern development environments (read: pretty much any besides VBA) include an "extract method" refactoring tool.

The idea is simple.  Select a portion of a long routine and extract it out into a standalone method (e.g., a Function or Sub).

This type of refactoring has many benefits:

• Condenses many lines of code into a single chunk of information which occupies less room in our brain (which is important).
• Makes it easier to replace the call to the user dialog with hard-coded values during testing (whether that be automated or ad hoc testing).
• Creates a reusable piece of code that can be called from multiple places in the application (i.e., Don't Repeat Yourself).
• The result of the function can be passed around as an argument to other procedures.
• It supports the "Functional Core, Imperative Shell" approach to software design.

While the VBA IDE may lack an "Extract Method" tool, the underlying concept is just as important in VBA as it is in other programming languages.

Let's rewrite our TestGetDeedInfo() function by extracting a method from the inline code.

GetDeedInfoFromUser() Function

Since the GetDeedInfo form returns two pieces of information (RecordBook and RecordPage), we can't return a simple value from our function.

Instead, we declare a custom type in VBA:

Type typDeedInfo
    UserCanceled As Boolean
    RecordBook As String
    RecordPage As String
End Type

We can then use the above custom type as our return type in our extracted function, GetDeedInfoFromUser():

Function GetDeedInfoFromUser() As typDeedInfo
    Dim Frm As Form_GetDeedInfo
    Set Frm = GetInfoForm("GetDeedInfo")
    
    If Frm Is Nothing Then
        GetDeedInfoFromUser.UserCanceled = True
        Exit Function
    End If
    
    With GetDeedInfoFromUser
        .RecordBook = Frm.tbRecordBook.Value
        .RecordPage = Frm.tbRecordPage.Value
    End With
    CloseForm Frm.Name
End Function

We can now rewrite our test function as follows:

Sub TestGetDeedInfo()
    Dim DeedInfo As typDeedInfo
    DeedInfo = GetDeedInfoFromUser()
    If DeedInfo.UserCanceled Then Exit Sub
   
    Debug.Print DeedInfo.RecordBook, DeedInfo.RecordPage
End Sub