Array Formula Limations (MMULT, TRANSPOSE etc.)

Excel 97 and Windows 98

I have created a procedure that builds matrices (square or rectangular arrays) and then performs mathematical operations using MMULT, MINVERSE, and TRANSPOSE etc. At some point (when a matrix reaches about 6,000 cells) the operation begins to return #VALUE for the array. According to Microsoft Q166342, the only limitation is RAM. Unfortunately, the 6,000 cells limitation appears to hold for computers with 64 or 512 of RAM. What is the true limitation on array formula calculations? And is there a work around?

<<Background information on question 2 -- A little background: Lognormal time series stock returns are generated for each component of a portfolio (the result of an external database query)- generally I am concerned with the most recent 60-months of data (all though this varies). The portfolio can be an actual portfolio or a list of user defined inputs - obviously the list can grow quite large. Thus the initial matrix is 60 rows by n columns. "asset_count" in this case is the number of columns. One of my goals is to make the spreadsheet as transparent as possible. The below code allows the user to see exactly where each figure originated.

' Creates the exponentially smoothed covariance matrix

Workbooks("Optimizer.xls").Worksheets("ReturnSummary"). _
Range(Cells(221, 1), Cells(221 + asset_count - 2, asset_count - 1)).Select

Selection.FormulaArray = "=MMULT(TRANSPOSE(R[-150]C[]: R[-90]C[" _
& asset_count - 2 & "]),R[-70]C[]:R[-10]C[" & asset_count - 2 & "])"

Workbooks("Optimizer.xls").Worksheets("SmoothedCovariance").Select

Workbooks("Optimizer.xls").Worksheets("SmoothedCovariance"). _
Range(Cells(2, 2), Cells(2 + asset_count - 2, asset_count)).Select

Selection.FormulaArray = "=12*MMULT(TRANSPOSE(ReturnSummary!R[69]C[-1]: R[129]C[" _
& asset_count - 3 & "]),ReturnSummary!R[149]C[-1]:R[209]C[" & asset_count - 3 & "])"

As a workaround to the 6,000 cell limit, I created a looping procedure that builds the matrix cell by cell - unfortunately it takes almost an hour to run and for some unknown reason each time I run it again it takes slightly longer than the time before.

For j = 0 To asset_count - 2

Application.StatusBar = "Building Exponentially Smoothed Covariance Matrix... Column " & j + 1 & _
" of " & asset_count - 1

For x = 1 To asset_count - 1

Workbooks("Optimizer.xls").Worksheets("SmoothedCovariance"). _
Range("B2").Offset(x - 1, j).FormulaArray = "=12*MMULT(TRANSPOSE(ReturnSummary!R[" & 70 - x & _
"]C[" & -1 & "]:R[" & 130 - x & "]C[" & -1 & "]),ReturnSummary!R[" & 150 - x & "]C[" & _
-2 + x - j & "]:R[" & 210 - x & "]C[" & -2 + x - j & "])"

Application.StatusBar = "Building Exponentially Smoothed Covariance Matrix... Column " & j + 1 & _
" of " & asset_count - 1 & " Row " & x

Next x

Next j

Your help is greatly appreciated. Thanks!

-Tom