Vfrdtyky

I am trying to do calculations as part of regression model in Excel.

I need to calculate ((X^T)WX)^(-1)(X^T)WY. Where X, W, Y are matrices and ^T and ^-1 are denoting the matrix transpose and inverting operation.

Now when X, W, Y are of small dimensions I simply run my macro which calculates these values very very fast.

However sometimes I am dealing with the case when say, the dimensions of X, W, Y are 5000 X 5, 5000 X 1 and 5000 X 1 respectively, then the macro can take a lot longer to run.

I have two questions:

1. Would, instead of using my macro which generates the matrices on Excel sheets and then uses Excel formulas like MMULT and MINVERSE etc. to calculate the output, it be faster for larger dimension matrices if I used arrays in VBA to do all the calculations? (I am not too sure how arrays work in VBA so I don't actually know if it would do anything to excel, and hence if it would be any quicker/less computationally intensive.)




2. If the answer to the above question is no it would be no quicker. Then does anybody have an idea how to speed such calculations up? Or do I need to simply put up with it and wait.

Thanks for your time.

Considering that the algorithm of the code is the same, the speed ranking is the following:

1. Dll custom library with C#, C++, C, Java or anything similar


2. VBA


3. Excel

I have compared a VBA vs C++ function here, in the long term the result is really bad for VBA.

So, the following Fibonacci with recursion in C++:

int __stdcall FibWithRecursion(int & x)

{

    int k = 0;

    int p = 0;



    if (x == 0)

        return 0;



    if (x == 1)

        return 1;



    k = x - 1;

    p = x - 2;

    return FibWithRecursion(k) + FibWithRecursion(p);

}

is exponentially better, when called in Excel, than the same complexity function in VBA:

Public Function FibWithRecursionVBA(ByRef x As Long) As Long



    Dim k As Long: k = 0

    Dim p As Long: p = 0



    If (x = 0) Then FibWithRecursionVBA = 0: Exit Function

    If (x = 1) Then FibWithRecursionVBA = 1: Exit Function



    k = x - 1

    p = x - 2



    FibWithRecursionVBA = FibWithRecursionVBA(k) + FibWithRecursionVBA(p)



End Function

Better late than never:
I use matrices that are bigger, 3 or 4 dimensions, sized like 16k x 26 x 5.
I run through them to find data, apply one or two formulas or make combos with other matrices.

Number one, after starting the macro, open another application like notepad, you might have a nice speed increase ☺ !

Then, I guess you switched of screen updating etc, and turned of automatic calculation

As last: don't put the data in cells, not in arrays.
Just something like:

• Dim Matrix1 as String ===>'put it in declarations if you want to use it in other macros as well. Remember you can not do "blabla=activecell.value2" etc anymore!!


• In the "Sub()" code, use ReDim Matrix1(1 to a_value, 1 to 2nd_value, ... , 1 to last_value)


• Matrix1(45,32,63)="what you want to put there"

After running, just drop the

Matrix1(1 to a_value, 1 to 2nd_value,1) at 1st sheet,

Matrix1(1 to a_value, 1 to 2nd_value,2) at 2nd sheet, etc

Switch on screen updating again, etc
In this way my calculation went from 45 minutes to just one, by avoiding the intermediary screen update
Success, I hope it is useful for somebody