What can cause Excel to crash?

[Charles]

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

 

[Bernie Deitrick]

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.