ArraySearch for VBA: Part 2

Last time, we implemented a simple VBA function to search an array for a value, returning a boolean whether it was found or not, and the index where it was found in an optional parameter.

Public Function ArraySearch(a As Variant, x As Variant, _
                            Optional ByRef found_index As Variant) As Boolean
    Dim i As Long
    Dim result As Boolean
 
    result = False
    For i = LBound(a) To UBound(a)
        If a(i) = x Then
            result = True
            found_index = i
            Exit For
        End If
    Next
 
    ArraySearch = result
End Function

Unfortunately, this implementation failed two of our original unit tests.

Private Sub TestArraySearchSpecial()
    Dim avar As Variant
      
    ' ut6 searching non-array
    AssertFalse ArraySearch("two", 2)
     
    ' ut7 searching for array within array
    avar = Array(1, Array(2, 3), 4, 5)
    Assert ArraySearch(avar, Array(2, 3))
End Sub

Ut6 exposes a simple bug. We have to add an early test that the parameter a is really an array before attempting to index it.

' Add this code to the beginning of ArraySearch.
If Not IsArray(a) Then Err.Raise 13, , "Parameter a is not an array."

Ut7 is more complicated. The VBA equality operator = does not support comparing two arrays. In order to compare two arrays for equality, the array elements would need to be compared, one at a time. Such a comparison would also need to be recursive, since there's nothing stopping the arrays from containing sub-array elements themselves.

We have to step back and evaluate whether the benefit outweighs the complexity. The goal is to write a generalized array search algorithm, and if the function is to be as general as possible, supporting searching for a sub-array seems like it's something that we would want to do. On the other hand, in my experience, this is not a scenario that comes up very often. I rarely get to the point where I have a data structure complicated enough to involve arrays within arrays. Before things have gotten to that level of complexity, I either have moved the data into a SQL database, or have developed a properly encapsulated data model using user-defined types and classes. Arrays in VBA are best used to store a homogeneous list of data or (in a pinch) to define a small, limited-use data structure, similar to a tuple in Python. Implementing a complex data model using arrays, sub-arrays, and perhaps a Dictionary or two will quickly lead you to an unmaintainable mess of spaghetti code.

Using that reasoning, I've made the decision that I will not support searching for sub-arrays. We still should check for sub-arrays in the input so that this edge case is properly accounted for. This test can be combined with the earlier check.

' Add this code to the beginning of ArraySearch.
If Not IsArray(a) Then
    Err.Raise 13, , "Parameter a is not an array."
ElseIf IsArray(x) Then
    Err.Raise 13, , "Searching for a sub-array is not supported."
End If

While we're at it, let's update the unit tests to accommodate our revised design.

Private Sub TestArraySearchSpecial()
    Dim avar As Variant
    Dim result As Boolean
      
    ' ut6 searching non-array
    On Error Resume Next
    result = ArraySearch("two", 2)
    AssertEqual Err.Number, 13
    Err.Clear
    On Error GoTo 0
     
    ' ut7 searching for array within array
    avar = Array(1, Array(2, 3), 4, 5)
    On Error Resume Next
    result = ArraySearch(avar, Array(2, 3))
    AssertEqual Err.Number, 13
    Err.Clear
    On Error GoTo 0
End Sub

We're not done yet, though. ArraySearch still has problems. A final form will be presented in tomorrow's post, but before that, here are some more failing unit tests. As an exercise, see if you can guess what causes these tests to fail and how the code will need to be updated to deal with them.

Private Sub TestArraySearchTypes()
    ' ut8 Search for vbObject
    Assert ArraySearch(Array(1, 2, 3, ThisWorkbook), ThisWorkbook)

    ' ut9 Searches involving Empty, Null, and vbObjects
    Assert ArraySearch(Array(1, 2, Empty, Sheets(1), Null, 4), 4)
    AssertFalse ArraySearch(Array(1, 2, Empty, vbNullString), Null)
    
    ' ut10 ParamArray searches
    TestArraySearchParamArray 1, "a", Null, Sheets(1), "arg1"
End Sub

Private Sub TestArraySearchParamArray(ParamArray params() As Variant)
    Assert ArraySearch(params, "arg1")
    AssertFalse ArraySearch(params, vbNullString)
End Sub

Private Sub TestArraySearchCollection()
    Dim c As New Collection

    ' ut11 Support for Collections? Why not?
    c.Add(1)
    c.Add("b")
    c.Add(Null)
    c.Add("cdef")
    Assert ArraySearch(c, "cdef")
    Assert ArraySearch(c, 1)
    AssertFalse ArraySearch(c, Empty)