Exceeding maximum array size
Posted by Wayne Field on September 15, 2001 7:43 AM
For anyone familiar with linear algegra, I am writing a spreadsheet that needs to do array calculations (specifically do the inverse)on a 96 row by 96 column array. Apparently this exceeds the maximum array size for excel. Is there any way around this?
Posted by Damon Ostrander on September 15, 2001 12:54 PM
Hi Wayne,
I just checked the Excel 2K specs, and there is no limitation on Excel array size--it is only limited by available memory. Are you using an earlier version of Excel? If so, just switching to Excel 2K might do the trick.
If you really need to avoid the use of Excel arrays, the inversion can be done in VBA. VBA can handle Excel arrays, but in this case you could avoid them by using Range objects instead. Just write a macro (a Sub rather than a Function) that, given the input and output array ranges, puts the input range into a VBA array, and does the same thing in reverse with the output array. You will have to refer to the Excel inverse function as:
OutArray = Application.MINVERSE( InArray )
where OutArray is a Variant that contains the resulting array, which must be dimensioned the same as InArray.
By the way, you can expect it to take a LONG time to do the inverse. 96 x 96 is pretty big.
Happy computing.
Damon
