Worksheet Calculate Event slows down workbook

Greetings,

I have the following worksheet code for displaying a hidden object if there are any formula errors.

If I run any macro (or show a userform) in the workbook, the screen flickers slightly (even though screenupdating is turned off) and the workbook slows down. Using the calculation timers from MS support, I found that the full workbook calculate time was 0.20 secs. If I eliminated the below code, the full workbook calculate time was 0.09 secs. Also, I did not see the screen flicker.

Is this normal when using the workbook calculate event? Any guidance would be appreciated.

Many thanks
Asha

Please Login or Register  to view this content.

It's not surprising that adding the code causes the calculate time to increase, as the code takes a small but finite time to run.

Also, it is likely that the screen flickers not because you turn ScreenUpdating off but because you turn it back on again. Obviously this is a necessary step, however when you turn it back on the screen needs to update in case soemthing has changed and so you see the flicker

Is it possible for you to leave screen updating on unless you are changing something? For example if the shape is visible and is going to stay visible then don't turn screen updating off and on. For that matter, do you need to turn screen updating off at all, since you are only making the one visual change and not running a lot of other code?

Why so complicated ?

Please Login or Register  to view this content.

A msgbox doesn't block an application, it only delays execution until the user clicks on a button.

Please Login or Register  to view this content.

This shows the popup for 3 seconds.

I'd just add that in my experience the Popup method is unreliable in terms of dismissing itself.

Originally Posted by asha3010

What I concluded (hopefully correct!) is that since my workbook was in autocalucation mode, clearing existing data / adding new data (which is what the macro / userform did) triggered full recalculation which turned on the screenupdating & hence caused the flickering.

I think your conclusion is correct, and I'm glad I could help.

One thought:
If the code only applies to one sheet, how about starting with "If ActiveSheet.Name="Sheet27" Then ..."

In VBA it isn't necessary to 'navigate between sheets' to update data.
Besides you can show msgbox/popup whenever you want:

Please Login or Register  to view this content.