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)

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!

Converting Variant types in Excel VBA

As part of an Excel project, I needed to write some code that converted Variants in a reliable way.

The project involved calling a stored procedure that took parameters of many different types which were provided via cells in a spreadsheet. An Excel spreadsheet cell can contain values in a variety of different data types. When you access a cell value with VBA code, it comes to wrapped in a Variant type. Essentially, I needed to be able to convert data from a very forgiving type system (spreadsheet cells) to a very strictly typed system (a stored procedure parameter in a SQL Server database). And since the data was being supplied by non-technical end-users, I had to be sure that unusual Variant types didn't cause issues with data integrity or summon the dreaded VBA error dialog box. For example, the Variant type vbError, which is used by error cell values such as #NA or #NAME, is infamous for causing problems with VBA macros. You'll see why this is in a future post when I discuss Variant comparisons.

To solve the problem, my approach was to write a function that takes a Variant type (that could be any sub-type) and returns a Variant of the desired sub-type, converting it if possible, choosing a logical substitution value if not, and only raising an error when there is no other option.

In preparation, I wrote some code to build a Variant type conversion chart, which shows whether a subtype can be converted to any other subtype, and any errors that may be generated when the conversion is attempted.

Most of the results in this chart are obvious. It's to be expected that just about every type can be converted to a string or that numbers will sometimes overflow when converting to narrower numeric types. Some results are surprising, such as the inability to convert a vbError to a vbDate or a vbDecimal, even though it can be converted to just about every other type.

The conversions for vbEmpty and vbNull are instructive. The Empty value in VBA represents the default value, so it makes sense that it can be converted into anything else. When you convert Empty to a subtype, it turns into the default value for that subtype: for example, an empty string or the integer 0. The Null value, on the other hand, is intended by VB's designers to be an "intentionally missing" value, and so it's a mistake to coerce it into a data containing type. Thankfully, a spreadsheet cell cannot contain a null value. Null values behave similarly as in SQL in comparisons, and Null is considered Falsey in a VBA If statement.

Here is the variant conversion function that I ended up writing. Remember, the intention was to convert a variant value from any subtype to a defined variant subtype, and to be as forgiving as possible in the process.

Const CustomError = 2742

' Convert Variant [x] to subtype [rtype], returning [if_null] if conversion is
' impossible, otherwise returns a converted Variant value.
' Additional parameters are values that should be treated as null.
Public Function VConvert(rtype As VbVarType, _
                         x As Variant, _
                         if_null As Variant, _
                         ParamArray nullable_values() As Variant) As Variant
    Dim result As Variant

    ' Raise an error if provided with an unsupported type.
    Select Case rtype
        Case vbEmpty, vbNull, vbInteger, vbLong, vbSingle, vbDouble, _
            vbCurrency, vbDate, vbString, vbError, vbBoolean, vbVariant, _
            vbDecimal, vbByte
            ' Guard against Case Else if a valid type is provided.

        Case vbObject, vbDataObject, vbLongLong, vbUserDefinedType, vbArray
            Err.Raise CustomError + 1, , "Invalid result type: " & rtype

        Case Else
            Err.Raise CustomError + 1, , "Unknown result type: " & rtype
    End Select
    
    If VarType(x) = vbString Then
        result = Trim(x)
    Else
        result = x
    End If
    
    If ArraySearch(nullable_values, x) Then
        result = if_null
    ElseIf IsEmpty(result) Or IsNull(result) Or result = vbNullString Then
        result = if_null
    Else
        ' Attempt conversion and return if_null if an error is raised.
        On Error GoTo ErrHandler
        Select Case rtype
            Case vbEmpty: result = Empty
            Case vbNull: result = Null
            Case vbInteger: result = CInt(result)
            Case vbLong: result = CLng(result)
            Case vbSingle: result = CSng(result)
            Case vbDouble: result = CDbl(result)
            Case vbCurrency: result = CCur(result)
            
            Case vbDate
                If IsDate(result) Then
                    result = DateValue(CDate(result))
                Else
                    result = if_null
                End If
            
            Case vbString: result = CStr(result)
            Case vbError: result = CVErr(result)
            Case vbBoolean: result = CBool(result)
            Case vbVariant: result = CVar(result)
            Case vbDecimal: result = CDec(result)
            Case vbByte: result = CByte(result)
        End Select
        On Error GoTo 0
    End If
        
    VConvert = result
    Exit Function

ErrHandler:
    VConvert = if_null
End Function

And here are the unit tests for this function.

Private Sub TestVConvert()
    Dim vDate As Variant
    Dim vResult As Variant
    
    vDate = DateSerial(1982, 5, 22) + TimeSerial(5, 30, 0)
    
    ' Test time truncation
    AssertEquals VConvert(vbDate, vDate, Null), #5/22/1982#
    
    ' Test conversion of vbDate to vbString
    AssertEquals VConvert(vbString, vDate, Null), "5/22/1982 5:30:00 AM"
    
    ' Test nullable ParamArray
    AssertIsNull VConvert(vbString, "Hello", Null, "a", "b", "c", "Hello", "d")
    
    ' Test conversion that causes overflow
    AssertEquals VConvert(vbInteger, 999999, -1), -1
    
    ' Test that the overflow Err was cleared
    AssertEquals Err.Number, 0&
    
    ' Test vbLong to vbInteger regular conversion
    AssertEquals VConvert(vbInteger, 100&, -1, 99.9), 100
    
    ' Test string trimming during conversion
    AssertEquals VConvert(vbString, "   abcd  ", Null), "abcd"
    
    ' Test number conversion with untrimmed string
    AssertEquals VConvert(vbDecimal, "   42.25 ", Null), CDec(42.25)
    
    ' Test that conversions retain type of is_null parameter
    AssertType VConvert(vbDate, "Not a date", 42&), "Long"
    
    ' Test that Nulls get converted to is_null
    AssertEquals VConvert(vbInteger, Null, -1), -1
    
    ' Test that Emptys get converted to is_null
    AssertEquals VConvert(vbLong, Empty, 50&), CLng(50)
    
    ' Test that vbNullStrings get converted to is_null
    AssertEquals VConvert(vbString, "", "[empty]"), "[empty]"
    
    ' Test that converting to invalid type raises error
    On Error Resume Next
    vResult = VConvert(vbObject, vStr, Null)
    AssertEquals Err.Number, CustomError + 1
    Err.Clear
    On Error GoTo 0
End Sub

You may have noticed a call to an unimplemented function called "ArraySearch". VBA provides no built-in function for searching an array, so a robust implementation should be a part of any serious VBA programmer's toolbox. Implementing ArraySearch in a way that intelligently handles heterogeneous arrays of multiple subtypes without producing errors or incorrect result is not trivial and will be a topic for a future post.