PAfE: Refreshing Behaviour Issue

Post Reply
Constantinb
Posts: 2
Joined: Thu Mar 18, 2021 11:26 am
OLAP Product: PAX
Version: 2.0.59
Excel Version: Office 365 32bit

PAfE: Refreshing Behaviour Issue

Post by Constantinb » Fri Mar 19, 2021 10:19 am

Hello,

I am curious if anyone is experiencing the same strange refreshing behavior as I am and if anyone has found a solution to it:

I am currently working on a rather large custom report with some VBA-Code. To make this report work, I have to refresh some cells multiple times while the code is running. For this I am using RefreshSelection as described in the PAfE API documentation.

The strange thing is PAfE is sometimes taking forever to refresh a selection, even if it is small. The time is often comparable to refreshing the whole sheet or book. Also, sometimes just calling RefreshSelection on an empty cell takes forever, which is surprising, since there is no Formula, which could be refreshed.

This behavior, as it stands, is unusable, since just running the affected once takes way too long.

I am looking forward to your responses

User avatar
gtonkin
MVP
Posts: 900
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PAfE: Refreshing Behaviour Issue

Post by gtonkin » Fri Mar 19, 2021 12:01 pm

Have you got any volatile formulas in your workbook e.g. OFFSET?

Also found it critical to use the Wait() function when triggering any refreshes via VBA to ensure that the refresh has actually completed before moving on.

Paul Segal
Community Contributor
Posts: 294
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: PAfE: Refreshing Behaviour Issue

Post by Paul Segal » Fri Mar 19, 2021 4:13 pm

I see you got an answer from Ted Phillips over in the IBM forum. For thread completeness, his suggestion was to explore constrained calcs: https://www.ibm.com/support/knowledgece ... tions.html
Paul

User avatar
macsir
MVP
Posts: 754
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: PAfE: Refreshing Behaviour Issue

Post by macsir » Mon Mar 22, 2021 8:22 pm

Try to disable to all unnecessary Excel addins and see?
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Constantinb
Posts: 2
Joined: Thu Mar 18, 2021 11:26 am
OLAP Product: PAX
Version: 2.0.59
Excel Version: Office 365 32bit

Re: PAfE: Refreshing Behaviour Issue

Post by Constantinb » Wed Mar 31, 2021 1:24 pm

Constrained Calc did not actually do the trick. What worked in our case, was moving the calculations to a separate sheet and disable all events, set Worksheet.EnableCalculation to false at certain points in the code and setting calculations to manual while VBA works.

We now have a hidden sheet, where the ranges containing the formulas are copied to. We then refresh this hidden sheet and copy the result back. Surprisingly this is usually done in only a few seconds, even if the amount of cells is in the thousands.

Post Reply