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.
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.
Since the GetDeedInfo form returns two pieces of information (
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