Screen 'Refreshing' - Too Much Code???

I have a large spreadsheet, with alot of VB data validation (under a
Worksheet_SelectionChange sub) and hiding/unhiding of rows (under a
Worksheet_Change sub) based upon user input. Whenever a cell entry is made,
the screen flickers alot, as if it is going through too much code - it is not
a smooth (or quick) transition from one cell to another. The code module
for this worksheet is 55KB; I have read that you shouldn't get over 64KB.
Could the size of the module be the problem? I've put in
Application.ScreenUpdating = False, but that doesn't help, and tried using
alot of target.address stuff in the Worksheet_Change sub...but still have the
problem. If I remove the Worksheet_Change sub most of the problem goes
away....however, I need the functionality provided so that is not a
resolution. Would appreciate any thoughts/guidance on what I may be doing
wrong or how I could improve this. Thanks so much....

The screen flicker is most likely the result of one of two things.

Overusing Application.Screenupdating. In any thread of execution you should
only turn it off once at the beginning and once at the end. Often I see
people turning off screen updating and then calling a procedure which proceed
to turn it off and on again before returning to the main calling procedure.
Every time the Application.Screenupdating = true is called the screen will
refresh. If this is done in a loop then there will be a lot of flicker.

The other thing to look for in your event code would be to see if the
procedrues are being called recursively. For example if change code changes a
cell then the change code is called again by virtue of the change made by
your code. This can get itself into a loop that can run over and over again.
Each time it will toggle the screenupdating and you will get flicker. To
avoid this use

Application.enableevents = false / true

When you use this be sure to use an error handler or you can run yourself
into real problems where no events will fire
--
HTH...

Jim Thomlinson


"Paige" wrote:

> I have a large spreadsheet, with alot of VB data validation (under a
> Worksheet_SelectionChange sub) and hiding/unhiding of rows (under a
> Worksheet_Change sub) based upon user input. Whenever a cell entry is made,
> the screen flickers alot, as if it is going through too much code - it is not
> a smooth (or quick) transition from one cell to another. The code module
> for this worksheet is 55KB; I have read that you shouldn't get over 64KB.
> Could the size of the module be the problem? I've put in
> Application.ScreenUpdating = False, but that doesn't help, and tried using
> alot of target.address stuff in the Worksheet_Change sub...but still have the
> problem. If I remove the Worksheet_Change sub most of the problem goes
> away....however, I need the functionality provided so that is not a
> resolution. Would appreciate any thoughts/guidance on what I may be doing
> wrong or how I could improve this. Thanks so much....

The screen flicker is most likely the result of one of two things.

Overusing Application.Screenupdating. In any thread of execution you should
only turn it off once at the beginning and once at the end. Often I see
people turning off screen updating and then calling a procedure which proceed
to turn it off and on again before returning to the main calling procedure.
Every time the Application.Screenupdating = true is called the screen will
refresh. If this is done in a loop then there will be a lot of flicker.

The other thing to look for in your event code would be to see if the
procedrues are being called recursively. For example if change code changes a
cell then the change code is called again by virtue of the change made by
your code. This can get itself into a loop that can run over and over again.
Each time it will toggle the screenupdating and you will get flicker. To
avoid this use

Application.enableevents = false / true

When you use this be sure to use an error handler or you can run yourself
into real problems where no events will fire
--
HTH...

Jim Thomlinson


"Paige" wrote:

> I have a large spreadsheet, with alot of VB data validation (under a
> Worksheet_SelectionChange sub) and hiding/unhiding of rows (under a
> Worksheet_Change sub) based upon user input. Whenever a cell entry is made,
> the screen flickers alot, as if it is going through too much code - it is not
> a smooth (or quick) transition from one cell to another. The code module
> for this worksheet is 55KB; I have read that you shouldn't get over 64KB.
> Could the size of the module be the problem? I've put in
> Application.ScreenUpdating = False, but that doesn't help, and tried using
> alot of target.address stuff in the Worksheet_Change sub...but still have the
> problem. If I remove the Worksheet_Change sub most of the problem goes
> away....however, I need the functionality provided so that is not a
> resolution. Would appreciate any thoughts/guidance on what I may be doing
> wrong or how I could improve this. Thanks so much....

other possibilities:

1. You could hide Excel during the procedure (extreme, huh!)

2. You could move your event handler code into a class module, for example,
name "CAppEvents". Then you could use two subs in a standard module to a)
turn event handling on <eg: Sub StartEventHandling()>; b) turn event
handling off <eg: Sub StopEventHandling()>. Now you can control when you want
it and when you don't, allowing you to "toggle" it for procedures that would
ordinarily fire these events, but you'd rather they didn't. This avoids the
recursive activity Jim mentioned in his post.

