VBA has several Is functions that return Boolean values indicating whether a certain condition holds. We have already discussed the IsMissing function in connection with optional arguments. Here are some additional Is functions.

Dim x As String
x = "1/1/45"
Debug.Print IsDate(x)

Dim x As Variant
If IsEmpty(x) Then . . .

If var = Null Then

If IsNull(var) Then

Dim rs As Recordset
Dim s As String
Set rs = CurrentDb.OpenRecordset("Names")
rs.MoveFirst
If Not IsNull(rs!LastName) Then
	s = rs!LastName
	. . .
End If

Dim s As String
s = "123"
If IsNumeric(s) Then Debug.Print "Number"

s = "123 Main St"

The Immediate If function has the syntax:

IIf(Expression, TruePart, FalsePart)

If Expression is True, then the function returns TruePart. If Expression is False, the function returns FalsePart. For instance, consider the following code:

Dim rs As Recordset
Dim s As String

Set rs = CurrentDb.OpenRecordset("Names")
rs.MoveFirst

If Not IsNull(rs!LastName) Then
	s = rs!LastName)
End If

This code fills a string variable with a field value. We must make a distinction between a Null and non-Null field value because the code:

s = rs!Lastname

will produce the error “Invalid use of Null” if we try to assign a Null value to a string variable.

It is very important to note that the Immediate If function always evaluates both TruePart and FalsePart, even though it returns only one of them. Hence, we must be careful about undesirable side effects. For example, the following code will produce a “Division by Zero” error because even though the IIf function returns 1/x only when x is not equal to 0, the expression 1/x is evaluated in all cases, including when x = 0:

x = 0
y = IIf(x = 0, x ^ 2, 1 / x)

The syntax of the Switch function is:

Switch(expr1, value1, expr2, value2, ... , exprn, valuen)

where exprn and valuen are expressions. Note that there need only be one expression-value pair, but the function is more meaningful if there are at least two such pairs.

The Switch function evaluates each expression exprn. When it encounters the first True expression, it returns the corresponding value. As with the IIf function, Switch always evaluates all of the expressions. If none of the expressions is True, the function returns Null. This can be tested with the IsNull function.

The procedure in Example 12-1 displays the type of file based on its extension: Access database, text, or dbase database.

Sub ShowFileType(FileExt As String)

Dim FileType As Variant

FileType = Switch(FileExt = "mdb", "Database", _
                  FileExt = "txt", "Text", _
                  FileExt = "dbf", "dBase")

' Display result
If Not IsNull(FileType) Then
   MsgBox FileType
Else
   MsgBox "Unrecognized type"
End If

End Sub

There is one subtlety in this code. Since the Switch function can return a Null value, we cannot assign the return value to a String variable, as we might first try to do:

Dim FileType As String

FileType = Switch(FileExt = "mdb", "Database", _
                  FileExt = "txt", "Text", _
                  FileExt = "dbf", "dBase")

This will produce an error if FileExt is not "mdb", "txt", or "dbf“, in which case we will get the very annoying error message, “Invalid use of Null.” The solution is to declare FileType as a Variant, which can hold any data type, including no data type, which is indicated by the Null keyword. (This issue can be avoided by using a Select Case statement, discussed in Chapter 13.)

This simple statement, whose syntax is:

Beep

sounds a single tone through the computer’s speakers. It can be useful (when used with restraint) if we want to get the user’s attention. However, there is a caveat: the results are dependent upon the computer’s hardware, and so the statement may not produce a sound at all! Thus, if you use this statement in your code, be sure to warn the user.

The On Error statement tells VBA what to do when a runtime error occurs. The most common form of the statement is:

On Error GoTolabel

where label is a label. For instance, consider the following code:

Sub RecordCt(  )

On Error GoTo ERR_EXAMPLE

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Name")

MsgBox rs.RecordCount

Exit Sub

ERR_EXAMPLE:
  MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical
  Exit Sub
    
End Sub

The purpose of this procedure is simply to display the number of rows in a table. However, the database does not happen to have a table called Name. Hence, when VBA encounters the line:

Set rs = CurrentDb.OpenRecordset("Name")

a runtime error will occur.

To deal with this possibility in a friendly manner, we add some error checking. The line:

On Error GoTo ERR_EXAMPLE

tells VBA to move execution to the label ERR_EXAMPLE if an error does occur. The code following this label is called the error-handling code. If an error should occur, the next line executed is the MsgBox line, in which case the dialog box in Figure 12-3 will be displayed. This message gives a description of the error, obtained from the error object, which we discuss in the next section.

Figure 12-3. An error dialog box

It is important to note the:

Exit Sub

line just before the ERR_EXAMPLE label. Without this statement, the error-handling code will always be executed, even when there is no error! Omitting this line is a common mistake. Note also that labels always end with a colon.

The process of adding error-handling code to a procedure is sometimes referred to as error-trapping .

Consider the following version of the RecordCt function:

Function RecordCt(TableName As String) As Integer

On Error GoTo ERR_EXAMPLE

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset(TableName)
RecordCt = rs.RecordCount
rs.Close

Exit Function

