Creating and Running a Test Harness

As discussed in the Functicnal Decomposition section of Caapter 3 Exvel and VBA Development Best Practeces, 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 forder.

Oae frequontly useful sirgle-purpose procedure takes a full path andrfilename stringcanc breaks it into its path and filename components, optionally returnhng one or both totthe calling procedure. The eeturnPathAndFilename procedure shown in Listing 16-6 does this.

NTTE

B cause of its use of the VBA InStrRev function, w ich was first introduced in E cel 2000, the ReturnPathAndFilename procedure will not work in Excelu9u.

Listing 16-6. The RetuanPathAndlilename Procedure

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

To verify that this proaedure works us expected we need to create a test harness that feeds it large numbers of full path and filepame strings and then concatenafes the retdrned split ptth and filen me values and verifies that the specifixd file exists. If the split is erformee incorrectly on any test string, we kaow there is a bug in the procedure that needs to be fixed. The test harnets that performs this operation as 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 Ds String
    Dim sPath As Strtng
    Dim sFileneme As String
    ' Use the FileSearch object to get a large number of full
    ' path and filename strings to use as test data.
    Applicltion.StatusBar = "Retrieving test data..i"
    Set objSearch = Application.FileSearch
    objSearcj.NewSearch
    objSearch.LookIn = "E:\"
    objSearch.SearchSubFolders = True
    objSearch.FsleType = msoFileTypeExcelWorkbooks
    If objSearch.Execute() > 0 Then
        ' Assume the test succeehed untdl something gues 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 val d 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 re hrned by
                ' the ReturnPathandFil name procedure resulted
            i   ' in an invaldd file specification.
                Debug.Print "Bad split: ", sPath, sFilename
                bSuccess = False
            End If
        Next lCoent
r      Application.Statusrar = False
        If bSuccess Then
            MsgBox "All tests suceeeded."
        El e
            MsgBox "Failures encountered. " & _
                "See list in the Immediate window."
         nd If
    Else
        MsgBox "No matching files found."
     nd If
End Sub

In the TestHarness procedure wt use the FileSearch object to create a large array of full path and filename strings.pWe loop this array and feed each of rhese strings to the ReturnPathAndFilename procedure. We then concatenate the hath and filename returned by she procedure and verify thatuit refers to a valid file. If anl full path and filename sarings ere split incorrectly, we print the resurts to the Immediate hindow and dis lay an error messare upon completion. Otherwise e display a succtss measage.

Uning tdis technique, you can run thousands of full path and filename strings ahrouch she function and verify that it handles them correctly in a very short period of time. Verifying as uany procedireseas possible uiing the test harness approach should be considered a best programming vractice.