UDF executing multiple times with Structured References-CodePudding

I am experiencing performance problems with a large (wide and long) table that uses a UDF that has variables passed as structured references.

It seems that when a structured reference for a specific cell, eg "[@A]" is passed to the UDF, it flags the whole column, ie "[A]" as dirty and every cell in the column the UDF references is recalculated (this is the source of the performance problem).

I have found that if I change the UDF within the table to use cell address, eg "A2", then the UDF only executes when that cell changes.

To test, create a simple function with debug.print to highlight when it is run:

Public Function zap(a As Double, b As Double) As Double
    Debug.Print "zap: " & a & " " & b
    zap = a   b
End Function

Create a 3 column table (A, B, C), with column C as "=zap([@A],[@B])". if you enter or change a value in column A or B, the UDF will execute for each row regardless of whether the value in the row/column intersection has changed.

if you change column C to use cell addressing, eg "=zap(A2,B2)", then the UDF only executes once for the row that has changed.

I can use cell addressing to overcome the performance problem, however using a table with structured references provide a lot of benefits for this application.

I would like to know if I have missed or misunderstood something about structured references, or whether there is an adjustment to the UDF that can accommodate.

CodePudding user response：

I used this formula in column C =zap([@A],[@B]) and as you can see in the video below I cannot reproduce your issue. It only re-calculates the formula in the row I changed something.

CodePudding user response：

Thank you to Rory - placing an @ at the beginning of the formula worked:

=@zap([@A],[@B])