Sort an array within CB code

joelsmalley · Mar 10th 2003

I have created an array in VB code below:

Sub tempMD()

Dim ClientDNBuy(30)
Dim nCount As Integer

For Each MDClient In Range("Clients")

MDColumnOffset = Application.WorksheetFunction.Match(MDClient, Range("BlotterClients"), 0)
MDBuy = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset - 1)
MDStockRef = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset + 1)
MDDelta = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset + 2)
MDSwapRef = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset + 3)
MDOR = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset + 4)
MDEquityDN = ((TouchStock - MDStockRef) * MDConvRatio / MDParValue * MDParQuote * MDDelta)
MDBondDN = (MDLiveSwap - MDSwapRef) * MDRhoPhi * 100

ClientDNBuy(nCount) = Array(MDClient, MDBuy + MDEquityDN + MDBondDN)
nCount = nCount + 1

This creates an array 30 rows X 2 columns with client name next to a value. Is it possible to sort this array as if it were a range in the spreadsheet, without pasting it into the spreadsheet and sorting!

Joel

Egad · Mar 10th 2003

Bubble sort!

This bubble sort sorts on 1 column only, I have seen examples sorting on multiple columns but would have to dig them up.

'numcols = number of columns in array

For i = 1 To UBound(MyArray, 1)
For j = 1 To UBound(MyArray, 1)
If MainArray(j, 1) > MyArray(j + 1, 1) Then
For k = 1 To numcols
tempArray(k) = MyArray(j, k)
Next k
For k = 1 To numcols
MyArray(j, k) = MyArray(j + 1, k)
Next k
For k = 1 To numcols
MyArray(j + 1, k) = tempArray(k)
Next k
End If
Next
Next

joelsmalley · Mar 10th 2003

Thanks. Sets me on the right track but would appreciate the two column version iof you can find it!

Egad · Mar 10th 2003

The following code will sort on column 1 then column 2 (I've also fixed bugs in my previous example):

Dim MyArray(10, 2)
Dim tempArray(10, 2)

Sub sorting()

'optional - this just creates an array from a spreadsheet (a1:b10) - for testing purposes
For x = 1 To 10
For y = 1 To 2
MyArray(x, y) = Cells(x, y).Value
Next y
Next x

'Sorting Macro
numcols = 2
For i = 1 To UBound(MyArray, 1) - 1
For j = 1 To UBound(MyArray, 1) - 1
If MyArray(j, 1) > MyArray(j + 1, 1) Or (MyArray(j, 1) = MyArray(j + 1, 1) And MyArray(j, 2) > MyArray(j + 1, 2)) Then
For k = 1 To numcols
tempArray(j, k) = MyArray(j, k)
Next k
For k = 1 To numcols
MyArray(j, k) = MyArray(j + 1, k)
Next k
For k = 1 To numcols
MyArray(j + 1, k) = tempArray(j, k)
Next k
End If
Next
Next

'optional - this just dumps the array back to the spreadsheet (k1:l10) - for testing purposes
For x = 1 To 10
For y = 1 To 2
Cells(x, y + 10).Value = MyArray(x, y)
Next y
Next x

End Sub

Hope this helps, Doug

joelsmalley · Mar 10th 2003

Thanks Doug, very helpful.

joelsmalley · Mar 10th 2003

If anyone else is interested, this does what I wanted:

Function SelectionSort(TempArray As Variant)
Dim MaxVal As Variant
Dim MaxIndex As Integer
Dim i, j As Integer

' Step through the elements in the array starting with the
' last element in the array.
For i = UBound(TempArray) To 1 Step -1

' Set MaxVal to the element in the array and save the
' index of this element as MaxIndex.
MaxVal = TempArray(i)
MaxIndex = i

' Loop through the remaining elements to see if any is
' larger than MaxVal. If it is then set this element
' to be the new MaxVal.
For j = 0 To i
If TempArray(j)(1) > MaxVal(1) Then
MaxVal = TempArray(j)
MaxIndex = j
End If
Next j

' If the index of the largest element is not i, then
' exchange this element with element i.
If MaxIndex < i Then
TempArray(MaxIndex) = TempArray(i)
TempArray(i) = MaxVal
End If
Next i

End Function

Sub SelectionSortMyArray()
Dim TheArray(3)
' Create the array.
TheArray(0) = Array("Zero", 0)
TheArray(1) = Array("Three", 3)
TheArray(2) = Array("One", 1)
TheArray(3) = Array("Two", 2)

' Sort the Array and display the values in order.
SelectionSort TheArray

End Sub

regards, Joel

Sort an array within CB code

Participate now!

Share