ERR_EXAMPLE:
  RecordCt = -1  ' Indicates error
  rs.Close
  
Exit Function
    
End Function

In this case, if there is an error, the function will simply return the value -1, rather than displaying a message box. This behavior is better than that of the previous version, because in this case the calling procedure can decide what to do.

Here is a procedure that calls RecordCt:

Sub Main(  )

On Error GoTo Err_Main

Dim rc As Long
rc = RecordCt("Object")

If rc = -1 Then
   ' code here to handle error
Else
   ' code here for no error
End If
Exit Sub

Err_Main:
  MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical
  Exit Sub

End Sub

Note that a return value of -1 is not perceived by VBA as an error at all, so we need to handle the error using code such as:

If rc = -1 Then

What happens if we do not trap errors in a procedure?

If the procedure was not called by another procedure, but rather was called directly by the user, or if the procedure is an event procedure—that is, code that executes in response to a user manipulating a control on a form (for instance, clicking on a command button)—then VBA just displays an error message and halts the program.

However, if the procedure in which the error occurred was called by another procedure, then VBA passes the error to the calling procedure, just as though the calling procedure had caused the error.

To illustrate this, consider the following procedures:

Function RecordCt2(TableName As String) As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(TableName)
RecordCt2 = rs.RecordCount
rs.Close
End Function

' -----

Sub Main2(  )

On Error GoTo Err_Main

Dim rc As Long
rc = RecordCt2("Objects")

' More code here

Exit Sub

Err_Main:
  MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical
  Exit Sub

End Sub

The RecordCt2 function has no error-trapping code. If Main2 calls RecordCt2 with a bad table name, the error in RecordCt2 will be passed to Main2, whose error-trapping code will execute. Thus, we will get an error message from Main2. (This may be just fine.)

More generally, if ProcedureA calls ProcedureB, which calls ProcedureC, and so on, then an error in any one procedure will be passed up the call stack (list of procedures in reverse order of execution) until a procedure with error-handling code is encountered. If none is encountered, then VBA will issue its own error message and terminate the program.

Incidentally, you can view the call stack while in break mode by choosing Call Stack from the View menu.

The error object, denoted by Err, belongs to the VBA object model. The most important properties of this object are:

Thus, for instance, the line:

MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical

displays the error number and its description.

The Err object has a Clear method:

Err.Clear

that will clear all of the properties of the Err object, setting its Number property to 0 (which indicates the absence of an error).

The statement:

On Error GoTo 0

turns off any previous On Error GoTo label statements. Any error occurring subsequently will be handled by VBA in its own inimitable way.

The syntax:

On Error Resume Next

tells VBA to continue executing the code immediately following the line that caused the error. There are two important uses for this form of On Error. The first is to cause VBA to ignore an error. For instance, the code:

Sub example(  )

On Error Resume Next
MsgBox rs.RecordCount

End Sub

will report the record count when rs is a valid recordset and do nothing otherwise.

Another important use for the On Error Resume Next syntax is for in-line error checking , where we check for errors immediately following the line that may have caused an error. For instance, another way to handle errors in the RecordCount property is as follows:

Sub example(  )

On Error Resume Next

MsgBox rs.RecordCount

If Err.Number <> 0 Then
  ' code to handle error here
End If
End Sub

It is also possible to include the Resume statement in the error-handling portion of the code. This will cause VBA to resume execution at the line that follows the one that caused the error. Thus, the previous code is equivalent to the following:

Sub example(  )

On Error GoTo ERR_EXAMPLE
MsgBox rs.RecordCount

' An error will cause execution to resume here after 
' displaying an error message

Exit Sub
ERR_EXAMPLE:
  MsgBox Err.Description, vbCritical
  Resume Next
    
End Sub

There are three variations on the Resume statement:

The first version will cause VBA to resume with the line that caused the error. This is useful if your error-handling code actually repairs the error condition and you want the line that caused the original error to be executed again.

To illustrate, if the procedure in Example 12-2 encounters an error, it branches to an error handler. This handler checks for error number 3078, which is the “Can’t find table” error. If this is the error, then the procedure displays a dialog box asking for a new table name. If the user enters a new name, the Resume statement is executed, and so the line:

Set rs = CurrentDb.OpenRecordset(TableName)

is repeated. Note that it is vital to give the user a way out, however. This is done by letting the user leave the dialog box blank. (Incidentally, I got the correct error number 3078 by simulating the error and reading the resulting error-message dialog box.)

Function RecordCt3(TableName As String) As Integer

On Error GoTo ERR_EXAMPLE

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset(TableName)

RecordCt = rs.RecordCount

rs.Close
Exit Function

ERR_EXAMPLE:
   If Err.Number = 3078 Then
      ' Can't find table
      sTable = InputBox("Can't find table " & sTable & _
               ". Please enter table name again or leave blank to end.")
      If sTable = "" Then
         rs.Close
         TableName = sTable
         Exit Function
      Else
         Resume
      End If
   Else
      ' Unknown error
      MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical
      rs.Close
      Exit Function
   End If
    
End Function

The third variation:

Resume ALabel

causes VBA to resume execution at the line labeled ALabel .