Matrix and Vector algebra in VBA

Hi all

I have created a macro to make a Mean/variance frontier for stocks. It works but it is not dynamic in the sense that i can add more stocks to see how the frontier will change.

Its uses an extensive amount of matrix algebra, which i found was only possible using the worksheetfunctions MMULT, TRANSPOSE and MINVERSE, using ranges instead of arrays.

Is it not possible to do these computations on arrays instead ? This would make the code much nicer and i would not have to grab all my data from the spreadsheet.

If it is not possible to do with arrays I just want it made dynamic, so it can take any size of range to make the graph. Its just pretty ugly code thats all.

Would also be appreciated if someone could redirect me to any good "Linear algebra for VBA" threads.. Ive tried google without much luck...

Please Login or Register  to view this content.

Thanks guys your help has been much appreciated. I have been doing some playing around on my own and it seems I can use my own dynamic arrays (not the ones taken from the worksheet) if I just write "application", Instead of Application.Worksheetfunction. Using this I should be able to make sth more Dynamic than what I have now...

Please Login or Register  to view this content.

Pretty smooth thanks i knew there had to be a better way than mine.....

Originally Posted by shg

Instead of this ...

Please Login or Register  to view this content.

You could do this:

Please Login or Register  to view this content.

Pretty smooth thanks i knew there had to be a better way than mine.....