Automatic Recalc

[hello123]

Hey Everyone,

We are using a SUBNM formula in a cell in PAfE. When Excel is set to Manual Calculation mode, selecting an element from the SUBNM dropdown or the subset editor results in the cell displaying RECALC instead of the selected element's name.

This issue only occurs when the calculation mode is set to Manual.

Is there a way to calculate only the selected cell after the element is chosen?
We tried using Selection.Calculate in a macro, but it triggers before the element is selected.

Regards,
Supriya

[WilliamSmith]

Hi there,

If you have Planning Analytics for Excel installed, and you are logged into PAW etc. you can trigger branching recalculation by calling the .Dirty() method on a range of cells.

Here is the VBA documentation: https://learn.microsoft.com/en-us/offic ... ange.dirty

This will not work if the application is set to manual calculation mode, so in VBA, below is the workarounds I use...

To recalculate a specific range:

Code: Select all

Sub ReCalcRng(rng As Range)

    calc = Application.Calculation
        
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    rng.Dirty
    
    Application.Calculation = calc

End Sub

To recalculate a worksheet:

Code: Select all

Sub ReCalcWS(Optional ws)
    
    If IsMissing(ws) Then
    
        Set ws = ActiveSheet
    
    End If
    
    calc = Application.Calculation
        
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    ws.Cells.Dirty
    
    Application.Calculation = calc
    
End Sub