PDA

View Full Version : [SOLVED] Application.ScreenUpdating



austenr
06-15-2005, 11:05 AM
Is it necessary to set this to "True" at the end of the macro? Isn't it set to "True" by default at the end of the macro? I see this a lot and read that it is not necessary to set it to "True". :dunno

Zack Barresse
06-15-2005, 11:15 AM
It's not reset in Excel 2002 and up, so you must do so manually.

mdmackillop
06-15-2005, 11:16 AM
Maybe not, but the beginners may assume this is true for all such Application settings, and leaving EnableEvents = False could cause some problems. I'll continue with resetting to true, needed or not.

Bob Phillips
06-15-2005, 11:24 AM
Maybe not, but the beginners may assume this is true for all such Application settings, and leaving EnableEvents = False could cause some problems. I'll continue with resetting to true, needed or not.

Absolutely agree. Even if these things do get reset

don't assume some future release will maintain this
don't assume some future release or an SP won't introduce a bug that blows it
can cause all sorts of debugging problems if someone thinks that this is the reason why the code doesn't work, or spends hours looking for it
It is so little effort, why would you not?

MWE
06-15-2005, 11:26 AM
Maybe not, but the beginners may assume this is true for all such Application settings, and leaving EnableEvents = False could cause some problems. I'll continue with resetting to true, needed or not.
I second this. It is all too common to find code that works wonderfully in the environment in which it was developed, but has subtle problems in other environments (and Murphy's Law states that the problem will not be found by the compiler). This example is a particularly good example because the automatic return to default states does not occur in newer versions.

Has anyone developed a "new version" checker for VBA code that checks for this kind of improvement/feature?

austenr
06-15-2005, 12:30 PM
This is something I just read and wanted some feedback from everyone. Thanks. Since I sometimes make macros for older versions of Excel (97) I was just curious. :thumb

Zack Barresse
06-15-2005, 12:34 PM
Great question Austen!!! :yes

Cyberdude
06-16-2005, 03:24 PM
For what it's worth, I use ScreenUpdating and Calculations a lot, and it's been my observation that:
1. In a macro that has not been called, the value of ScreenUpdating defaults to True when the macro begins execution.
2. When a called macro begins execution, the value is whatever it was in the calling macro at the time the called macro was invoked.
3. When a called macro returns control to a calling macro, the value is whatever it was when the called macro exited.
4. When a macro that has not been called terminates execution, the value reverts to True.

For Calculations, all the above apply EXCEPT, number 4.
It does not revert to xlAutomatic when the macro exits. So all macros running after that one will inherit the value that was set by the last macro that ran.

Ken Puls
06-16-2005, 09:27 PM
Heya guys!

I just wanted to chime in and say that I pretty much agree with the general concensus of not leaving too much to chance. I'm also a firm believer that just because code can be assumed to do certain thins or revert to certain ways doesn't mean that we shouldn't explicity state it. (Explicit to me means more than just dimensioning my variables.)

Sid, I agree with points 2 & 3 of what you have. This would seem to be true from my experience. With regards to 1 though... I guess that it's probably a safe assumption, but I wouldn't stake my reputation on it. For the sake of adding one line of code, I wouldn't risk it. As Bob mentioned, you never know when a future version or SP will blow the doors off it.

Then there's 4... as Zack mentions, from 2002 on, it doesn't appear to be that way. While I haven't tested it to prove it to myself, I did bail on a macro the other day in 2003 without resetting the screenupdating to true, and Excel acted weird for me. I didn't delve into it too far as I knew immediately what I did, so fixed it right away.

For reference, I've taken to using the following procedures which I load at the beginning and end of all my macros:


'Global variable to record user's calculation state
Dim xlCalcState as Long

Sub Environ_SpeedBooster()
'Macro Purpose: To set application properties to maximize speed
xlCalcState = Application.Calculation
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
End Sub

Sub Environ_RestoreSettings()
'Macro Purpose: To restore application properties to user settings
With Application
.ScreenUpdating = True
.Calculation = xlCalcState
.StatusBar = False
End With
xlCalcState = 0
End Sub


I haven't entered the EnableEvents part as I haven't needed it yet.

Cheers!

austenr
06-16-2005, 09:44 PM
Ken,

I know there is a way to load Option Explicit automatically when you insert a module, user form etc. Can the other settings be loaded as you create these as well or do you have to manually do it?

Ken Puls
06-16-2005, 09:48 PM
Hi Austen,

You mean like:
Option Compare Text
Option Base 1
etc...?

From strictly an unmodified VBA interface, I don't think so. I'm not sure if you can get an addin or tools to add those in automatically though. From my perspective, though, I use them so infrequently (if ever) that I don't know that I'd find too much use for it anyway. :dunno

Could be corrected on this though...

sheeeng
06-16-2005, 10:49 PM
Is it necessary to set this to "True" at the end of the macro? Isn't it set to "True" by default at the end of the macro? I see this a lot and read that it is not necessary to set it to "True". :dunno

It is better to set it back to "True" at the end of macro. :thumb Some time the application is not properly closed when the macro encounter runtime error. This may cause undesirable results.

Do keep it a custom to set it back to "True" at the end.:thumb

sheeeng
06-16-2005, 10:51 PM
Hi Austen,

You mean like:
Option Compare Text
Option Base 1
etc...?

From strictly an unmodified VBA interface, I don't think so. I'm not sure if you can get an addin or tools to add those in automatically though. From my perspective, though, I use them so infrequently (if ever) that I don't know that I'd find too much use for it anyway. :dunno



I was wondering also bout this question. Is there anyway to automatically set all code begins with Option Explicit and Option Base 1?

Bob Phillips
06-17-2005, 01:49 AM
I was wondering also bout this question. Is there anyway to automatically set all code begins with Option Explicit and Option Base 1?

Option Explicit is easy, and a must IMO. In Tools>Options>Editor in the VB IDE, check the 'Require variable declaration' box.

Others are manual AFAIK.

sheeeng
06-17-2005, 09:15 AM
Option Explicit is easy, and a must IMO. In Tools>Options>Editor in the VB IDE, check the 'Require variable declaration' box.

Others are manual AFAIK.

Sorry. What is IMO and AFAIK? :doh:

Ken Puls
06-17-2005, 09:16 AM
"In My Opinion" and "As Far As I Know"

;)

