Slow reaction on cell changes

I have a relatively complex application with a few thousand cell links and dozens of VBA macros (3.5 MB heavy). This application (one file for each project) is used by a few hundred users in about 200 projects.
What I did recently (to allow updating macro program functionality in existing files) is to move all macros into an add-in.

What happens now is every time when I change the content of any cell anywhere in the file, it takes about 3 seconds(!) to leave the cell after changing it and to jump into the one below.
Of course I'm getting complaints from users about this slow behaviour.

This also applies to cells in sheets that have no event macro program related to them.
It applies as well to cells that are not referenced in any other cell.
I tried changing the calculation method from automatic to manual - no change in speed.
I de-activated the add-in - no change in speed.
I have checked if there are any formulas that contain the today() or function or the like - there are none (actually, I had replaced them with links to a cell that contains today's date, which is automatically updated when the file is opened, but this was also this way in the previous version).

It seems that there is something going on that causes Excel to be busy with itself.

Does anyone know what might cause this strange behaviour?
I tried everything I could think of without success and I'm lost now.

We are using Excel 2003 in an XP Professional SP2 environment and most of our computers have 512 MB RAM.

I am desperately looking for help!

Mike

Does the file now contain any code at all, or is it all in the add-in? It sounds very much like event code is slowing things down to me.

Volatile formulas are the most likely other cause, but let's rule out one thing at a time!

Originally Posted by Stormseed

I guess, you are quite an experienced fellow to troubleshoot OS related issues.

This isn't an OS issue though?

Lately I remember someone like you at MrExcel who used to troubleshoot exactly the same way as you do and his name is Rory A.

Great minds think alike then! (or is it that fools seldom differ? )

I get cranked knowing more about issues like these for example

Excel junkie, huh?

If you use Volatile functions like INDIRECT, OFFSET, NOW and others, then those functions recalculate whenever the workbook does, even if their inputs don't change. If other calculations then depend on these volatile calculations, you end up recalculating large parts of the workbook for no reason. That can cause exactly the behaviour you describe. For more details see this page (and others on the same site).

Not volatile, but usually hugely inefficient. I almost never use VLOOKUP and then only if it's a simple two column table. A combination of INDEX and MATCH is preferable, especially if you return data from multiple columns for a given lookup value.

It's probably not helping but hard to say how much effect they have without seeing it. What are you using them for?

CELL is a volatile function.

There's a UDF at the link that Stomper posted that will tell you if a given function is volatile.

If you push F9 and see the Calculate indicator in the statusbar, you have volatile functions someplace.

Amplifying Stomper's INDEX/MATCH suggestion, look at formulas and see if there are expressions that get calculated duplicatively that could be eliminated by a helper column -- like the MATCH part of an INDEX/MATCH.

The CELL functions are used to identify to a macro program where the end of a table is (several tables within one sheet where the user can add rows in between). It would be hard to replace them, but it wouldn't help speeding up the application:
I had replaced all volatile functions by simply writing their results into the cell. I even replaced all VLOOKUPs with their results.
This doesn't seem to be the fix, as there is no change in performance between before and after replacing them.

I have to admit that there is quite a number of cell values that are referred to by other cells which are referred to by other cells ... However, this was also in place before I had moved the VBA functionality into an add-in.

Any other suggestions related to potential root causes?

Can you narrow down exactly what you added between the two versions. You mentioned defined names so I'd start with those.

There were just four additional defined names to identify folder and file name for the add-in file to be copied into the standard add-in folder plus alternate folder and file name in case a user had mapped her/his drive differently.
The total number of defined cell names is 72 (there is an additional approx. 50 macro buttons with defined names). I have applications with many many more defined names that work way faster.

It's not so much the number of names as how they are defined. How/where are those additional 4 names used? And what exactly do you mean by 'buttons with defined names'?

Oh I see, you just mean you changed the button names.
I can't think of another reason offhand for the behaviour you describe if it's not to do with volatile functions or event code. (if it were just a question of formula complexity I would have expected the behaviour to be there before, although if you have moved lots of UDFs to the add-in, that might slow things down a little.)

After all of your suggestions, I guess I found the reason for the slow file:
It seems to be linked pictures!
I stripped my file down by eliminating all remaining macros - performance was still bad.
I deleted a number of linked pictures - and there we go: performance is back to normal!

One of the sources for linked pictures was a relatively heavy graph on that particular file, which explains why one doesn't see that poor performance in other files which use less heavy graphs!

Thank you all, especially RomperStomper, for your advise!
I learnt a lot about optimizing my applications from that, which will certainly help me in future applications!

Mike

I had completely forgotten about that as a potential cause. A few years ago, I had a single linked picture in a workbook to see what was going on in a few cells on another sheet, and it completely whacked performance.

Going to write that down, right now ...