I prefer the second suggestion because of the added control. Having the two
subs to turn it off/on is also a bonus when doing debugging or development on
the project. Making changes to code and recompiling can cause the event
handler to stop working, even when the changes have nothing to do with
CAppEvents.

I hope this was additionally helpful.
Regards,
GS

Not to question your thought process but are you suggesting to hide the
application whenever the validation code runs? That would be very
disconcerting... Not to mention what happens if something goes wrong in the
code and it dies. The application is still open, but you can not get it back
(easily)... Any other spreadsheet syou have open will be trapped in the
application. New sheets will open in the hidden instance... Generally not
good.

As for your second suggestion I do not see the value in the class module
(Unless I am missing something). You can enable and disable the events with
Application.enableEvents. What value does your class have above and beyond
that...
--
HTH...

Jim Thomlinson


"GS" wrote:

> other possibilities:
>
> 1. You could hide Excel during the procedure (extreme, huh!)
>
> 2. You could move your event handler code into a class module, for example,
> name "CAppEvents". Then you could use two subs in a standard module to a)
> turn event handling on <eg: Sub StartEventHandling()>; b) turn event
> handling off <eg: Sub StopEventHandling()>. Now you can control when you want
> it and when you don't, allowing you to "toggle" it for procedures that would
> ordinarily fire these events, but you'd rather they didn't. This avoids the
> recursive activity Jim mentioned in his post.
>
> I prefer the second suggestion because of the added control. Having the two
> subs to turn it off/on is also a bonus when doing debugging or development on
> the project. Making changes to code and recompiling can cause the event
> handler to stop working, even when the changes have nothing to do with
> CAppEvents.
>
> I hope this was additionally helpful.
> Regards,
> GS

Hi Jim,

Well, as I stated in my post, the hiding the application is extreme. I
intentionally did not expand on that for the very reasons you cite here.
Probably, it would have been better had I not mentioned it, ..not knowing
anything about the OP, or his use of error handling techniques. -I accept
your objection and stand admonished!

As for the use of a class module for handling application events:
I guess the reason I prefer using this method is for the same reasons you
object to hiding the application, and the caution you gave at the end of your
post. What if something goes wrong with events disabled. I never have to
worry about that happening because Excel's events are always intact.

Also, in the case of an add-in, it works on any open workbook.

Regards,
Garry


"Jim Thomlinson" wrote:

> Not to question your thought process but are you suggesting to hide the
> application whenever the validation code runs? That would be very
> disconcerting... Not to mention what happens if something goes wrong in the
> code and it dies. The application is still open, but you can not get it back
> (easily)... Any other spreadsheet syou have open will be trapped in the
> application. New sheets will open in the hidden instance... Generally not
> good.
>
> As for your second suggestion I do not see the value in the class module
> (Unless I am missing something). You can enable and disable the events with
> Application.enableEvents. What value does your class have above and beyond
> that...
> --
> HTH...
>
> Jim Thomlinson
>

Thanks everyone! Will work on getting this corrected....have a great
weekend...


"GS" wrote:

> Hi Jim,
>
> Well, as I stated in my post, the hiding the application is extreme. I
> intentionally did not expand on that for the very reasons you cite here.
> Probably, it would have been better had I not mentioned it, ..not knowing
> anything about the OP, or his use of error handling techniques. -I accept
> your objection and stand admonished!
>
> As for the use of a class module for handling application events:
> I guess the reason I prefer using this method is for the same reasons you
> object to hiding the application, and the caution you gave at the end of your
> post. What if something goes wrong with events disabled. I never have to
> worry about that happening because Excel's events are always intact.
>
> Also, in the case of an add-in, it works on any open workbook.
>
> Regards,
> Garry
>
>
> "Jim Thomlinson" wrote:
>
> > Not to question your thought process but are you suggesting to hide the
> > application whenever the validation code runs? That would be very
> > disconcerting... Not to mention what happens if something goes wrong in the
> > code and it dies. The application is still open, but you can not get it back
> > (easily)... Any other spreadsheet syou have open will be trapped in the
> > application. New sheets will open in the hidden instance... Generally not
> > good.
> >
> > As for your second suggestion I do not see the value in the class module
> > (Unless I am missing something). You can enable and disable the events with
> > Application.enableEvents. What value does your class have above and beyond
> > that...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
>