Planning Analytics

 View Only

 Factors affecting DBRW performance in PAFE

Hallbjorn Bjornsson's profile image
Hallbjorn Bjornsson posted Wed September 24, 2025 01:51 PM

Hi,

I have a client that created excel workbook with around 30 sheets with a lot of DBRW formulas. At some point the workbook / sheets started to show the behavior that if you click on one cell with DBRW formula it started to do something (not sure what but at least the workbook was occupied in doing something) that took up to 1 minute. But if you click on a cell with no DBRW formula, then everything worked as normal. This of course made the WB unusable.

I deleted all sheets but one out of the WB and still had the same behaviour. Also removed all formatting and other things that might have some effect on performance without any luck.

As a test I did a copy/paste values to a new workbook and recreated the DBRW formulas. That resulted in a workbook that is working properly.
So I now have 2 identical workbooks with totally different performance. One working like a dream and other one unusable.

This got me thinking about what might cause this behaviour... that is what factors in workbooks are likely to have such a huge affect on performance?

Also, what could the system be doing when I click on a cell with a DBRW formula?

Any ideas would be appreciated.

Regards,

Habbi

Simon Saul's profile image
Simon Saul posted Wed September 24, 2025 02:08 PM

2 things you could look for

  1. Named ranges. If you open up the name manager in excel are there a lot of ranges. If the name manager hangs that's also an indication of lot of ranges. Could have perhaps been created accidentally through an dynamic report/active form
  2. Are the references inside the dbrw static or formulae driven. Could be the references are slow
Hallbjorn Bjornsson's profile image
Hallbjorn Bjornsson posted Wed September 24, 2025 03:54 PM

Thanks Simon for a promt responce,

Regarding those 2 pointers both of the workbooks are identical in the way that they only have one sheet in them and in those sheets the only formula in the sheets are the DBRW formulas without one line that is a simple sum formula. And regarding named ranges there are none in both of the files. I have also checked for conditional formating formulas and there are none.

regards,

Habbi

Steven Rowe's profile image
Steven Rowe posted Thu September 25, 2025 05:16 AM

With respect to named ranges, with VBA you can also hide a named range in the same way you can hide a worksheet.  There are applications out there that add ALOT of hidden named ranges to excel workbooks, though we have never been able to track down the source for this.

Run this VBA script to unhide all names.

Sub UnhideAllNames()

    Dim nm As Name
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    For Each nm In ActiveWorkbook.Names

        nm.Visible = True

    Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

If there are alot of named ranges then this can take a while to run.

Your symptom does sound a lot like the behaviour I've seen in older releases where all named ranges (for reasons unknow) are checked by PAfE on each navigational step in a sheet.

In current releases a lot of named ranges cause a big performance drop off, would be great if PAfE could ignore hidden named ranges when performing a calculation pass (or entirely..)

I've had extreme cases where there were so many named ranges that Name Manger in Excel was crashing and I had to manually unzip and edit the Excel on the file on the disc to get it working.  Fun times!

Related Content