Using Arrays to populated Matrices and other data

[gt213]

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

 

[Eric W]

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:

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.

 

[gt213]

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

 

[Eric W]

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:

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

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!

 

[gt213]

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