VBA Code

1) Is it possible to prevent the calculation from being interrupted ?
Yes. There are two ways this can be achieved:
Using the CalculationInterrupting property.

Application.CalculationInterrupting = xlCalculationInterruptKey.xlNoKey. 

If you are running from VBA using the Application OnTime.

Application.OnTime MacroName 

2) What does the Range.Calculate method do ?
Smart Recalculation of the specified range.

Range("A1:D20").Calculate 

3) What does the Range.CalculateRowMajorOrder method do ?
Smart Recalculation of the specified range ignoring any forward references or within range dependencies.

Range("A1:D20").CalculateRowMajorOrder 

4) Is there a way in code to trigger a recalculation of the active sheet only ?
Togglying the EnableCalculation property and then recalculating.

ActiveSheet.EnableCalculation = False 
ActiveSheet.EnableCalculation = True 
Activesheet.Calculate 

5) Is there a way to check the calculation state ?
Yes. You can use the CalculateState property.

Application.CalculationState 

6) What does the Workbook.ForceFullCalculation property do ?

Workbook.ForceFullCalculation = True 

Returns or sets the workbook to forced calculation mode.
This property will be reset when Excel is restarted or the workbook is closed.
When you want every calculation of the workbook to be a full calculation.
When this property is set to True the calculation time for data tables will increase significantly.

7) When would you use the Workbook.ForceFullCalculation property ?
If a workbook has a large number of complex dependencies that takes ages to load or when a recalculation takes longer than a full calculation.

8) Write code that will trap the F9 events and redirect it to a subroutine.

Application.OnKey "{F9}", "HandleF9" 
Application.OnKey "+{F9}", "HandleShiftF9" 
Application.OnKey "^+{F9}", "HandleCtrlShiftF9" 
Application.OnKey "%^+{F9}", "HandleAltCtrlShiftF9" 

9) What is the Range.Dirty method ?
Designates a range to be recalculated the next time a recalculation is performed.

Range("A1:B10").Dirty = True 

Clearing Data

There is a way to clear a worksheet model of data and leave the formulas intact.

Range("A1").SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 
                         Value:=xlSpecialCellsValue.xlNumbers).ClearContents. 

xlCVError.xlErrRef 

ActiveWindow.DisplayFormulas = True 

Removing a Formula

Selection.Formulas = Selection.Value 
Range("D4").Formula = "=SUM(B5:D20)" 
Range("F5").Formula = "=5-10*RAND()" 
Cells(1,2).Formula = "=G20" 

Range("E2").FormulaR1C1 = "=R[-2]C[2]-R[-2]C[3]" 
Range("E2").FormulaR1C1 = "=RC[-1]+RC[-3]-(8/24)" 

Entering dates

Range("A1").FormulaR1C1 = "=DATEVALUE(""01/07/77"")+30 
Range("A1").NumberFormat = "mmm-dd-yy" 

Converting Formulas between A1 and R1C1

Public Function FORMULAGET(ByVal rgeCell As Range, _ 
                   Optional ByVal bCellAddressPrefix As Boolean = False) As String 
   
   If VarType(rgeCell) = 8 And Not rgeCell.HasFormula Then 
      FORMULAGET = "'" & rgeCell.Formula  
   Else 
      FORMULAGET = rgeCell.Formula 
   End If 
   If rgeCell.HasArray Then 
      FORMULAGET = "{" & rgeCell.Formula & "}" 
   End If 
   
   If bCellAddressPrefix = True Then 
      FORMULAGET = rgeCell.Address(0, 0) & ":  " & FORMULAGET 
   End If 
End Function 

Application.FormulaBarHeight

Reads and writes the height of the formula bar.

Name as many ways as you can to make Excel calculate something.
Press F9
Shift + F9
F2 in a cell
Enter a formula in a cell
Select a cell containing a formula, press F2 then ENTER
Create a pivot table
Refresh a pivot table
Using VBA code spin buttons/scroll bar etc

Calculation Options

Formulas > 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 

Formula or Formula2

link - learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2 

When targeting a Dynamic Arrays version of Excel, you should use Range.Formula2 in preference to Range.Formula.

Range.Formula and Range.Formula2 are two different ways of representing the logic in the formula.
They can be thought of a 2 dialects of Excel's formula language.

Excel has always supported two types of formula evaluation: Implicitly Intersection Evaluation ("IIE") and Array Evaluation ("AE").

IIE was the default for cell formulas
AE was used everywhere else (Conditional Formatting, Data Validation, CSE Array formulas, etc).

The primary difference between the two forms of Evaluation was how they behaved when a multi celled range (e.g. A1:A10) was passed to a function that expected a single value:
IIE would choose the cell on the same row or column as the formula. This operation is referred to as "implicit intersection".
AE would call the function with each cell in the multi celled range and return an array of results. This operation is referred to as "lifting".

When Range.Formula is used to set a cell's formula, IIE is used for evaluation.

With the introduction of Dynamic Arrays ("DA"), Excel now supports returning multiple values to the grid and AE is now the default.
AE formula's can be set/read using Range.Formula2 which supersedes Range.Formula.

However, to facilitate backcompatiblity, Range.Formula is still supported and will continue to set/return IIE formulas.
Formula's set using Range.Formula will trigger implicit intersection and can never spill. Formula read using Range.Formula will continue to be silent on where Implicit Intersection occurs.

