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

[CRP0021]

Hi William,
I'm experiencing the same issue as the op and your code does work but I have a small wrinkle.

If I need to keep excel in maual calc mode after a user makes a selection from a SUBNM is there any vba code you can think of that would do this?

The reason we'd like to keep excel in manual calc mode is that we have enabled the tm1features file with constrained calcs to help with background excel workbook refresh issues (this has been working well for us and the users).

IBM has instructed that excel should be kept in manual calc mode since we have enabled the tm1features file but we are running into an unexpected issue of the RECALC error when a user makes a selection from a SUBNM.

We'd like to try and avoid inserting action buttons across all workbooks as that is a change for our user community and would really like to mimic the behaviour of excels auto calc, while in manual mode and staying in manual mode.

Any advice would be greatly appreciated.