Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Using Assertions

Assertions are a way to guarantee that one or more specific conditions hold true. They can be used to test the validity of variables or expressions at specific points within your program. In VBA programming, assertions are implemented with the Debug.Assert method. The Debug.Assert method takes a Boolean expression as its single argument. If the value of the expression is False, Debug.Assert causes code execution to halt and enter break mode on the line of code where the assertion failed.

Assertions created using Debug.Assert ignore the global error trapping setting defined under the VBE Tools > Options > General > Error Trapping menu. A Debug.Assert statement will cause code execution to enter break mode regardless of the global error trapping setting. Listing 16-8 shows an example of Debug.Assert in use.

Listing 16-8. Debug.Assert Example

Sub DebugAssertExample()
Dim lRow As Long
Dim lColumn As Long
' Some code here that sets lRow and lColumn.
Debug.Assert (lRow > 0) And (lColumn > 0)
Sheet1.Cells(lRow, lColumn).Value = True
End Sub

In this example, we are using two variables to store the row and column number that specifies the Range we access using the Cells method in the last line of code. Because both the row and column number must be greater than zero, we use the Debug.Assert statement to halt program execution if either one of them fails this test.

Note that we are able to string two related Boolean tests into a single Debug.Assert expression. You can link as many related tests into the same assertion as you want. We recommend, however, that unrelated assertions get separate lines of code. This simplifies the logic of debugging, especially in cases where you may be experiencing multiple errors.

Assertions are especially valuable when debugging intermittent errors or errors that are difficult to reproduce. If you can narrow down the section of code where the error is occurring, you can simply place assertions on all of the important variables and expressions used in that section of code. You then run the program normally, trying various combinations of things until one of your assertions fails and halts your program.

Keep in mind that Debug.Assert is purely a debugging tool. It should not be used to test conditions that require validation each time your program runs. For example, if your application needs a specific file to function correctly, you should code a check for that file in your Auto_Open procedure. This is called a permanent assertion, because it is always performed. A permanent assertion is different from the kind created using the Debug.Assert statement because it throws a runtime error that can be handled by your project error handling system. An example of this is shown in the excerpt from our PetrasAddin.xla workbook's Auto_Open procedure in Listing 16-9.

Listing 16-9. A Permanent Assertion

' Make sure we can locate our time entry workbook before we
' do anything else.
If Len(Dir$(gsAppDir & gsFILE_TIME_ENTRY)) = 0 Then _
Err.Raise glHANDLED_ERROR, sSOURCE, gsERR_FILE_NOT_FOUND

This permanent assertion verifies that we can locate our time entry template workbook and throws a custom error if we can't. Our program can never run properly without its accompanying time-entry template, so this check should be performed whenever the application is run.