Excel automatically translates between these two formula variations, so either can be read and set.
To facilitate the translation from Range.Formula (using IIE) to Range.Formula2 (AE), Excel will indicate where implicit intersection could occur using the new implicit intersection operator @.
Likewise, to facilitate the translation from Range.Formula2 (using AE) to Range.Formula (using IIE) Excel will remove @ operators that would be performed silently.
Often there is no difference between the two.

Converting Formulas

Converts cell references in a formula between the A1 and R1C1 reference styles, between relative and absolute references, or both. Variant.

This example converts a SUM formula that contains R1C1-style references to an equivalent formula that contains A1-style references, and then it displays the result.

Dim sFormula As String 
Dim sChangedFormula As String 

sFormula = "=SUM(R10C2:R15C2)" 
sChangedFormula = Application.ConvertFormula (Formula:=sFormula, _ 
                                              FromReferenceStyle:=xlReferenceStyle.xlR1C1, _ 
                                              ToReferenceStyle:=xlReferenceStyle.xlA1) 

sChangedFormula = "=SUM($B$10:$B$15)" 

Formula - Required Variant. A string that contains the formula you want to convert. This must be a valid formula, and it must begin with an equal sign.
FromReferenceStyle - The reference style of the formula.
ToReferenceStyle - The reference style you want returned. If this argument is omitted, the reference style isn't changed; the formula stays in the style specified by FromReferenceStyle.
ToAbsolute - Specifies the converted reference type. If this argument is omitted, the reference type isn't changed.
RelativeTo - Optional Variant. A Range object that contains one cell. Relative references relate to this cell.

Dim sFormula As String 

sFormula = "=SUM(R10C2:R15C2)" 
Application.ConvertFormula (Formula:=sFormula, _ 
                            FromReferenceStyle:=xlReferenceStyle.xlR1C1, _ 
                            ToReferenceStyle:=xlReferenceStyle.xlA1, _ 
                            ToAbsolute:=xlReferenceStyle.xlR1C1, _ 
                            RelativeTo:=Range("D4") ) 

Watch Window

Use the Watches property of the Application object to return a Watches collection
The Watch object represents a range which is tracked when the worksheet is recalculated. The Watch object allows users to verify the accuracy of their models and debug problems they encounter. The Watch object is a member of the Watches collection.
In the following example, Microsoft Excel creates a new Watch object using the Add method. This example creates a summation formula in cell A3, and then adds this cell to the watch facility.

Sub AddWatch() 
    With Application 
        .Range("A1").Formula = 1 
        .Range("A2").Formula = 2 
        .Range("A3").Formula = "=Sum(A1:A2)" 
        .Range("A3").Select 
        .Watches.Add Source:=ActiveCell 
    End With 
End Sub 

You can specify to remove individual cells from the watch facility by using the Delete method of the Watches collection. This example deletes cell A3 on worksheet 1 of book 1 from the Watch Window. This example assumes you have added the cell A3 on sheet 1 of book 1 (using the previous example to add a Watch object).

Sub DeleteAWatch() 
    Application.Watches(Workbooks("Book1").Sheets("Sheet1").Range("A3")).Delete 
End Sub 

You can also specify to remove all cells from the Watch Window, by using the Delete method of the Watches collection. This example deletes all cells from the Watch Window.
You can also specify to remove all cells from the Watch Window, by using the Delete method of the Watches collection. This example deletes all cells from the Watch Window.

Sub DeleteAllWatches() 
    Application.Watches.Delete 
End Sub 

Range.ShowDependents 
Range.ShowPrecedents 

Range.NavigateArrow

Navigates a tracer arrow to a precedent and returns a Range object.
This method causes an error if it is applied to a cell that does not have any visible tracer arrows.

Range.NavigateArrow(object TowardPrecedent, _ 
                    object ArrowNumber, _ 
                    object LinkNumber) 

TowardPrecedent - Specifies the direction to navigate, True = towards precedents, False = towards dependents
ArrowNumber - Specifies the arrow number to navigate. Corresponds to the numbered reference in the cells formula.
LinkNumber - If the arrow is an external reference arrow, this indicates which reference to follow.
If this argument is not provided, the first external reference is followed.

Array Formulas

You can enter an array formula into a cell using the FormulaArray property.

Range("B2").FormulaArray = "=SUM(A1:A2)" 

SS
Returns or sets the array formula of a range.
Returns (or can be set to) a single formula or a Visual Basic array.

Entering an array formula

Range("A1").FormulaArray = "=SUM(IF(-----------------))" 

Range("A1:B20").FormulaArray = "=SUM(IF(-----------------))" 

Reading an Array Formula

If the specified range doesn't contain an array formula, this property returns null. Read/write Variant.

It is often useful to create arrays in a VBA function and return them to Excel.

Public Function MovingAverage(ByVal rgeValues As Range, _ 
                              ByVal iInterval As Integer) As Variant 
   Dim lrowno As Long 
   Dim inumber As Integer 
   Dim arresult() As String 
   Dim dbtotal As Double 
   ReDim arresult(rgeValues.Rows.Count - 1) 
   For lrowno = 1 To (iInterval - 1) 
      arresult(lrowno - 1) = 0 
   Next lrowno 
   For lrowno = 1 To (rgeValues.Rows.Count - iInterval + 1) 
      dbtotal = 0 
      For inumber = 1 To iInterval 
         dbtotal = dbtotal + rgeValues(lrowno, 1).Offset(inumber - 1, 0).Value 
      Next inumber 
      arresult(lrowno + iInterval - 2) = (dbtotal / iInterval) 
   Next lrowno 
   MovingAverage = Application.WorksheetFunction.Transpose(arresult) 
End Function