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

--
Rich Locus
Logicwurks, LLC


"NewToVB" wrote:

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 :)