SethEden


I've been developing this really massive VBA Excel Application as part of a government contract and it's gotten to be about 55,000 lines of code, optimized down from more than double that of course.

Everything seems to be working nearly perfect and I think I've even solved my problem with the call to Application.ScreenUpdating, but I'm not really sure why, and I couldn't find documentation relating to the problem I found or the work around that I developed.

There are a number of things that I discovered.

1.) It seems that making a change to any cell on any sheet in VBA code will cause the application to change the users view to that sheet. This is the reason for the property Application.ScreenUpdating to even exist in the first place so that programmers can have control over what the user sees.

2.) Every time code execution enters a new module the property Application.ScreenUpdating seems to get re-set so setting any values to any sheets inside a module that the user isn't supposed to see whould set the property to false before making the changes to the sheet.

3.) If I run some calculations in a module and I need to update sheets based on the new calculated values then if I want to user to see the value in the cell change for the sheet the user is currently active on then I should set the property to True, then change the cell, then set it back to false before making additional changes on other sheets....My question is this: If in this case the code in the sub is executing from another sheet and I change the property to update the sheet the user is active on then will it cause a flicker because the code is executing on a different sheet

For example: The user is working on Sheet1 and makes a change to some cell triggering a Sheet_Change. The Sheet_Change calls a sub/function on Sheet2. Sheet2 does some calculations and needs to update values on Sheet2 and Sheet1. Would setting the Application.ScreenUpdating Property in code from Sheet2 cause a flicker/ or visably active sheet to change to Sheet2 since the property change was executed from Sheet2 even if, inside the Property Set Block, i.e. between Application.ScreenUpdating = True and Application.ScreenUpdating = False the code is only accessing Sheet1. A similar case and more common would be to simply turn the property back to true at the end of the module since returning from the function means going back to code execution on Sheet1, in which case the user should see any additional changes.

My experience is that the above example is true, but there is no documentation about this behavior at all!

4.) If you are in debug mode, and stepping through the code manually the property is ALWAYS set to true. This makes it nearly impossible to find flickering or other visible active sheet changes that the user would be annoyed by.

5.) I've read in a few places while doing research on this that some environments and versions of Excel reset the property at the beginning of every Macro which would fit with my experience, but again there was no documentation that I could find about this.

Someone should probably update the documentation to reflect some of this more advanced behavior.

Cheers

Seth





Re: Application.ScreenUpdating Problems

Suhel Khan


Mr. Seth How are you

I'm facing the similar kind of problem. I researched over internet but I didn't get any solution yet. In my case Application.Screenupdating is reseting True automatically and it's happening when I'm using code through a Menubar button when I calling my macro with .OnAction = "MyMacro" method of a control. When I use it from Immediate Window or with Run Command its working perfectly.

If someone know the solution please share with us. So we can move further in our Project. Here is a code:

Code Snippet

Set MBarSubCtl = MBarCtl.Controls.Add(Type:=msoControlButton)
With MBarSubCtl

.Style = msoButtonIconAndCaption
.Caption = "Remove all &Numeric in Selection"
.FaceId = 11
.OnAction = "remNumbers()"
End With


Sub remNumbers()
Application.ScreenUpdating = False
msgbox Application.ScreenUpdating
exSp = "RNUM"
frmPrgBar.lblStatus.Caption = "Do You want to remove all Numeric Characters (Numbers) in Selection "
frmPrgBar.Show vbModal
Application.ScreenUpdating = True
End Sub


msgbox Application.ScreenUpdating always showing True when i run code on click of button. But from Immediate Window it's showing False.




Suhel Khan







Re: Application.ScreenUpdating Problems

SethEden

From what I've learned, the last line of your routine "remNumbers()" sets the state back to True.

Try removing that line of code from the Sub routine and put it just after you call the function.

eg:

Code Snippet

WithMBarSubCtrl

.Style = msoButtonIconAndCaption

.Caption = "Remove all &Numeric in Selection"

.FAceId = 11

.OnAction = "remNumbers()"

Application.ScreenUpdating = True

End With

Then in your Macro would look like this:

Code Snippet

Sub remNumbers()

Application.ScreenUpdating = False

msgbox Application.ScreenUpdating

exSp = "RNUM"

frmPrgBar.lblStatus.Caption = "Do You want to remove all Numeric Characters (Numbers) in Selection "

frmPrgBar.Show vbModal

End Sub

So that may help, from what I've researched, but it probably won't affect the state of the msgbox at the beginning of the routine....I'm not sure why, and it doesn't really answer the question of course.....so I'd still like to hear from someone about this.

Thanks for the additional post and example!

