Sorting a multi-dimensional array in Excel VBA 2007

[phudgens]

I'm using the VBA Sort routines to do 3-level sorts of 2-dimensional worksheet tables of data in Excel 2007. This works well, and requires a minimum of code. I'm wondering if there are similar VBA routines to sort multi-dimensional arrays of data, ie data that has not been written out to a worksheet. Or is it necessary to write code to do such sorts? Thanks for any insight.

Paul Hudgens
Denver

 

[SkipVought]

hi,

Code required.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[phudgens]

I was afraid of that - thanks.

 

[N1GHTEYES]

I don't suppose you have access to Visual Studio do you? Full blown VB list boxes have a sort property which can come in handy. In the past, I've sometimes done my sorting using them. The idea is to create a form and put a list box on it - you can keep it invisible if you like. When you want to sort the data, you simply add it to the list box, and set the sorted property to true. Et voila - you have a sorted list. Unfortunately, I don't think VBA listboxes are quite so clever - at least not in my old steam-powered version. Maybe 2007 can hack it.

If you need a sort routine, you could try quicksort. It is not the best in the world, but it is usually quick enough unless your data is huge. Here is some code. Obviously, you'll need to tweak it to allow you to do multi-dimensional sorting, but it is a start.

Public Sub Quick_Sort(ByRef SortArray As Variant, ByVal First As Long, ByVal Last As Long)
'This sub performs a sort-in-place on the array "SortArray", using the quicksort algorithm.
'When first called, First and Last should be the lower and upper bounds respectively of Sortarray.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Low As Long, High As Long
Dim temp As Variant, List_Separator As Variant
Low = First
High = Last
List_Separator = SortArray((First + Last) / 2)
Do
    Do While (SortArray(Low) < List_Separator)
        Low = Low + 1
    Loop
    Do While (SortArray(High) > List_Separator)
        High = High - 1
    Loop
    If (Low <= High) Then
        temp = SortArray(Low)
        SortArray(Low) = SortArray(High)
        SortArray(High) = temp
        Low = Low + 1
        High = High - 1
    End If
Loop While (Low <= High)
If (First < High) Then Quick_Sort SortArray, First, High
If (Low < Last) Then Quick_Sort SortArray, Low, Last
End Sub

Alternatively, could you not simply assign your data to an unused, possibly hidden, section of the workbook (or one opened just for this purpose), and sort it there? If you do, remember to assign your array data as a block to the chosen range, not cell by cell. If you do that it should be fairly quick.

I hope that helps,

Tony

 

[phudgens]

Thanks for the input. I do not have access to Visual Studio but it may be time to acquire it. I'm needing to sort large 2-dimensional arrays with conditional sorts (ascending-descending, varying columns) depending on what options the user selects. Writing out to a worksheet is working - I was just hoping there was an easy way to avoid that step. Thanks again.

 

[Gruuuu]

Actually, writing it out to a worksheet might still be faster. If you want it not to look sloppy while running, you can always turn Application.ScreenUpdating to false while you do your sort, and back to true before the script is done.

 

[N1GHTEYES]

You might be better off doing it in code anyway. I've just done some timing tests to satisfy my own curiosity. I took an entire column of random data and sorted it using quicksort, then sorted the same data (in a macro so I could time it) using the Excel inbuilt sort process.

Quicksort alone took about 0.1s for 65536 samples.
To read the column of data, do the quicksort and paste the answer back into the sheet took 0.4s.
Using the inbuilt process took a more variable time, but mostly was around 0.6 to 0.7s.

So, if you are after speed, doing it all in code seems the best option.

Tony

 

[DougAJ4]

I have an open source VBA sort function here:

http://newtonexcelbach.wordpress.com/2009/03/23/a-sort-function/

The function can be used either from within VBA, or as a UDF to provide a dynamic sort.

There is also a link to a Chip Pearson article on sorting by transfer to the spreadsheet and back if you prefer that way (but it will normally be much slower)

Doug Jenkins

http://newtonexcelbach.wordpress.com/

 

[xlbo]

Chip's sort function could be faster if he didn;t iterate through cells to load back into an array

Quicker to do:

Dim arrVarOut as variant
arrVarOut = R

(works as a range is an array of values)

Tweaking Chip's routine to do that times a sort of 65536 values at 0.2 to 0.25s over 50 runs..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[yooneek]

Actually, I was looking for this yesterday...

Check out this site

http://www.xtremevbtalk.com/showthread.php?t=150521

OCD, it’s not obsessive if you can control it…

 

[phudgens]

This is great stuff - thanks guys.

Paul H.