Sorting two-dimensional array

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote