PAfE: Refreshing Behaviour Issue

[Constantinb]

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

[gtonkin]

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]

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

[macsir]

Try to disable to all unnecessary Excel addins and see?

[Constantinb]

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.