Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Creating and Running a Test Harness

As discussed in the Functional Decomposition section of Chapter 3 Excel and VBA Development Best Practices, you should strive to break your application into as many single-purpose, reusable procedures as possible. After you've done this, however, you need to verify that these procedures work under all circumstances. Testing them manually within your application is tedious, time-consuming and not very thorough.

The proper way to test a procedure is to write a wrapper procedure that calls the procedure to be tested in a loop, passing it all possible combinations of arguments and verifying the results to ensure that they are correct. This wrapper procedure is called a test harness and we show you how to build one in this section. The workbook containing the procedures we're about to demonstrate is called TestHarnessDemo.xls and is located on the CD in the \Concepts\Ch16VBA Debugging folder.

One frequently useful single-purpose procedure takes a full path and filename string and breaks it into its path and filename components, optionally returning one or both to the calling procedure. The ReturnPathAndFilename procedure shown in Listing 16-6 does this.

NOTE

Because of its use of the VBA InStrRev function, which was first introduced in Excel 2000, the ReturnPathAndFilename procedure will not work in Excel 97.

Listing 16-6. The ReturnPathAndFilename Procedure

Public Sub ReturnPathAndFilename(ByRef sFullName As String, _
Optional ByRef sPath As String, _
Optional ByRef sFile As String)
Dim lPosition As Long
lPosition = InStrRev(sFullName, "\")
sPath = Left$(sFullName, lPosition)
sFile = Mid$(sFullName, lPosition + 1)
End Sub

To verify that this procedure works as expected we need to create a test harness that feeds it large numbers of full path and filename strings and then concatenates the returned split path and filename values and verifies that the specified file exists. If the split is performed incorrectly on any test string, we know there is a bug in the procedure that needs to be fixed. The test harness that performs this operation is shown in Listing 16-7.

Listing 16-7. The Test Harness for the ReturnPathAndFilename Procedure

Public Sub TestHarness()
Dim bSuccess As Boolean
Dim objSearch As FileSearch
Dim lCount As Long
Dim sFullName As String
Dim sPath As String
Dim sFilename As String
' Use the FileSearch object to get a large number of full
' path and filename strings to use as test data.
Application.StatusBar = "Retrieving test data..."
Set objSearch = Application.FileSearch
objSearch.NewSearch
objSearch.LookIn = "E:\"
objSearch.SearchSubFolders = True
objSearch.FileType = msoFileTypeExcelWorkbooks
If objSearch.Execute() > 0 Then
' Assume the test succeeded until something goes wrong.
bSuccess = True
' Run each path and filename returned by the FileSearch
' object through our ReturnPathAndFilename.
' Then concatenate the results and verify that they
' refer to a valid file.
For lCount = 1 To objSearch.FoundFiles.Count
sFullName = objSearch.FoundFiles(lCount)
Application.StatusBar = "Checking: " & sFullName
ReturnPathAndFilename sFullName, sPath, sFilename
If Len(Dir$(sPath & sFilename)) = 0 Then
' Combining the path and filename returned by
' the ReturnPathAndFilename procedure resulted
' in an invalid file specification.
Debug.Print "Bad split: ", sPath, sFilename
bSuccess = False
End If
Next lCount
Application.StatusBar = False
If bSuccess Then
MsgBox "All tests succeeded."
Else
MsgBox "Failures encountered. " & _
"See list in the Immediate window."
End If
Else
MsgBox "No matching files found."
End If
End Sub

In the TestHarness procedure we use the FileSearch object to create a large array of full path and filename strings. We loop this array and feed each of these strings to the ReturnPathAndFilename procedure. We then concatenate the path and filename returned by the procedure and verify that it refers to a valid file. If any full path and filename strings are split incorrectly, we print the results to the Immediate window and display an error message upon completion. Otherwise we display a success message.

Using this technique, you can run thousands of full path and filename strings through the function and verify that it handles them correctly in a very short period of time. Verifying as many procedures as possible using the test harness approach should be considered a best programming practice.