Thread: Sorting two-dimensional array
2014-06-09, 17:18 #1
- Join Date
- Apr 2003
- Vail, Colorado, USA
- Thanked 0 Times in 0 Posts
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.
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
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!
2014-06-10, 21:40 #2
- Join Date
- Jan 2001
- Melbourne, Victoria, Australia
- Thanked 128 Times in 121 Posts
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.Andrew Lockton, Chrysalis Design, Melbourne Australia
2014-06-20, 18:28 #3
- Join Date
- Dec 2009
- Dallas, Texas, USA
- Thanked 5 Times in 5 Posts
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 Gray, Chief Wizard
Irving, Texas, USA
WizardWrx Web - Technical Articles and Free Software
You are more important than any technology we may employ.