Lag on Cell Navigation in Pafe 64,65 and 66

[Steve Rowe]

Hi,
We're getting some strange behaviour at a customer site, not sure if anyone has encountered before?

We've upgraded them to the latest everything from an early release of PAW / Pafe (.33).

Some of their workbooks now experience a crippling lag when navigating across cells with DBRW formula in them. i.e. Select a cell with a DBRW, wait 5s and regain control, hit an arrow key to navigate to the next cell, wait 5s to regain control, repeat....

If I copy the cells of the sheet to a fresh sheet in a new workbook then I get the normal performance. (i.e. select all the cells and copy to a clean sheet in a new wkb)
If I copy the cells of the sheet to a fresh sheet in the same workbook then the issue persists.
If I move or copy the sheet to a new workbook the cell navigation issue persists.

There seems to be some property at the book or worksheet level that is triggering the issue. The original excel file was produced in an older version of Excel, not sure how old.

Areas already checked with no impact on the issue.

• No Macros
• Removed all formats
• Checked fiddler trace, indicating the calls to TM1 are the same in the working / not working test case. Interesting to see that there seems to be a call to the DB just from selecting a cell.
• Calculation on automatic for both cases.
• Tested from a variety of machines and locations and bitness of Excel / Pafe
• Performed a repair on open for the failing use case (actually seemed to make the issue worse, with the lag occuring repeatedly on the same cell)
• Rebuilt dependancy tree (ctrl+alt+shift+f9)
• Problem does not occur when the Pafe client is not loaded.
• Problem does not occur when the Pafe client is loaded but not connected to the DB.
• Saved the file as a .xls, issue persists
• Saved the .xls back as a .xlsx, issue persists
• Issue does not occur on a subnm formula.

Any ideas as to what is occuring?

[Wim Gielis]

Is it an active form or simple DBRW formulas ?
What happens if you hard code the arguments inside the DBRW’s ?

[gtonkin]

Have you checked out the UsedRange - the addin may be checking the entire worksheet?
Any volatile formulas that may be behaving differently now e.g. OFFSET, NOW etc. etc.
Named ranges with #Refs (don't forget that PAfE creates hidden named ranges you may need to unhide)
TM1RebuildOption=0?
Very hidden Cognos_Office_Connection_Cache exists? Tried deleting and letting PAfE (hopefully) recreate?
Opened the VBA editor and stepped into a Sub to avoid other events triggering any other VBA (possibly not the PAfE addin but worth checking)

[Steve Rowe]

Hi,

• It is a simple DBRW
• Hardcoded references - Untested at this point
• TM1REBUILDOPTION =1 , tested with 0 no impact
• I'd already cleaned up the named ranges with errors, but there are load of named ranges with links to other workbooks. Deleted all names, no impact.
• Opened the VBA editor and stepped into a Sub to avoid other events triggering. Good idea but no impact
• Cognos_Office_Connection_Cache wasn't present, manually added no impact.

Creating a new custom report versus a different DB in the problem workbook has the same issue.

Thanks for the input all