PDA

View Full Version : Sorting an array from small to large with an error twist!



Sock
07-18-2013, 01:41 PM
Hi everyone, I hope this one is a little interesting. I've tried diving into sorting algorithms only to realize it's a pretty crazy place to go.

I have a single dimension array which can vary in size depending on the larger code's inputs. I want to sort this array from smallest to largest. Here's the catch: in this array there may be error values: CVErr(xlErrNA) to be exact. I'm unsure on how to incorporate these into the sorting situation.

Here's a contrived example with a five-item array. I've used the quicksort algorithm that I found from here: http://en.allexperts.com/q/Visual-Basic-1048/string-manipulation.htm (I hope linking this is okay; I just want to source where I have gotten things from)

Sub arraysorttest()

Dim arraytester(1 To 5) As Variant

arraytester(1) = 3
arraytester(2) = 2
arraytester(3) = CVErr(xlErrNA) 'Sort me! I dare you!
arraytester(4) = 1
arraytester(5) = 4

QuickSort arraytester, 1, UBound(arraytester)

End Sub

Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)

Dim pivot As Variant
Dim tmpSwap As Variant
Dim tmpLow As Long
Dim tmpHi As Long

tmpLow = inLow
tmpHi = inHi

pivot = vArray((inLow + inHi) \ 2)

While (tmpLow <= tmpHi)

While (vArray(tmpLow) < pivot And tmpLow < inHi)
tmpLow = tmpLow + 1
Wend

While (pivot < vArray(tmpHi) And tmpHi > inLow)
tmpHi = tmpHi - 1
Wend

If (tmpLow <= tmpHi) Then
tmpSwap = vArray(tmpLow)
vArray(tmpLow) = vArray(tmpHi)
vArray(tmpHi) = tmpSwap
tmpLow = tmpLow + 1
tmpHi = tmpHi - 1
End If

Wend

If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi

End Sub

I'm really interested in how I might be able to sort through an array when there are some tricky error values in there.

Thank you so much for everything, everyone! You've all been of immeasurable help to me!

p45cal
07-18-2013, 02:47 PM
I'm unsure on how to incorporate these [errors] into the sorting situation.Do you want to treat them as small values so they all end up at one end of the sorted array, or as large values so they appear at the other end of the sorted array .. or something else?

Sock
07-18-2013, 03:16 PM
Do you want to treat them as small values so they all end up at one end of the sorted array, or as large values so they appear at the other end of the sorted array .. or something else?

Hey, p45cal. Good question. I would prefer they end up on the larger part of the sorted array. Thanks for your response :)

p45cal
07-18-2013, 03:32 PM
one way:Sub arraysorttest()
'http://vbaexpress.com/forum/showthread.php?t=46878
Dim arraytester(1 To 5) As Variant
arraytester(1) = 3
arraytester(2) = 2
arraytester(3) = CVErr(xlErrNA) 'Sort me! I dare you!
arraytester(4) = 1
arraytester(5) = 4
'convert errors to large(st) values:
For i = 1 To UBound(arraytester)
If IsError(arraytester(i)) Then arraytester(i) = 9E+99
Next i
QuickSort arraytester, 1, UBound(arraytester)
'convert specific large values to errors:
For i = 1 To UBound(arraytester)
If arraytester(i) = 9E+99 Then arraytester(i) = CVErr(xlErrNA)
Next i
End Sub

Sock
07-18-2013, 05:48 PM
one way:Nice and simple


Thanks for the help! It's simple and should suit my purposes just fine. However, I'm going to keep this open for further discussion and other input if anyone else is interested/has some other method they'd like to share.

Once again, thanks for the speedy response; it's greatly appreciated :)

Kenneth Hobs
07-18-2013, 06:11 PM
Chip Pearson's routines should suffice. Import them and then try my test.

e.g.
' http://www.cpearson.com/excel/SortingArrays.aspx
Public Sub ken()
Dim a() As Variant, aa(1 To 5) As Variant, v As Variant

a() = Array("z", 1, 0, "a", "A", 11, 9)
aa(1) = 3
aa(2) = 2
aa(3) = CVErr(xlErrNA) 'Sort me! I dare you!
aa(4) = 1
aa(5) = 4

QSortInPlace a()
MsgBox Join(a(), vbLf)

MsgBox QSortInPlace(aa)
For Each v In aa()
Debug.Print v
Next v
End Sub