Fix a 3 second delay when entering text in any cell

lightmaster

New Member
Joined
Feb 7, 2018
Messages
13
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?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
Macro recorder is very dangerous in the wrong hands. Volatile functions too. Good look in your endeavours.

WBD
 
Upvote 0
Trying to figure out how to write an alternative for
Code:
=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.
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
Or instead of every time a sheet is activated, if it suits, just do it once to each sheet when the workbook opens
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top