Fix a 3 second delay when entering text in any cell

[lightmaster]

I have a fairly large workbook (60+sheets) that I'm taking over maintaining. Its got a lot of macros in it that control moving data from entry sheets into databases, and collecting that data into report sheets. I've been successful speeding up several sheets that had macros that took 30+ seconds to complete and have gotten them down to 2-3 seconds, but for some reason whenever you enter data into any cell on any sheet, even a brand new sheet that's not referenced by anything else, there's a 2-3 second delay before excel allows you to do anything else. What are some things that I could look for that could be causing this lag, especially when you make a new sheet?

 

[wideboydixon]

I would check for volatile functions as they will trigger on *any* change. Here's a good article:

https://chandoo.org/wp/handle-volatile-functions-like-they-are-dynamite/

WBD

 

[lightmaster]

I would check for volatile functions as they will trigger on *any* change. Here's a good article:

https://chandoo.org/wp/handle-volatile-functions-like-they-are-dynamite/

WBD

I guess the original developer was a big fan of volatile functions, since this Workbook has 1135 of them. I assume this is enough to cause my issues? Guess I'm gonna be spending some time going through and rewriting those cells to use non-volatile functions instead.

Guess I shouldn't be too surprised, since most of the macros look like they were written by the macro recorder... Already having to rewrite a lot of macros that take dozens of lines to select and offset the selection and then copy it.

 

[wideboydixon]

Macro recorder is very dangerous in the wrong hands. Volatile functions too. Good look in your endeavours.

WBD

 

[lightmaster]

Trying to figure out how to write an alternative for

=IFERROR(INDIRECT(BB6&BB$8),0)

BB6 contains a column letter, and bb$8 contains a row number. The value of BB$8 changes based on the value of other cells, while BB6 is has a static value. The result of the function that currently contains INDIRECT() should be the value of the cell reference when combining BB6 and BB$8, or 0 if the cell referenced has an error.

 

[wideboydixon]

If BB6 is static then you can use something like:

=IFERROR(INDEX($C:$C,BB$8),0)

If BB6 is "C" for example.

WBD

 

[lightmaster]

Went through and did a find and replace to replace every volatile function with the word "bad(" so I could see if stopping all volatile functions would improve the speed of data entry. Good news is that it does. Bad news is the original author is very good at writing crazy complex formulas where one cell that contains INDIRECT() references another cell that references another cell, etc. Probably a large part of the input lag, and is very difficult for me to follow exactly how the functions work. At other times though, he'd use an indirect function just to reference some static cells so I can easily replace the indirect function with the cell reference that is typed into the other cell.

And yes, I'm more just ranting about my frustrations here, lol.

 

[Fazza]

sounds like simply slow formulas. nothing else

though you might look too at VBA events & put in some temporary Stop commands at the start of their code. Then single step through in debug mode to see if there are cascading events

and check the used ranges are all OK on all sheets
and page break visibility
and conditional formatting
and all defined names
and any links (to other files)
just the normal culprits/suspects

 

[lightmaster]

Already created a macro to call before each other macro that disables page break visibility, events, screenupdating and another macro to call at the end that enables all them again except the page break visibility. Going through the easier to fix volatile functions right now, like replacing all 1100 TODAY()s with worksheet_activate macros that hard codes the current date each time the sheet is switched to.

 

[Fazza]

Or instead of every time a sheet is activated, if it suits, just do it once to each sheet when the workbook opens