Slow reaction on cell changes

**romperstomper** · 08-25-2009, 04:23 AM

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?

**Mike1001** · 08-25-2009, 12:10 PM

Thanks for replies, guys.

I would tend to say that, no, it is not event code driven, as I had set EnableEvents = false and the same behaviour continues.

As I had said, I had even removed the add-in, and the slow motion continued.

There are dozens of macros in the workbook, but these are all just small pieces of code which do nothing but calling the macros in the add-in.
Two "bigger" ones (no more than 50 lines of code) just make sure the add-in is loaded and the other one is the relay function that calls the macros in the add-in and hands over arguments.

RomperStomper, when the problem is related to complex formulas (which, I have to admit, are contained in this workbook), would one see a delayed reaction even when one changes cells that are not referred to by any other cell?

What I found out today is that the "new" file with not much user data entered into it has normal speed, but as you enter more data, it becomes slower.

What drives me crazy is that I really didn't change a lot in spreadsheets at all when I came up with that add-in (just a few user defined cell names on top of those already existing to ensure macros find the correct data), compared to the privious version without add-in and without slow motion.

What I have seen once in a while is that 'Ready' and 'Calculate' are shown in the status bar at the same time.

Does that give any of you a hint what the problem could be?

Mike

**romperstomper** · 08-25-2009, 12:15 PM

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).

**Mike1001** · 08-25-2009, 12:37 PM

Yes, this had been a problem while ago, where I had 'today()' contained in about 1500 formulas, but I had eliminated that issue by creating a user defined cell called TDay which is loaded by a workbook_open macro with today's date ('8/25/09', not '=today()') and replaced 'today()' with TDay in all formulas.
I have quite a number of 'vlookup' calculations across sheets; would you consider those volatile?

**Mike1001** · 08-25-2009, 12:48 PM

I just check the link about volatile functions you provided, RomperStomper. This is very useful information for me - thank you!
I just noted that I have about 40 cells that contain the 'cell()' function. Would you think that number would slow that application down?

Anyways, I now have something to chew on! I will have to study that for a while and get back to you with results.

I do appreciate your qualified and quick help!

Best regards,
Mike