# Sorting two-dimensional array

• 2014-06-09, 17:18
mitchbvi
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
• 2014-06-10, 21:40
Andrew Lockton
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.
• 2014-06-20, 18:28
TXWizard
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.