Is this a Useful PAX tip?

Post Reply
John Hammond
Community Contributor
Posts: 285
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Is this a Useful PAX tip?

Post by John Hammond »

L & G

A user was moaning that, when he was moving from Cell to cell in a spreadsheet with PAX enabled, it was taking 5 seconds.

So PAX was intercepting an event and doing 5 seconds of work and then returning control to Excel to move the cursor.

We told him to type and run this in VBA: "Application.EnableEvents = false" and the problem went away. Furthermore all explicit actions such as PAX refresh, the Panel etc all worked fine.

So is this a handy way to resolve this problem, or a fool's paradise. We haven't exhaustively tested this, and told the user to try it and see, as it was making his work impossible otherwise.

But what if killing events for PAX may introduce further subtle errors? Does it use events to build internal structures which might become out of sync with reality?

Does anyone know what events PAX intercepts and what it does before returning control to Excel?

What could it possibly do in the 5 seconds of delay our user was experiencing? (The user speculated it was to do with External References as they had recently moved to a slower more secure server and were seeing slowness across the board. But why would PAX care about these?)

If anyone can shed a light on this it would be much appreciated. I'll let you know how our user gets on too...
User avatar
Posts: 1071
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL; PAW 2.0.71
Excel Version: Office 365 64-bit
Location: JHB, South Africa

Re: Is this a Useful PAX tip?

Post by gtonkin »

Not sure I would do that as many things will no longer work as designed.

Is this a general issue or a particular book/sheet?
Look for volatile functions like today, offset and others. They can kill performance.
Posts: 72
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Office 365 x32

Re: Is this a Useful PAX tip?

Post by Adam »

Recommend running a fiddler trace to get the specifics, but yes when you move in PAFE QR grid from cell to cell there’s some jitter.
Take care.
Wim Gielis
Posts: 2912
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL
Excel Version: Microsoft 365
Location: Brussels, Belgium

Re: Is this a Useful PAX tip?

Post by Wim Gielis »

If you select / show a different menu in the Excel ribbon, does the lag disappear ?

What is the version number ?
Last edited by Wim Gielis on Fri Aug 05, 2022 8:26 pm, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014 ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules
Posts: 71
Joined: Fri Feb 13, 2009 1:41 am
OLAP Product: IBM Planning Analytics Cloud
Version: 2.0.9 IF (2)
Excel Version: 2016

Re: Is this a Useful PAX tip?

Post by jorelb »

Maybe a couple of things to look into.
1) If a custom report, is the "Refresh data on writeback" option set to "None" in the Planning Analytics Options?
2) Is the Excel Calculation Option set to "Manual".
Post Reply