Cheers

Seth







Re: Application.ScreenUpdating Problems

SethEden

Bump....Anybody

Seth






Re: Application.ScreenUpdating Problems

Cringing Dragon

Firstly - using the VBA editor for Excel 2003, I have noticed that when stepping through the code, hovering over "Application.ScreenUpdating" always displays True, even when it's been declared False. This is probably because, as you point out, when in debug mode the ScreenUpdating setting is ignored (behaves as True even when set to False) - I assume that's deliberate so that you can see what you're code is doing. But you can check what the currently declared setting is by add a line setting a variable to Application.ScreenUpdating (eg after turning ScreenUpdating to false). Step through to just past that line. If yours behaves the same way mine does, then when you hover your cursor over "Application.ScreenUpdating" - it falsely displays "True", but hover your cursor over the variable and it will correctly show False. So in this case you can't rely on the VBA Editor's ToolTip to tell you what the current value is. As a workaround, when troubleshooting, you can keep setting that variable at intervals throught the code to track the value of ScreenUpdating.

I'm pretty sure any screen update causes a flicker, even when other sheets are not involved. Try with a single sheet workbook and code only affecting that sheet. It will probably still flicker when a screen update is triggered (mine does).

Secondly, some things I've tried to reduce the flickers (which means reducing the screen updates).

I often turn ScreenUpdating off at the start of a sub and back on at the end (and set Calculation to Manual as well). But when that sub is called from another sub, I don't want them turned back on again at the end of the called sub. So I got into the habit of putting the following code at the beginning and end of the subs, instead of the simple true at the start and false at the end (I do a similar thing with calculate). NB I use this any time I want to turn ScreenUpdating off or Calculate to manual, not just at the start and end of subs.

Code Block

Sub example()

bScrUpdate = Application.ScreenUpdating
If bScrUpdate = True Then Application.ScreenUpdating = False
' ...

' ... code here

' ...

If Not Application.ScreenUpdating = bScrUpdate _
Then Application.ScreenUpdating = bScrUpdate
End Sub

That means that at the end of the sub it's set back to what it was at the beginning (not simply reset to True), and the If statements stop it from re-applying unnecessarily. Despite this, as you have noted, when code switches subs the screen is updated (I get this even when the subs are in the same module). But if you've put the simple "Application.ScreenUpdating = True" at the end of the sub, you'll get two redraws (flickers) instead of just the one.

SethEden wrote:

3.) If I run some calculations in a module and I need to update sheets based on the new calculated values then if I want to user to see the value in the cell change for the sheet the user is currently active on then I should set the property to True, then change the cell, then set it back to false before making additional changes on other sheets....My question is this: If in this case the code in the sub is executing from another sheet and I change the property to update the sheet the user is active on then will it cause a flicker because the code is executing on a different sheet

For example: The user is working on Sheet1 and makes a change to some cell triggering a Sheet_Change. The Sheet_Change calls a sub/function on Sheet2. Sheet2 does some calculations and needs to update values on Sheet2 and Sheet1. Would setting the Application.ScreenUpdating Property in code from Sheet2 cause a flicker/ or visably active sheet to change to Sheet2 since the property change was executed from Sheet2 even if, inside the Property Set Block, i.e. between Application.ScreenUpdating = True and Application.ScreenUpdating = False the code is only accessing Sheet1. A similar case and more common would be to simply turn the property back to true at the end of the module since returning from the function means going back to code execution on Sheet1, in which case the user should see any additional changes.

Try setting ScreenUpdating to true, then immediately false again just after changing the cell in question (you might also need to Activate the appropriate sheet just before the ScreenUpdating to make sure that's the one the user will see). That seems to refresh the screen without unnecessary flickers (it will still flicker when it updates, but only once).

If the user is looking at a form rather than a sheet, then you can make the form redraw by using the .Repaint method, which should work even with ScreenUpdating off. So far as I know, there isn't an equivalent of this for a sheet, but if anyone else knows of one, please share it with us. Word has a ScreenRefresh method, but again I don't think there's an Excel equivalent.




Re: Application.ScreenUpdating Problems

Mezhick

Hi!

I had not met this problem. But maybe you should try Application.EnableEvents = False Then the environment would not change its inner properties at all.






Re: Application.ScreenUpdating Problems

SethEden

Thank you so much! That really helps me understand what is going on a lot more!!!!

To bad Excel doesn't have a repaint function for the sheets. You'd think Microsoft would have been smart enough to make one, but I guess not.

I like your suggestion of using the temporary varable, that looks nice.

Hopefully someone else going through the same problem will find this topic useful! <grin>

Cheers!

Seth