TM1 Forum

Posted: **Wed Mar 15, 2023 4:39 pm**

Sharing findings I discovered today. We've been wracking our brains trying to find a way to get PAfE to refresh DBRWs etc. while staying in manual calc mode. The .Dirty() method of the Range class seems to be the magic function.

Anyone have a better way to do this?

Code: Select all

Sub RecalcSelection()
    
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    Selection.Dirty
    Application.Calculation = xlManual

End Sub

Sub RecalcSheet()
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    ws.Cells.Dirty
    Application.Calculation = xlManual
    
End Sub

Posted: **Fri Apr 21, 2023 5:34 pm**

I investigated doing something like this in PAfE a few years back and the best I could come up with at the time was to just get Excel to call the keyboard shortcut to recalculate using the SendKeys application method, in this case Shift+F9, but you can adjust the keys depending on your use case. Below in the simplest form:

Code: Select all

Sub RecalcSheet()
' Recalculate Sheet using shortcut key
    SendKeys "+{F9}"
End Sub

It does require the custom report setting to refresh data on Excel recalc keys, but in my experience these hotkeys seem to work in PAfE even if this setting is not enabled.

Not sure if this is the best alternative way but certainly one that worked for me!

TM1 Forum

PAfE VBA Refresh in Manual Calc Mode

PAfE VBA Refresh in Manual Calc Mode

Re: PAfE VBA Refresh in Manual Calc Mode