Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    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
                ActiveCell.Value = n
                ActiveCell.Offset(0, 1).Value = ArrData(n, i)
                ActiveCell.Offset(1, 0).Select
                'TEST Ends
            ActiveCell.Offset(1, 0).Select
        BubbleSort2D ArrData, 1, 2
        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.


  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!

    Excel 2013: The Missing Manual

    + 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. #2
    Super Moderator
    Join Date
    Jan 2001
    Melbourne, Victoria, Australia
    Thanked 124 Times in 117 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

  4. #3
    2 Star Lounger
    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.
    Attached Files Attached Files
    David Gray, Chief Wizard
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

Posting Permissions

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