PAfE. Double click on subnm takes several minutes for set editor to appear

[JohnO]

And I found the reason.

We only recently moved from Perspectives and of course have encountered several challenges.

In this specific case the dimension only has about 20 elements. Clutching at straws to understand why we were having this problem I thought to look in 'Name Manager' at the ranges as I have noticed that many of our workbooks have a lot of named ranges defined as #Ref.

When I removed all of these #Ref cases and saved the workbook the response time for set editor to appear changed from several minutes to instant. I have no idea why one or more of these #Ref cases would have caused the performance hit.

This information may be of use to others.

[Steve Rowe]

I've hit this issue a few times (at the same customer, see also here )

When I last hit this, the other common symptom was for PAfE having a noticable lag when navigating around from one cell to another using the cursor keys and more general slow calculation peformance.

If you think you are suffering from this then it's worth checking for named ranges that have been hidden, these don't show up in the Excel front end and need to be unhidden programmatically.

Code: Select all

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

Checking for a large volume of named ranges is one of the first things we check for when diagnosing performance issues.

[JohnO]

I've hit this issue a few times (at the same customer, see also here )

Checking for a large volume of named ranges is one of the first things we check for when diagnosing performance issues.

Thanks Steve, I now recall your thread. It's not an obvious thing to think of as a cause of poor performance for a subnm.

I used the following (I think I may have taken this from the web some time back) to get rid of ours.

Sub DeleteNamedRangesWithREF()
Dim nm As Name
For Each nm In ThisWorkbook.Names
If InStr(nm.Value, "#REF!") > 0 Then
nm.Delete
End If
Next nm
End Sub

Did you raise this with IBM? / any response?

[Steve Rowe]

From what I have been told / inferred / remember the issue relates to PAfE driven picklists.
When a cell is selected it has to check if there is PAfE driven picklist in the cell.
For reasons unknown this check involves checking and testing all the named ranges in the workbook. What the purpose of this step is, is unknown to me, it does seem an odd thing to do, but I'm not really in a position to judge.

When I first hit the issue many years ago, back when Rebecca Hansen was looking after PAfE, they did fix the code such that this check was no longer performed and the general issue of performance and named ranges disappeared in the next release of PAfE.

A number of years later we upgraded the customer and the issue had resurfaced. Once we realised it was the same issue as we hit in the intial implementation the fix was straightforward. I did not pursue this with IBM as I had a fix and chose to assume that the check had been re-introduced for a good reason rather than an accident.....

My suspicion is that there is a procedure in PAfE that is called often that is something like "make sure I know everything I might possibly need to know about the workbook". This includes the check of named ranges. This procedure is called at the start of many actions in PAfE and so you see the performance hit in odd places...

Just to stress the above is mostly guess work and what I remember of a couple of converstaions I had with Rebecca.

Cheers,