UDF executing multiple times with Structured References

user_18142084 0
Viewed 81

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.

user-defined-functions vba excel
2 Answers

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.

enter image description here

user_32196130

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

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

Update:

In putting this into practice, I also found that should any of the dependent variables be based on formulas (eg, if [@A] contained a formula), then this will need an "@" at the front as well.

In my circumstance, there are so many dependencies it was easier to place an "@" at in front of every formula in the table (my new 'rule' when working with UDFs and Tables). Hope this helps someone.

user_181420840
Related