A robust implementation of ArraySearch for VBA: Part 1

VBA doesn't include a built-in function for searching an Array. Because of this, after doing work in VBA for a while, you'll probably have written several loops like this.

Dim i As Long
Dim found As Boolean

For i = 0 To UBound(a)
    If a(i) = v Then
        found = True
        Exit For
    End If
Next

If found Then
    Debug.Print "Value was found at index " & i
Else
    Debug.Print "Value is missing"
End If

You might think that this is quite a bit of code for what should be a simple operation. A huge element of good programming style is to eliminate repeated logic and abstract it into separate procedures. VBA, with its out of date, weirdly designed standard library, provides us with several opportunities to practice this aspect of software design.

I think that most VBA programmers should put together their own "utility" module that they include in any non-trivial projects to eliminate the standard, boilerplate code that they find themselves writing often. As you might have guessed by now, this post is going to focus on writing a standard ArraySearch function that would fit perfectly in such a utility module.

First attempt at ArraySearch

This is our first attempt. We're going to try just converting the code in the snippet above into a Function.

' Search the array [a] for value [x], returning whether x was found.
' If [found_index] is provided and [x] is found, [found_index] receives the
' index where [x] was found.
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

I changed the indexing from the prior version to go from LBound to UBound in case someone passes in an array with a base other than zero, which is possible when declaring an array or by using the Option Base directive. Other than that, it's pretty similar.

As an exercise, try reading this function carefully and make a prediction of what kinds of input it will fail to process.

Let's do some unit testing, with a focus on VBA's many different possible kinds of arrays.

Private Sub TestArraySearchFixed()
    Dim afixed(4) As String
    Dim found As Long
    
    afixed(0) = "alpha"
    afixed(1) = "bravo"
    afixed(2) = "1"
    afixed(3) = "two"
    afixed(4) = "3"
    
    ' ut1 successful/unsuccessful searches on fixed array
    ' unsuccessful search should preserve found_index
    Assert ArraySearch(afixed, "two", found)
    AssertEqual found, 3&
    AssertFalse ArraySearch(afixed, 2, found)
    AssertEqual found, 3&
End Sub

Private Sub TestArraySearchDynamic()
    Dim adynamic() As Long

    ReDim adynamic(3)
    adynamic(0) = 78503
    adynamic(1) = 65075
    adynamic(2) = 21738
    adynamic(3) = 43044
    
    ' ut2 unsuccessful/sucessful search on dynamic array
    AssertFalse ArraySearch(adynamic, 44444)
    Assert ArraySearch(adynamic, 78503)
End Sub

Private Sub TestArraySearchSpecial()
    Dim abase(1 To 2) As Variant
    Dim asingleton(0) As Integer
    Dim aempty As Variant
    Dim avar As Variant

    abase(1) = "abc"
    abase(2) = 123&
    
    asingleton(0) = -1
    
    aempty = Array()
    
    ' ut3 search on a singleton array (found and not found)
    Assert ArraySearch(asingleton, -1)
    AssertFalse ArraySearch(asingleton, -2)
    
    ' ut4 search on empty array
    AssertFalse ArraySearch(aempty, vbNullString)
    
    ' ut5 search on homogeneous variant array (found and not found)
    avar = Array(1, 2, 3, 4)
    Assert ArraySearch2(avar, 3)
    AssertFalse ArraySearch(avar, 0)
    
    ' 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

Unit tests 1 through 6 pass okay, but we run into problems on the last two.

Why did the tests ut6 and ut7 fail? How are we going to fix the function so that the tests pass? Could there be other problems with our ArraySearch function that we haven't even tested for yet? Find on Monday, in the next blog post!