Calculation

Calculation Options

Multi Threaded Calculation

Formulas > Calculation - Multi Threading

Calculation Engine

Excel has a very complicated algorithm for choosing which cells to calculate in order to return the correct value from a formula.
The calculation algorithm was changed in Excel 2000 and again in Excel 2002.
Excel will always try and calculate the minimum number of cells possible and will only recalculate cells when:
1) cells, formulas, values or names have changed.
2) cells have been flagged as needing a recalculation.
3) cells dependent on other cells, formulas, names or values that need recalculating.

Calculating all the open workbooks regardless

Pressing (Ctrl + Alt + F9) recalculates all cells in all open workbooks regardless of whether they need to be recalculated.

Application.CalculateFull

For all open workbooks, forces a full calculation of the data and rebuilds the dependencies.
Dependencies are the formulas that depend on other cells. For example, the formula "=A1" depends on cell A1.
The CalculateFullRebuild method is similar to re-entering all formulas.

Application.CalculateFullRebuild

Calculating all the open workbooks

Pressing F9 recalculates any cells that have changed in all the open workbooks.
Application.Calculate returns an error if there are no workbooks open.

If Workbooks.Count > 0 Then 
   Application.Calculate 
End If

Calculating all the worksheets in a Workbook

There is no quick way to do this so you have to loop through each worksheet in that particular workbook.

For Each WshName in ActiveWorkbook.Worksheets 
  'copy from depository and/or put in depository
Next

Calculating all the cells on just a particular worksheet

Pressing (Shift + F9) is the same as pressing F9 except that it only recalculates cells on the active worksheet.

ActiveSheet.Calculate 
Worksheets(1).Calculate

Calculating a particular range on a particular worksheet

Worksheets(1).Range("A1:B10").Calculate

Range.Calculate

This will fail if calculation is set to manual and iteration is enabled.

Range("A4:C10").Calculate

Range.Dirty

If calculation is Manual, using the Dirty method instructs Excel to identify the specified cell to be recalculated.
If calculation is Automatic, using the Dirty method instructs Excel to perform a recalculation.
This is used to add the specified cells to the list of cells requiring calculation at the next recalculation

Application.Range("A3").Dirty

Application.Iteration

Indicates whether Excel calculations are in progress, pending or done

Application.CalculationState = xlCalculationState.xlPending

Returns the Excel version and calculation engine version used when the file was last saved.

Application.CalculationVersion

Stops any recalculations in an Excel application

Application.CheckAbort

Changing to Manual in your Macros

Start by defining a global variable that will contain the user's calculation mode before the macro is run.

Public glCalculationMode As Long

Public Sub Macro_Start 
   glCalculationMode = Application.Calculation 
   Application.Calculation = xlCalculation.xlCalculationManual 
End Sub

If you need to make any changes with automatic formula calculation, change the calculation to Automatic, make the changes and then set it back to Manual

Application.Calculation = xlCalculation.xlCalculationAutomatic 
'so whatever you need with automatic calculation switched on
Application.Calculation = xlCalculation.xlCalculationManual

Once the macro has finished change the calculation mode back to what it was originally.

Public Sub Macro_Finish 
   Application.Calculation = glCalculationMode 

End Sub

Application.CalculationInterruptKey

It is possible to define the key which you can use to interrupt the calculation process

Application.CalculationInterruptKey = xlCalculationInterruptKey.xlEscKey

Remember if you use xlNoKey then the calculation cannot be interrupted

Togglying EnableCalculation

You can also use the EnableCalculation property to calculate all the formulas on a worksheet.
Changing this property from False to True will flag all the formulas as uncalculated so next time the worksheet is calculated a "full" calculation will take place.

Dim objWorksheet As Worksheet 
Application.Calculation = xlConstants.xlManual 
objWorksheet = Worksheets(2) 
objWorksheet.EnableCalculation = False 
objWorksheet.EnableCalculation = True 
objWorksheet.Calculate

If you wanted to recalculate all the cells in all the open workbooks then you could do the following for all the worksheets in the workbook.

Dim objWorksheet As Worksheet 
Application.Calculation = xlConstants.xlManual 
For Each objWorksheet In Workbooks.Worksheets 
   objWorksheet.EnableCalculation = False 
   objWorksheet.EnableCalculation = True 
Next objWorksheet 
Application.Calculate

Important

If a calculation find more than 65,536 dependencies then a full calculation is performed.