In yesterday's post, I wrote about "guard clauses." I used a helper function named SaveFails to demonstrate the concept. Today, I'm going to show you a different function that I commonly use in guard clauses: ControlIsBlank.
Like the SaveFails function, the ControlIsBlank function returns a boolean. If the result is True, that generally indicates that we want to exit our routine.
I regularly use this on unbound forms where I want to require the user to enter a value for a control.
For example, consider a report preview form that prompts the user for a Start and End Date. Let's assume that we're querying an enormous table with years of history. We want to avoid accidentally retrieving every single record, so we're going to force the user to enter a starting and ending date before they can preview the report.
How it works
By default, the ControlIsBlank function performs the following tasks:
- Checks to see if the value of the passed control is "blank"
- Warns the user if the control is blank
- Sets focus to the control with the missing information
- Forces a list dropdown if the control is a ComboBox
Sometimes a control with no value will return Null and sometimes it will return an empty string. I think that's a big part of the reason why I wrote this function in the first place. Honestly, though, it's been so long since I've used anything but this function to check for an empty control, I don't remember what the different circumstances are that lead to the control's value being a Null versus an empty string.
The function will use the caption from an "attached label" (if there is one) to build the message it shows to the user. Alternatively, you can pass it a description of the missing value in the CtlDesc argument (e.g., if there is no attached label) or the full text of the message you want to display to the user in the ErrMsg argument.
As long as the target control is both visible and enabled, the function will set the focus back to that control. And, if that control is a ComboBox, then the function forces the ComboBox to drop down. I have it force the dropdown for two reasons. First, it makes it visually obvious to the user where the problem lies. Second, it saves the user from having to hit the relatively small mouse target on the right side of the dropdown control.
The importance of library functions
As you can see, there's quite a bit of functionality packed into a tiny package. And by calling the function from within a guard clause, there's zero readability cost. We're not introducing any extra nesting, and the code needs no comments as it is self-documenting.
By investing the time up front to develop this function, I can have a much greater impact with my ongoing development. Imagine writing this sort of functionality ad hoc everywhere I needed it. Actually, why imagine it when I can show you what that would look like? Obviously, we wouldn't need most of the functionality built into the generalized function, but it would still make our button click code much less readable:
Private Sub btnPreviewRpt_Click() If Nz(Me.tbStartDate, "") = "" Then MsgBox "Please enter a Start Date" Exit Sub End If If Nz(Me.tbEndDate, "") = "" Then MsgBox "Please enter an End Date" Exit Sub End If Dim Criteria As String Criteria = "RptDate Between " & Dt(Me.tbStartDate) & _ " And " & Dt(Me.tbEndDate) DoCmd.OpenReport "MyReport", acViewPreview, , Criteria End Sub
The above code is easy to write. But it definitely took longer than the code from the top of this article. What's more, it's less functional. We certainly could add the lines to SetFocus to the appropriate controls, but would we? And would we do it consistently?
Taking Making the easy way out
One of the keys to being a good developer is to make the easy way also be the right way. Very often that means investing time up front to build a highly capable function that can also be called easily.
Don't underestimate the value of sensible default values. It's nice to have the flexibility to override the defaults in my ControlIsBlank function, but I would not use it nearly as much if I had to pass values for those other arguments on every call.
Along those same lines, try to assign short names to functions you will call a lot. Good examples of this are the Dt() and Qt() functions I linked to earlier. Functions that are called infrequently should have longer, more explicit names. But for functions that get called repeatedly, excessively verbose names contribute more Noise than Signal.
This particular example violates that principal (which merely proves Ralph Waldo Emerson's point that "a foolish consistency is the hobgoblin of little minds"). I prefer the added function name length because it makes the guard clauses in which it appears read like English sentences.
More AutoHotkey goodness
Nevertheless, it's more typing than I prefer for such a common function. To further enable my laziness, I created an AutoHotkey text expansion to save me some keystrokes. Now when I type "cib" in the VBA IDE, AutoHotkey transforms it into "If ControlIsBlank(Me".
;VBA IDE #IfWinActive ahk_class wndclass_desked_gsk ::cib::If ControlIsBlank(Me
I'm closing in on a thousand words in this article, so I should probably just get to the code already:
'--------------------------------------------------------------------------------------- ' Procedure : ControlIsBlank ' DateTime : 9/16/2011 - 4/20/2015 12:59 ' Author : Mike@nolongerset.com ' Purpose : Returns whether a control on a form is blank/null. It also moves the focus ' to that field and displays a message to the user (optional). ' Version 2 : 1/8/2008 10:08 ' If no Control Description is passed the function attempts to use the ' caption of the attached label. If that fails, it uses the name of the ' control itself. ' Version 3 : 12/31/2008 ' If the control is a combo box, then the combo box drops down after ' receiving the focus. ' Version 4 : 9/15/2014 ' Allow passing an optional full error message to display. '--------------------------------------------------------------------------------------- ' Function ControlIsBlank(Ctl As Control, Optional ShowMsg As Boolean = True, _ Optional CtlDesc As String = "Attached_Label", _ Optional ErrMsg As String = vbNullString) As Boolean Dim Description As String ControlIsBlank = False If CtlDesc = "Attached_Label" Then On Error Resume Next Description = Ctl.Controls.Item(0).Caption If Err.Number <> 0 Then Description = Ctl.Name Err.Clear End If On Error GoTo 0 'Use temporary placeholder (§) to handle escaped (eg, &&) and hotkey (eg, &Close) ampersands Description = Replace(Description, "&&", "§") Description = Replace(Description, "&", "") Description = Replace(Description, "§", "&") Else Description = CtlDesc End If If Nz(Ctl, "") = "" Then ControlIsBlank = True If ShowMsg Then Dim Msg As String If Len(ErrMsg) > 0 Then Msg = ErrMsg Else Msg = "Please enter a value for " & Description End If MsgBox Msg, vbExclamation, "Missing Data" End If If Ctl.Visible And Ctl.Enabled Then Ctl.SetFocus If Ctl.ControlType = acComboBox Then Ctl.Dropdown End If End If End If End Function
P.S. As a reminder, the reason you never see the kind of traditional error handling code you're used to seeing in VBA examples is because I use a global error handler in conjunction with vbWatchdog. Read all about that here: Error Handling Evolution.