Is this a Useful PAX tip?

[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...

[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.

[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.

[Wim Gielis]

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

What is the version number ?

[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".

[Steve Rowe]

I've hit this at a couple of customers and I forget which versions exactly but some have had the problem and then they fixed it and then it came back again.

The issue is caused by a huge volume of hidden named ranges in the workbooks. There is some product that creates hidden named ranges out there, never been able to track it down.
To see the named ranges

Code: Select all

Sub UnhideAllNames()
 
    Dim nm As Name
 
    For Each nm In ActiveWorkbook.Names
 
        nm.Visible = True
 
    Next
 
End Sub

You should suddenly see "thousands" of named ranges in the Name Manager, many of these will be error.

VBA to delete ALL named ranges in the wkb.

Code: Select all

Sub UnhideAllNames()
 
    Dim nm As Name
 On Error Resume Next
    For Each nm In ActiveWorkbook.Names
 
        nm.Delete
 
    Next
 On Error Goto 0
End Sub

This will skip over some names that have illegal characters in them which you'll need to delete manually.

HTH!

[burnstripe]

Steve's solution should fix it. I've also seen this before but in perspectives instead of pax.
You can confirm if this is the issue by trying to open up the name manager, if it just hangs or takes an age it's because there's a ton of references.

[Steve Rowe]

Steve's solution should fix it. I've also seen this before but in perspectives instead of pax.
You can confirm if this is the issue by trying to open up the name manager, if it just hangs or takes an age it's because there's a ton of references.

This assumes that the named ranges are visible in the name manager, so make sure to run the first bit of code in my post. To be honest I've not seen issues with the Name Manager even with named range counts in the high thousands but this might be version dependant.