Differences

This shows you the differences between two versions of the page.

--- microsoft_excel:macro_search_binary [2016/07/11 11:29] – created peter
+++ microsoft_excel:macro_search_binary [2021/08/04 14:24] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== Microsoft Excel - Macro 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>