Is it possible to enable Excel Manual Calculation for specific formulas?

In the context my previous answer to the post involving single cell, i also want share our old experience involving multiple cells. that days We used the formula in an indexed fashion like =myformula(1)... etc and stored it in a global array. Now today thanks to your great idea of Caller function. I recreated another improvised solution involving multiple cells.

Here again in module1

Global Flag As Boolean, LastValArr(1 To 10, 1 To 2) As Variant, Ws As Worksheet, Rng As Range

Public Function MyFormula() As Variant
Dim Adr As String, X As Integer
   If Flag Then
   MyFormula = GetNewValueFromAPI()  'expensive call to server
   Else
   Adr = Application.Caller.Address
    For X = 1 To 10
        If InStr(1, LastValArr(X, 2), Adr) > 0 Then
        MyFormula = LastValArr(X, 1)
        Exit For
        End If
    Next
   End If
End Function

Function GetNewValueFromAPI() As Variant
GetNewValueFromAPI = Application.WorksheetFunction.RandBetween(1, 1000)
End Function

Sub CalcA1()
Flag = True
Rng.Dirty
Flag = False
Ws.Range("F1").Value = IIf(Flag, "On", "Off")
End Sub

Sub ToggleFlag()
Flag = Not Flag
Ws.Range("F1").Value = IIf(Flag, "On", "Off")
If Flag Then Rng.Dirty
End Sub

in Workbook_Open event

Private Sub Workbook_Open()
Dim X As Integer
Dim Cell As Range
Set Ws = ThisWorkbook.Sheets("Sheet1")
Set Rng = Ws.Range("A1:A5")
Set Rng = Union(Rng, Ws.Range("C1:C5"))
Flag = True
Rng.Dirty
Flag = False
Ws.Range("F1").Value = IIf(Flag, "On", "Off")
End Sub

in Sheet1 Worksheet_Calculate event

Private Sub Worksheet_Calculate()
Dim X As Integer
Dim Cell As Range
X = 1
    For Each Cell In Rng.Cells
    LastValArr(X, 1) = Cell.Value
    LastValArr(X, 2) = Cell.Address
    X = X + 1
    Next
End Sub

Edit: On second thought after initial feel good of posting the Demo answer, I found it lacks User friendliness and ease of copy pasting UDF formulas while working in Excel Therefore i tried improvise it further so it could be used by users don't have access to VBA code and could work with copy paste of the UDF.

So 1st I came across a solution to store the Last Values in a temp sheet (may be Very Hidden Sheet). with apprehension that working with cell access may degrade performance of the code, I refrained from posting it and I finally restored to Dictionary Object.

This solution have added with basic advantage of Auto mapping of formula cells (by searching "=myformula" in used range of the Sheet) to enable/disable calculation. This would enable users without access to code modules to work freely with UDF.

Here reference to Microsoft scripting runtime has been added.

Code in module:

Global Flag As Boolean, Ws As Worksheet, Rng As Range, Dict As Dictionary
Public Function MyFormula() As Variant
Dim Adr As String
   If Flag Then
   MyFormula = GetNewValueFromAPI() 'expensive call to server
   Else
   Adr = Application.Caller.Address
   'Debug.Print Adr
   MyFormula = IIf(Dict.Exists(Adr), Dict(Adr), 0)
   End If
End Function
Function GetNewValueFromAPI() As Variant
'Delay (2)
GetNewValueFromAPI = Application.WorksheetFunction.RandBetween(1, 1000)
End Function
Sub CalcA1()
Flag = True
If Not Rng Is Nothing Then Rng.Dirty
'Debug.Print "in calA1"
Flag = False
Ws.Range("F1").Value = IIf(Flag, "On", "Off")
End Sub
Sub ToggleFlag()
Flag = Not Flag
Ws.Range("F1").Value = IIf(Flag, "On", "Off")
If Flag And Not Rng Is Nothing Then Rng.Dirty
End Sub
Sub BuildRange()
Application.EnableEvents = False
Dim Cell As Range
CalcCnt = CalcCnt + 1
    Set Rng = Nothing
    Dict.RemoveAll
    For Each Cell In Ws.UsedRange.Cells
        If Left(Cell.Formula, 10) = "=myformula" Then
        'Debug.Print "From Sht Calc -" & Cell.Address
            If Dict.Exists(Cell.Address) = False Then
            Dict.Add Cell.Address, Cell.Value
            Else
            Dict(Cell.Address) = Cell.Value
            End If

            If Rng Is Nothing Then
            Set Rng = Cell
            Else
            Set Rng = Union(Rng, Cell)
            End If
        End If
    Next

Application.EnableEvents = True
End Sub

In Workbook_Open

Private Sub Workbook_Open()
'Dim X As Integer
Dim Cell As Range
Set Ws = ThisWorkbook.Sheets("Sheet1")
Set Dict = New Dictionary
Flag = True
BuildRange
If Not Rng Is Nothing Then Rng.Dirty
Flag = False
Ws.Range("F1").Value = IIf(Flag, "On", "Off")
End Sub

In Sheet Calculate event

Private Sub Worksheet_Calculate() BuildRange End Sub

If you are using an UDF in the cell, I will like to make it like this workaround. For demo and test, Only used a single cell A1 in "Sheet1" , instead of using any API, I used WorksheetFunction.RandomBetween May use range and array if multiple cells are used.

In "Sheet1" cell A1 used =myFormula()

in a module

Public Flag As Boolean, LastVal As Variant

Public Function MyFormula() As Variant
   If Flag Then
   MyFormula = GetNewValueFromAPI()  'expensive call to server
   Else
   MyFormula = LastVal
   End If
End Function

Function GetNewValueFromAPI() As Variant
GetNewValueFromAPI = Application.WorksheetFunction.RandBetween(1, 1000)
End Function

Sub CalcA1 in Module1 would be used to recalculate A1 whenever necessary. It could be called from any events also according to actual requirement.

Sub CalcA1()
Flag = True
Worksheets("Sheet1").Range("A1").Dirty
Flag = False
End Sub

In workbook Open event the the LastVal was calculated with Flag as true and then Flag was reset to false to prevent further calling GetNewValueFromAPI

Private Sub Workbook_Open()
Flag = True
Worksheets("Sheet1").Range("A1").Dirty
LastVal = Worksheets("Sheet1").Range("A1").Value
Flag = False
End Sub

In Worksheet_Calculate event of Sheet1 the LastVal is being recorded.

Private Sub Worksheet_Calculate()
LastVal = Worksheets("Sheet1").Range("A1").Value
End Sub

Working Demo

Regret, I came across this post (A Real Good Question) late, since We had already been used something in this line in our workplace. Thanks to @Pawel Czyz for editing the post it came under Active List today only.