====== Microsoft Excel - Macros - Search - Search Binary ======

<code excel>
' Binary Search of array.
' Tries to find vCrit within oArrWithCrit.
' If found then return corresponding oArrWithValues otherwise vDefault.
' Note that due to way that binary search works this may not report on the very 1st instance of that vCrit if this
' was done sequentially.  It will simply report on the 1st instance found and there may be various oArrWithValues
' data against the same vCrit.
Function ArrayFind(oArrWithCrit As Variant, vCrit As Variant, oArrWithValues As Variant, vDefault As Variant)
    Dim low As Long
    low = LBound(oArrWithCrit)
    Dim high As Long
    high = UBound(oArrWithCrit)
    Dim i As Long
    Dim result As Boolean
    ArrayFind = vDefault
    
    Do While low <= high
        i = (low + high) / 2
        If vCrit = oArrWithCrit(i, 1) Then
            ArrayFind = oArrWithValues(i, 1)
            Exit Do
        ElseIf vCrit < oArrWithCrit(i, 1) Then
            high = (i - 1)
        Else
            low = (i + 1)
        End If
    Loop
    
End Function
</code>

and

<code excel>
' Tries to find vCrit within oArrWithCrit.
' If found then returns vFoundValue otherwise vNotFoundValue.
' Note that due to way that binary search works this may not report on the very 1st instance of that vCrit if this
' was done sequentially.  It will simply report on the 1st instance found and there may be various oArrWithValues
' data against the same vCrit.
Function ArrayFindEx(oArrWithCrit As Variant, vCrit As Variant, vFoundValue As Variant, vNotFoundValue As Variant)
    Dim low As Long
    low = LBound(oArrWithCrit)
    Dim high As Long
    high = UBound(oArrWithCrit)
    Dim i As Long
    Dim result As Boolean
    ArrayFindEx = vNotFoundValue
    Do While low <= high
        i = (low + high) / 2
        If vCrit = oArrWithCrit(i, 1) Then
            ArrayFindEx = vFoundValue
            Exit Do
        ElseIf vCrit < oArrWithCrit(i, 1) Then
            high = (i - 1)
        Else
            low = (i + 1)
        End If
    Loop
    
End Function
</code>