When exactly DOES calculation need to be auto?

[9tanstaafl9]

Many of the posts suggest that unless it needs to be on for some reason, that you should turn calculation to manual at the beginning of your code and back to automatic at the end. Aside from trial and error, are there some basic guidelines for determining when exactly you DO need to have it set to automatic?

Also, if you have one macro that runs several smaller macros, do you need to turn off calculation for each of them, or would putting it at the beginning and the end of the main macro be sufficient?

Sorry if this is something I should've been able to figure out from the just searching, but I couldn't seem to narrow it down enough to find what I needed.

Thanks in advance!

 

[markjarvisltd]

My understanding is that these two lines speed up the execution of your code.
This is done by not wasting processor time recalculating and redrawing the screen after each change initiated by your code.
In most cases this would be in the outer layer of your code unless you have decision or entry points which may be dependant on calculations being up todate. It is quoted by the Gods of excel as good practice, so all my code starts and ends as such.

Application.ScreenUpdating = False
Application.Calculation = xlManual

Code

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

 

[DonkeyOte]

the secret is in the code...

Application.

implies the variable will apply to the entire application not merely a given workbook or a given sub routine

You simply need to be careful that when forcing to manual you ensure you're recalcualting when and where necessary... for ex if say you post some values to one sheet that in turn impact another sheet and you intend to use the other sheet values later in your code you must recalculate those sheets (possible both, maybe just the second sheet pending scenario) in order for your code to work (assuming you've forced into manual mode).

I will leave this for one of the MVP & MVP-esque guys to give the official low-down here but the above is a fools guide.

And yes -- turning off ScreenUpdating can/does have dramatic impact on performance as XL will not try and redraw the screen with updates as the code progresses.

I learnt myself that ScreenUpdating will by default revert to True on completion of routine (ie you don't have to revert to True at end) however most people code the True at end of routine out of habit (best practice)

 

[9tanstaafl9]

Thank you! Aside from calling me a fool (which I knew already) you gave me the exact information I needed. Some things may seem truly obvious, but when you are really new to this (still on my first VBA project) you sometimes need it all spelled out.

BTW, how do I indicate that my question was answered? Does someone else do that?

Thanks again!

the secret is in the code...

Application.

implies the variable will apply to the entire application not merely a given workbook or a given sub routine

You simply need to be careful that when forcing to manual you ensure you're recalcualting when and where necessary... for ex if say you post some values to one sheet that in turn impact another sheet and you intend to use the other sheet values later in your code you must recalculate those sheets (possible both, maybe just the second sheet pending scenario) in order for your code to work (assuming you've forced into manual mode).

I will leave this for one of the MVP & MVP-esque guys to give the official low-down here but the above is a fools guide.

And yes -- turning off ScreenUpdating can/does have dramatic impact on performance as XL will not try and redraw the screen with updates as the code progresses.

I learnt myself that ScreenUpdating will by default revert to True on completion of routine (ie you don't have to revert to True at end) however most people code the True at end of routine out of habit (best practice)

 

[DonkeyOte]

sorry, not my intention to come across in that manner ! we all start somewhere and I am continually learning new tricks here so I'm the last to pretend to know everything!

if you have a lot of indendent routines (ie not invoked via one another) you may find it's actually less aggravation to create common routines that you can reuse to perform repetitive tasks... for ex I quite often have a module with the following within:

Sub APP_CALC(ty As Integer)
Select Case ty
    Case 0
        Application.Calculation = xlCalculationManual
    Case 1
        M1 = MsgBox("App Calculation is set to Manual." & vbLf & vbLf & _
                "Do You Wish to Switch to Automatic", vbYesNo, "Manual or Auto ?")
        If M1 = vbYes Then
            Application.Calculation = xlCalculationAutomatic
        End If
End Select
End Sub

Sub APP_SCREEN(ty As Integer)
Select Case ty
    Case 0
        Application.ScreenUpdating = False
    Case 1
        Application.ScreenUpdating = True
End Select
End Sub

so if I have lots of independent routines all I need do at the beginning of my module is add:

Call APP_CALC(0)
Call APP_SCREEN(0)

and to reverse at the end:

Call APP_CALC(1) 
Call APP_SCREEN(1)

though in the case of calculation I will also get the end-user to confirm they want to go to back to Auto Calc (it may be the end user will actually want to remain in manual mode) -- this way they get the choice...


as for marking thread as closed/answered - just leave as is.

 

[9tanstaafl9]

Thanks again, more useful info! I was getting tired of writing that bit over and over again. Also, I should've put a smiley at the end of my last post, I meant to be funny, I didn't think poorly of you. I've been told I'm a bit vulcan, and my sense of humor doesn't convey in text.

 

[DonkeyOte]

let's just hope you don't have the ears...

should add that the CALL APP_SCREEN(1) should precede the APP_CALC(1) call... oops