sheeeng
06-17-2005, 09:17 AM
"In My Opinion" and "As Far As I Know"

;)



Thanks a lot. :beerchug:

MWE
06-17-2005, 01:09 PM
It is better to set it back to "True" at the end of macro. :thumb Some time the application is not properly closed when the macro encounter runtime error. This may cause undesirable results.

Do keep it a custom to set it back to "True" at the end.:thumb
One way around this problem generally is to make sure you have "On Error ..." statements for those cases where runtime errors are possible and have a "housekeeping" section at the bottom of the procedure that cleans up things

Zack Barresse
06-17-2005, 02:36 PM
Just to clear things up, this property (EnableEvents) will NOT reset itself in new versions of Excel when going from one routine to another. If you don't believe me, test yourself. Open your VBE and copy/paste this code and run it.


Sub FooFooOnYouOFF()
Application.EnableEvents = False
End Sub

Then run this routine ...


Sub FooFooOnYouAchoo()
MsgBox "EnableEvents = " & Application.EnableEvents
End Sub

Now run these in succession ...


Sub FooFooOnYouON()
Application.EnableEvents = True
End Sub

'again..

Sub FooFooOnYouAchoo()
MsgBox "EnableEvents = " & Application.EnableEvents
End Sub

All routines ...


Option Explicit

Sub FooFooOnYouOFF()
Application.EnableEvents = False
End Sub

Sub FooFooOnYouON()
Application.EnableEvents = True
End Sub

Sub FooFooOnYouAchoo()
MsgBox "EnableEvents = " & Application.EnableEvents
End Sub

