PAfE VBA Refresh in Manual Calc Mode

Post Reply
User avatar
WilliamSmith
Posts: 37
Joined: Tue Dec 13, 2022 8:54 pm
OLAP Product: TM1 / PA / PAx / PAW
Version: TM1 11
Excel Version: 365

PAfE VBA Refresh in Manual Calc Mode

Post by WilliamSmith »

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
sebsimpson
Posts: 1
Joined: Fri Apr 21, 2023 12:10 pm
OLAP Product: TM1
Version: 2.0.9.16
Excel Version: O365

Re: PAfE VBA Refresh in Manual Calc Mode

Post by sebsimpson »

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!
Post Reply