Thread: Sorting two-dimensional array

1. Sorting two-dimensional array

I am using a User Form to select outputs for various cells. Then the worksheet is recalculated X times and that data stored in an array. I have had a lot of help getting this far but while I can sort a 1 dimensional array I have just not been able to figure it out for 2.
Because I am building a Monte Carlo simulation model there will be thousands of calculations when it is working and I do not want to have to enter all that data in a worksheet. It will slow it down too much. Because of th amount of data I feel that a Bubble sort will be slow not that I have been able to get that to work either.
The code includes data being entered into the worksheet but that is just for testing purposes.

Code:
Private Sub CommandButton1_Click()
Dim SelRangeVarr() As String, SelRangeV, ArrData(), Test
SelRangeVarr = Split(RefEdit1.Value, ",")
recalc = TextBox1                                                   'No of times the worksheet is to be recalculated
Range("E1").Select                                                       'Testing
ReDim ArrData(recalc, UBound(SelRangeVarr))        'Redimensions the Data Array to the No of Required Calcuations and the No of OutPut Cells
Arrout = "ArrCnt" & UBound(SelRangeVarr)
For n = 0 To recalc - 1                                         'Limits to No calcs as Array start at 0
Application.Calculate                                           'Calculates the Worksheet
For i = LBound(SelRangeVarr) To UBound(SelRangeVarr)
Set SelRangeV = Range(SelRangeVarr(i))      'Sets The Range
ArrData(n, i) = SelRangeV.Value                 'Assigns the Value of the Calculation for Each Out Put Cell
'TESTING
ActiveCell.Value = n
ActiveCell.Offset(0, 1).Value = ArrData(n, i)
ActiveCell.Offset(1, 0).Select
'TEST Ends
Next
ActiveCell.Offset(1, 0).Select
Next
BubbleSort2D ArrData, 1, 2
Range("G1").Select
For i = LBound(SelRangeVarr) To UBound(SelRangeVarr)
For n = 0 To recalc - 1
'Call QuickSort(ArrData, LBound(ArrData), UBound(ArrData))
ActiveCell.Value = ArrData(n, i)
ActiveCell.Offset(1, 0).Select
Next n
ActiveCell.Offset(-recalc, 1).Select
Next i
Unload Me
End Sub
Any suggestions will be greatly appreciated.

Thanks

2. Subscribe to our Windows Secrets Newsletter - It's Free!

Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

+ Get this BONUS — free!

Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

3. If you code is too slow, the first thing to address is your use of the Selection object. This would be faster to use Ranges. If you set an initial address you can use offset in both rows and columns to populate the values without continually changing the selection.

Secondly, you are double dipping - populating an array as well as writing the same data to a spreadsheet. Work out which one you are going to use and jettison the other. Personally, I would write the data to a spreadsheet and avoid the array completely. You can sort the spreadsheet data and retain all the values for use in your subsequent processing.

4. The other advantage to storing the data in a worksheet is that you can use the built-in Sort methods of the Range object, which can sort on up to three columns or rows. Beginning with Excel 2010, that limit has been effectively removed; I have successfully sorted on up to 5 keys.

However, if the array is not worth keeping, I dug into the notes for a project that I did in 2009 that required a routine to sort an array of information about the tabs (sheets) of a workbook. The only routine that I could find that could handle a two dimensional array was the venerable QuickSort algorithm. The attached code was thoroughly tested before I integrated it into the project, and it is compatible with all versions of Visual Basic. It should work even in VB.NET, although the .NET Framework provides much better alternatives.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•