Ask a new question

Application.Calculation = xlCalculationAutomatic

Sub monte()

I am working on a model for forecasting. I don't actually understand what the implication is for Application.Calculation = xlCalculationAutomatic?

What is the calculation difference between Application.Calculation = xlCalculationAutomatic and Application.Calculation = xlCalculateManual?

Apparently it calculates one more time after the cycle. The main impact of the code is for round formula. Can anyone tell me if there is special calculation under Application.Calculation = xlCalculateAutomatic for round formula?

Thanks,

Bob






Application.Calculation = xlCalculateManual

Dim x               As Integer
Dim MyTimer         As Double

For i = 13 To 1012
    
    If (i - 12) Mod 25 = 0 Then
        Application.StatusBar = "Progress: " & i - 13 & " of 1000: " & Format(i / 1000, "Percent")
    End If
    
    calculate
    
    Worksheets("Datasheet").Cells(i, 13) = Worksheets("Datasheet").Cells(12, 10)
    Worksheets("Datasheet").Cells(i, 14) = Worksheets("Datasheet").Cells(13, 10)
    Worksheets("Datasheet").Cells(i, 15) = Worksheets("Datasheet").Cells(14, 10)
    Worksheets("Datasheet").Cells(i, 16) = Worksheets("Datasheet").Cells(15, 10)
    

    
Next i

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Ok I understood the difference.

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Sub monte()

I am working on a model for forecasting. I don't actually understand what the implication is for Application.Calculation = xlCalculationAutomatic?

What is the calculation difference between Application.Calculation = xlCalculationAutomatic and Application.Calculation = xlCalculateManual?

Apparently it calculates one more time after the cycle. The main impact of the code is for round formula. Can anyone tell me if there is special calculation under Application.Calculation = xlCalculateAutomatic for round formula?

Thanks,

Bob






Application.Calculation = xlCalculateManual

Dim x               As Integer
Dim MyTimer         As Double

For i = 13 To 1012
    
    If (i - 12) Mod 25 = 0 Then
        Application.StatusBar = "Progress: " & i - 13 & " of 1000: " & Format(i / 1000, "Percent")
    End If
    
    calculate
    
    Worksheets("Datasheet").Cells(i, 13) = Worksheets("Datasheet").Cells(12, 10)
    Worksheets("Datasheet").Cells(i, 14) = Worksheets("Datasheet").Cells(13, 10)
    Worksheets("Datasheet").Cells(i, 15) = Worksheets("Datasheet").Cells(14, 10)
    Worksheets("Datasheet").Cells(i, 16) = Worksheets("Datasheet").Cells(15, 10)
    

    
Next i

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Hi Bob,

You could profitably look at Charles Williams' MSDN page entitled Excel 2010 Performance: Improving Calculation Performance at:


In this regard, See also Chip Pearson at:



===
Regards,
Norman


1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Application.Calculation = xlCalculationManual
stops Excel from auto calculating any cells in the workbook.

For example, if there is cell e.g. R1, referring to Worksheets("Datasheet").Cells(i, 13),
when Worksheets("Datasheet").Cells(i, 13) is updated, cell R1 won't be updated.
In such case, in order to force R1 to update, you need to use a line of macro like this:

Worksheets("Datasheet").calculate
or
Worksheets("Datasheet").Range("R1").calculate



Application.Calculation = xlCalculationManual is often used to speed up loops by manually controlling the calculations of Excel. A common application is like this:

For i = 1 to 10
    ' do something here, e.g. updating some cells
    Activesheet.calculate
Next

In the above macro, the active sheet is calculated ONLY at the end of each For-Next cycle.


It is always a good practice to restore the calculation setting of Excel at the end of a macro. e.g. using:
Application.Calculation = xlCalculationAutomatic

Regards,
Edwin Tam
Excel Power Expander - http://www.vonixx.com

31 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated March 30, 2025 Views 75,318 Applies to: