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
PAfE: Refreshing Behaviour Issue
-
- Posts: 2
- Joined: Thu Mar 18, 2021 11:26 am
- OLAP Product: PAX
- Version: 2.0.59
- Excel Version: Office 365 32bit
- gtonkin
- MVP
- Posts: 1211
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: PAfE: Refreshing Behaviour Issue
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.
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.
-
- Community Contributor
- Posts: 306
- 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
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
- macsir
- MVP
- Posts: 785
- 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
Try to disable to all unnecessary Excel addins and see?
-
- 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
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.
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.