Results 1 to 5 of 5

Thread: Using Arrays to populated Matrices and other data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2016
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using Arrays to populated Matrices and other data

    I have a spreadsheet where i create 4-5 fairly large matrices in worksheet cells and then perform some further calculation using these.

    I'd like to update the spreadsheet to not use worksheet cells and to hopefully run faster.

    I've started to do this using 2D Arrays to store my data in reference it in later calculations, however this is running pretty slow (may even be slower than v1 using worksheets cells). Here is an example of one of my arrays.

    'Corr Matrix Array
    ReDim CorrArr(1 To c, 1 To c) As Variant
    For i = LBound(CorrArr, 1) To UBound(CorrArr, 1)
    For j = LBound(CorrArr, 2) To UBound(CorrArr, 2)
    CorrArr(i, j) = WorksheetFunction.Correl(ws1.Range(ws1.Cells(i + 1, 7), ws1.Cells(i + 1, LastCol1)), ws1.Range(ws1.Cells(j + 1, 7), ws1.Cells(j + 1, LastCol1)))
    Next
    Next

    My guess is it is really being slowed down by having to loop through each element of the matrix (which is 1000 x 1000).

    Is there a way to create arrays or store data without having to loop through each element?

    Thanks

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,639
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Using Arrays to populated Matrices and other data

    The key to running a fast macro is to read all the data from your spreadsheet in one shot, save it in an internal array, and do the processing from there. In your macro, you read the worksheet in every loop, or i*j times, = 1,000,000. Which is slow. Try this:

    Code:
    Sub test1()
    Dim CorrArr() As doubld, CorrData As Double, c As Long, i As Long, j As Long
    
    
        CorrData = Range("G2:K6").Value
        'Corr Matrix Array
        c = UBound(CorrData)
        ReDim CorrArr(1 To c, 1 To c) As Variant
        
        For i = LBound(CorrArr, 1) To UBound(CorrArr, 1)
            For j = LBound(CorrArr, 2) To UBound(CorrArr, 2)
                CorrArr(i, j) = WorksheetFunction.Correl(WorksheetFunction.Index(CorrData, i, 0), WorksheetFunction.Index(CorrData, j, 0))
            Next j
        Next i
    
    
    End Sub
    The line in red reads the entire array in one shot. Then the INDEX function picks out the rows you want to compare. I haven't tested it on an array of that size, but this should run much quicker. Let us know.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Jul 2016
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Arrays to populated Matrices and other data

    Thanks Eric, I updated the code with your suggestion and it seems to improve the speed from around 40 secs to 35ish.

    I timed the various parts of the sub and found that this is what takes the majority of the time. Do you know if there is a way to perform this task in a more efficient way?

    ReDim AvCorr(1 To c) As Variant
    For i = LBound(AvCorr, 1) To UBound(AvCorr, 1)
    AvCorr(i) = (Application.Sum(Application.Index(CorrArr, 0, i)) - 1) / (c - 1)
    Next i

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,639
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Using Arrays to populated Matrices and other data

    The only thing that immediately comes to mind is to make sure that AvCorr is defined as Double (you can't change that on a ReDim statement). If you make it a Variant, VBA has to do some internal type conversions which can take some time. Other than that, if you have 1000 columns, that might be just how long it takes.

    You could experiment with different VBA/Excel functions. For example, both these routines give the same results:

    Code:
    Sub test3()
    Dim CorrArr As Variant, AvCorr As Variant, Ones() As Long, i As Long
    
        c = 5
        CorrArr = Range("A1:E5").Value
        
        ReDim Ones(1 To 1, 1 To c)
        For i = 1 To c
            Ones(1, i) = 1
        Next i
        AvCorr = WorksheetFunction.MMult(Ones, CorrArr)
        For i = 1 To c
            AvCorr(i) = (AvCorr(i) - 1) / (c - 1)
        Next i
    
    End Sub
    Code:
    Sub test4()
    Dim AvCorr As Variant
    
        AvCorr = Evaluate("(MMULT(COLUMN(A1:E1)^0,A1:E5)-1)/4")
        
    End Sub
    The first uses MMULT instead of SUM and INDEX, but it does require 2 additional loops. Both should be quick, but I can't really tell if the combined routine will run longer or shorter than your current version.

    The second is a 1-liner, but reads from the sheet. We know that's slow, but if this is the only time you read it, it should be OK. Even if not, it might be worth testing/timing. You do have to create the string in the Evaluate, I don't know how dynamic your sheet is.

    Hope this gives you some ideas to try!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    Board Regular
    Join Date
    Jul 2016
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Arrays to populated Matrices and other data

    Thanks Eric I'll have a play with those and see if either are any faster to run.
    Appreciate the help!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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