Charles,
Your UDF returning an array can know what size to return: here's an example:
Function ArrayF() As Variant
Dim ReturnArray() As Integer
Dim i As Integer
Dim j As Integer
Dim myRows As Long
Dim myCols As Long
myRows = Application.Caller.Rows.Count
myCols = Application.Caller.Columns.Count
ReDim ReturnArray(1 To myRows, 1 To myCols)
For i = 1 To myRows
For j = 1 To myCols
ReturnArray(i, j) = i * j
Next j
Next i
ArrayF = ReturnArray
End Function
Also, depending on your array calculations, you may be able to replace a lot of them with a
PivotTable.... depends.
--
HTH,
Bernie
MS Excel MVP
"Charles" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Excel (2002 SP3) crashes regularly on several of my model, most of the
> time when I open the spreadsheet. Was wondering what could cause this
> unstability. The main model I use is specific in the following way:
>
> - more than 500 range names are used
> - user defined functions:
> - array UDF
> - one UDF returning an array of 10,000 rows but with an array
> formula defined on only 20 rows (this basically allow me to return a
> variable number of rows (20 in this case), since there is no way to
> know what is the size of the output of an array UDF
> - lot of array calculations (excel formula). A list of 2000 entries,
> with a lot (probably 10,000 cells) of calculations based on the whole
> column
> - file size of 5Mb
>
> the spreadsheet is nearly brand new, so hopefully no weird stuff like
> circular reference or #REF.
>
> Any thought?
> Charles
>
|
Similar Threads
