Sheet became suggish. Why?

[Don Wiss]

I have been working on one of my workbooks, including removing a reference
to an add-in, and in the VBA replacing with qualified calls to its
functions. (See other thread I started.) For some strange reason on one of
the worksheets it has become sluggish when you change a cell's value.
Sluggish enough that you see the Calculating Cells: 0% appear. All other
sheets are fine. On the sluggish sheet it is only sluggish when you change
a cell that has a dependent on another sheet. I turned off events, as these
cells also trigger an event. No effect. I did an Open and Repair on the
workbook. No effect. This is annoying, though not really fatal. This sheet
has so little input, and almost no formulas, that the users may not notice.
But I do. In the currently released version all is fine.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Don Wiss]

I have been working on one of my workbooks, including removing a reference
to an add-in, and in the VBA replacing with qualified calls to its
functions. (See other thread I started.) For some strange reason on one of
the worksheets it has become sluggish when you change a cell's value.
Sluggish enough that you see the Calculating Cells: 0% appear. All other
sheets are fine. On the sluggish sheet it is only sluggish when you change
a cell that has a dependent on another sheet. I turned off events, as these
cells also trigger an event. No effect. I did an Open and Repair on the
workbook. No effect. This is annoying, though not really fatal. This sheet
has so little input, and almost no formulas, that the users may not notice.
But I do. In the currently released version all is fine.

Update. A work colleague figured it out. He's not a programmer, but has
excellent spreadsheet skills. And as the actuary responsible for this
application he took an interest in the problem. He noticed that the file
(with no data) grew by over a MB relative to the current version. On the
Calc sheet (this is where we copy down formulas in over 100 columns) he hit
End-Home and the cursor moved to cell EB4668. There was nothing visible in
the cell. We then cleared all the lower rows. The size returned to where it
was and the sluggishness on the first sheet went away. Go figure.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Peter T]

Update. A work colleague figured it out. He's not a programmer, but has
excellent spreadsheet skills. And as the actuary responsible for this
application he took an interest in the problem. He noticed that the file
(with no data) grew by over a MB relative to the current version. On the
Calc sheet (this is where we copy down formulas in over 100 columns) he hit
End-Home and the cursor moved to cell EB4668. There was nothing visible in
the cell. We then cleared all the lower rows. The size returned to where it
was and the sluggishness on the first sheet went away. Go figure.

Don <www.donwiss.com> (e-mail link at home page bottom).

Does any UDF or event code process all cells in the UsedRange of that sheet,
just a guess.

Regards,
Peter T

 

[Don Wiss]

Does any UDF or event code process all cells in the UsedRange of that sheet,
just a guess.

The macro first builds the initial 24 columns. They are all hard coded
values. Then the row just above where the values were put has formulas in
columns 25 to 132 (132 is EB). Those formulas are then copied down to as
many rows as needed. The user defined function in the add-in appears in
many of those columns, and after I removed the reference to the add-in, and
temporarily had to give them the add-in name in front, is when the problem
started. This sheet has no events. To save space we now, after the formulas
are copied down, blank out the unused columns. And to keep the spreadsheet
from becoming unbearably sluggish, we range value none/most/all of the user
functions, depending on the number of rows.

If the user makes a change in input on one of the other sheets, a change
event then clears all the rows below that first formula row. Our
spreadsheets always do this, so we never have input and output out of sync.

Don <www.donwiss.com> (e-mail link at home page bottom).