What I usually do, since I hate more than anything is to screw up my StatusBar (I know, I'm a little wierd like that) I have a routine I keep in my Personal.xls and I use that as a one-liner at the end of other routines ...



Sub SomeWierdOtherRoutineIMayRun()
'some code
'blah blah
'...
Call ResetMe
End Sub

'... meanwhile, in Personal.xls ...


Sub ResetMe()
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.CutCopyMode = False
.StatusBar = False
'.. whatever else I want, maybe a calculation or whatever
End With
End Sub

Bob Phillips
06-17-2005, 04:20 PM
Sorry. What is IMO and AFAIK?
http://www.ucc.ie/cgi-bin/uncgi/acronym

Norie
06-17-2005, 06:45 PM
International Meteor Organisation

Cyberdude
06-17-2005, 08:32 PM
Regarding my comments earlier, I have run tests on 2002 and 2003 to support what I listed.

I'm quite surprised that there wasn't more discussion of the Calculations = xlManual. Now talk about causing problems if it doesn't get reset ... whew! I've even gone to the trouble to put a Calculations condition checker in my Workbook_Open macros just to make sure. Every once in awhile I'll catch one that's in "xlManual" state, and the logic resets it for me. You sure have to be careful how you use the Calculations = xlManual. It can really speed things up when used correctly, but there are times when you really don't want it to be manual. If you don't realize what's going on, there can be some real surprises. I might add, that no matter how diligent you are about resetting to "xlAutomatic" at macro's end, if you forget after a crash you can be in for some misery.

Ken Puls
06-17-2005, 09:43 PM
Hey Sid,

Just for reference, the routine I posted one up's the calcuation method you suggest (I think anyway! ;) )

Mine actually checks the calculation method the user has set at the beginning, and "bookmarks" it. It then sets it to manual for processing, and sets it back to the user's preference at the end. This way you don't bugger a book which actually should have manual calculations on. (I do actually have some that I only want calculating manually, believe it or no.)

I do totally agree though, that it can be a shocker if you forget to set it back and think you have it going. I actually built a class event at one time to check every workbook when I opened it, as I had to work with my manual book quite a bit. One thing I really don't like is that the calculation is set at an application level, not workbook level. Can be a nasty surprise that! :yes

sheeeng
06-18-2005, 12:16 AM
One way around this problem generally is to make sure you have "On Error ..." statements for those cases where runtime errors are possible and have a "housekeeping" section at the bottom of the procedure that cleans up things


Thanks, MWE. Can you or anyone show me an example for the "On Error" controls? :hi:

Richie(UK)
06-18-2005, 01:51 AM
Thanks, MWE. Can you or anyone show me an example for the "On Error" controls? :hi:
Hi sheeeng,

Using Ken's example routines you would incorporate the 'On Error' into your main routine something like this:


Sub Slow_Macro()
Dim iCounter As Integer
On Error GoTo ErrHandler
Call Environ_SpeedBooster
For iCounter = 1 To 50000 'causes an error
Cells(iCounter, 1).Value = iCounter
Next iCounter
ErrHandler:
Call Environ_RestoreSettings
End Sub

The 'On Error' line tells the VBE to go to the indicated line when an error arises - immediately after this line we have a call to the routine that resets the various settings.

HTH

sheeeng
06-18-2005, 07:04 AM
Hi sheeeng,

Using Ken's example routines you would incorporate the 'On Error' into your main routine something like this:

Sub Slow_Macro()
Dim iCounter As Integer
On Error GoTo ErrHandler
Call Environ_SpeedBooster
For iCounter = 1 To 50000 'causes an error
Cells(iCounter, 1).Value = iCounter
Next iCounter
ErrHandler:
Call Environ_RestoreSettings
End Sub

The 'On Error' line tells the VBE to go to the indicated line when an error arises - immediately after this line we have a call to the routine that resets the various settings.

HTH



Thanks. :hi: You helped me to understand VBA error control. :thumb

Cyberdude
06-18-2005, 11:12 AM
Mine actually checks the calculation method the user has set at the beginning, and "bookmarks" it. It then sets it to manual for processing, and sets it back to the user's preference at the end.

Hey, Ken,
That's interesting. I'm not sure what you mean by "bookmarks", but it sounds like something I did for a long time. I started each macro with two statements: one saved the current Calculations setting (in a variable) and the other saved the current ScreenUpdating setting. Then at the end of the macro, I re-established the "entry settings" using the philosophy of "leave it like you found it". I stopped doing that for some reason ... don't know why. I think I'll start doing it again. :beerchug: