Sort/Search Arrays
 

How do you sort an array and then do a binary search on an array in VBA?
Also, can you assign pointers to the array? How?

Sort/Search Arrays
 

You can easily sort an array, and it would depend upon whether the array
will be large or not, how many dimensions it has, as to which type of sort
you might use.

Here is a simple bubble sort

Option Explicit

Public Function BubbleSort(InVal As Variant, Optional Order As String =
"Asc")
Dim fChanges As Boolean
Dim iElement As Long
Dim iElement2 As Long
Dim temp As Variant
Dim ToSort

If TypeName(InVal) = "Variant()" Then
ToSort = InVal
Do
fChanges = False
For iElement = LBound(ToSort) To UBound(ToSort) - 1
If ((Order = "Asc" And ToSort(iElement) ToSort(iElement +
1)) Or _
(Order < "Asc" And ToSort(iElement) < ToSort(iElement +
1))) Then
'Swap elements
temp = ToSort(iElement)
ToSort(iElement) = ToSort(iElement + 1)
ToSort(iElement + 1) = temp
fChanges = True
End If
Next iElement
Loop Until Not fChanges
ElseIf TypeName(InVal) = "Range" Then
ToSort = InVal
ReDim temp(LBound(ToSort, 2) To UBound(ToSort, 2))
Do
fChanges = False
For iElement = LBound(ToSort) To UBound(ToSort) - 1
If ((Order = "Asc" And ToSort(iElement, 1) ToSort(iElement
+ 1, 1)) Or _
(Order < "Asc" And ToSort(iElement, 1) <
ToSort(iElement + 1, 1))) Then
'Swap elements
For iElement2 = LBound(ToSort, 2) To UBound(ToSort, 2)
temp(iElement2) = ToSort(iElement, iElement2)
ToSort(iElement, iElement2) = ToSort(iElement + 1,
iElement2)
ToSort(iElement + 1, iElement2) = temp(iElement2)
Next iElement2
fChanges = True
End If
Next iElement
Loop Until Not fChanges
End If
BubbleSort = ToSort
End Function



Sub testbubblesort()
Dim ary
ary = Array(1, "Bill", 4, 3, 6, "Alan", "Barry", 2)
ary = BubbleSort(ary)
ary = BubbleSort(Range("B1:D10"))
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mike H." wrote in message
...
How do you sort an array and then do a binary search on an array in VBA?
Also, can you assign pointers to the array? How?

Sort/Search Arrays
 

I am having trouble getting this code to work. When I call the function
1. Is the INVal the array I am passing?
2. The "Dim To Sort" line doesn't make sense to me. Don't you have to Dim
it as something?
Thanks...


Option Explicit

Public Function BubbleSort(InVal As Variant, Optional Order As String =
"Asc")
Dim fChanges As Boolean
Dim iElement As Long
Dim iElement2 As Long
Dim temp As Variant
Dim ToSort

If TypeName(InVal) = "Variant()" Then
ToSort = InVal
Do
fChanges = False
For iElement = LBound(ToSort) To UBound(ToSort) - 1
If ((Order = "Asc" And ToSort(iElement) ToSort(iElement +
1)) Or _
(Order < "Asc" And ToSort(iElement) < ToSort(iElement +
1))) Then
'Swap elements
temp = ToSort(iElement)
ToSort(iElement) = ToSort(iElement + 1)
ToSort(iElement + 1) = temp
fChanges = True
End If
Next iElement
Loop Until Not fChanges
ElseIf TypeName(InVal) = "Range" Then
ToSort = InVal
ReDim temp(LBound(ToSort, 2) To UBound(ToSort, 2))
Do
fChanges = False
For iElement = LBound(ToSort) To UBound(ToSort) - 1
If ((Order = "Asc" And ToSort(iElement, 1) ToSort(iElement
+ 1, 1)) Or _
(Order < "Asc" And ToSort(iElement, 1) <
ToSort(iElement + 1, 1))) Then
'Swap elements
For iElement2 = LBound(ToSort, 2) To UBound(ToSort, 2)
temp(iElement2) = ToSort(iElement, iElement2)
ToSort(iElement, iElement2) = ToSort(iElement + 1,
iElement2)
ToSort(iElement + 1, iElement2) = temp(iElement2)
Next iElement2
fChanges = True
End If
Next iElement
Loop Until Not fChanges
End If
BubbleSort = ToSort
End Function



Sub testbubblesort()
Dim ary
ary = Array(1, "Bill", 4, 3, 6, "Alan", "Barry", 2)
ary = BubbleSort(ary)
ary = BubbleSort(Range("B1:D10"))
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mike H." wrote in message
...
How do you sort an array and then do a binary search on an array in VBA?
Also, can you assign pointers to the array? How?