How do I make a Binary Search Algorithm on Excel?

[NewToVB]

Basically I have to search a data set via customer codes using a binary
search algorithm and have no idea how to do it? I'm very new to VB so would
appreciate some simple words

 

[Charles Williams]

The simplest way is to use the Excel MATCH or VLOOKUP functions with
the sorted option, they already use Binary Search.

If you want to do it from VBA then use something like

vRow=application.WorksheetFunction.Match(ValuetoFind,RangetoSearch,1)

If for some reason you want to program it yourself in VB then I would
Google for Binary Search VB6 or VBA

regards
Charles

 

[Mike H]

Hi,

In summary a binary search works on sorted data and starts from the
mid-point of that data and; unless your lucky and find what you want,
continues the search at the mid-point of the data above or below the start
point. It does this iteratively until the required data are found.

Is the creation of binary search code an end in itself i.e. is this some
sort of assignment or are you simply supposing that's the best way to search
through data?

Unless the data set are very large; and in Excel that's not really likely,
and if it is your probably using the wrong software.

Perhaps you could give us some more detail on what your trying to achieve.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rich Locus]

Hello:
I use binary searches quite frequently. As you know, you can search an
extremely large number of rows very quickly.

A couple of cautions: Before you do a binary search in VBa, make sure you
sequence check the rows.

If you use the Excel sort feature in VBA... and you are not careful... you
can sort it in such a manner that numbers are sorted as numbers, and it will
not be in sequence as far as "IF" compares.

Here's my typical approach:

1) Format the rows using a Cstr() command to convert any numbers to strings.
2) Use the Excel Sort feature from VBA (it is hundreds of times faster than
a bubble sort)
3) Sequence check the column
4) Then do the binary search

The following is all the code you will need to be able to set up a test case
and make modifications for your own application:

Option Explicit
Option Base 1
Sub TestBinarySearch()
Dim strSearchArray(15) As String
Dim strValueArray(15) As String
Dim intTheUpperLimit As Long
Dim strTheSearchFor As String
Dim booFound As Boolean
Dim strFoundValue As String
Dim strWhatImLookingFor As String

strSearchArray(1) = "A"
strSearchArray(2) = "C"
strSearchArray(3) = "E"
strSearchArray(4) = "G"
strSearchArray(5) = "I"
strSearchArray(6) = "K"
strSearchArray(7) = "M"
strSearchArray(8) = "P"
strSearchArray(9) = "R"
strSearchArray(10) = "T"
strSearchArray(11) = "V"
strSearchArray(12) = "W"
strSearchArray(13) = "X"
strSearchArray(14) = "Y"
strSearchArray(15) = "Z"
strValueArray(1) = "1"
strValueArray(2) = "2"
strValueArray(3) = "3"
strValueArray(4) = "4"
strValueArray(5) = "5"
strValueArray(6) = "6"
strValueArray(7) = "7"
strValueArray(8) = "8"
strValueArray(9) = "9"
strValueArray(10) = "10"
strValueArray(11) = "11"
strValueArray(12) = "12"
strValueArray(13) = "13"
strValueArray(14) = "14"
strValueArray(15) = "15"

Do Until strTheSearchFor = "Exit"
strTheSearchFor = InputBox("Enter Search Argument")
intTheUpperLimit = 15
strFoundValue = "*"
Call BinarySearch(strSearchArray, strValueArray, intTheUpperLimit,
strTheSearchFor, booFound, strFoundValue)
MsgBox ("Found = " & booFound & " Value = " & strFoundValue)
Loop

End Sub

Sub TestSequenceCheck()
Dim strSearchArray(15) As String
Dim intTheUpperLimit As Long
Dim booInSequence As Boolean

strSearchArray(1) = "A"
strSearchArray(2) = "C"
strSearchArray(3) = "D"
strSearchArray(4) = "E"
strSearchArray(5) = "H"
strSearchArray(6) = "K"
strSearchArray(7) = "M"
strSearchArray(8) = "P"
strSearchArray(9) = "R"
strSearchArray(10) = "T"
strSearchArray(11) = "V"
strSearchArray(12) = "W"
strSearchArray(13) = "X"
strSearchArray(14) = "Y"
strSearchArray(15) = "a"

intTheUpperLimit = 15

Call SequenceCheck(strSearchArray, intTheUpperLimit, booInSequence)

MsgBox ("Sequence Check Proved " & booInSequence)

End Sub

Sub BinarySearch(SearchArray() As String, _
ValueArray() As String, _
intUpperLimit As Long, _
SearchFor As String, _
Found As Boolean, _
FoundValue As String)
Dim Left, Right, Middle As Integer
Found = False
Left = 1
Right = intUpperLimit

Do Until Found
If Left > Right Then
Exit Do
End If
Middle = (Left + Right) / 2
If SearchArray(Middle) = SearchFor Then
Found = True
FoundValue = ValueArray(Middle)
ElseIf SearchFor > SearchArray(Middle) Then
Left = Middle + 1
Else
Right = Middle - 1
End If
Loop
End Sub

Sub SequenceCheck(SearchArray() As String, _
UpperLimit As Long, _
InSequence As Boolean)

Dim strLastValue As String
Dim intArrayPointer As Long

InSequence = True

strLastValue = SearchArray(1)
For intArrayPointer = 1 To UpperLimit
If strLastValue > SearchArray(intArrayPointer) Then
InSequence = False
Exit For
Else
strLastValue = SearchArray(intArrayPointer)
End If
Next intArrayPointer
End Sub