CODEWITHSUNDEEP
excelvba
user2694476
user2694476

Reputation: 51

Why does Excel call UDFs more than required?

I have a spreadsheet that utilizes a lot of UDFs. The last time I ran it, the execution time was much greater than it was the previous time, but the data quantity is now greater. I decided to count calls to each UDF to see if something unusual was going on.

I was rather stunned to find the UDFs that are unconditionally utilized are called exactly 2 times more than the number of times the UDF equation is used in the worksheet. Note that this happens when I request a worksheet recalculation.

However if I calculate a subset of the UDF equations (e.g. a column) the number of calculations counted is exactly the same as the number of UDF usages -- just as you would expect.

Any idea what is going on when the entire sheet is recalculated? Is there anyway to force single evaluation of UDFs when recalculating the entire worksheet?

Upvotes: 2

Views: 84

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33165

This typically happens because Excel doesn't know where to put your UDF on the calculation tree. It makes a guess and monitors the progress. In your case, it's guessing wrong and reinserting it into the tree (x2).

The most common reason Excel can't figure out what to do with the UDF is that you haven't supplied all the arguments necessary. Excel can only tell what needs to be calculated before the UDF if you pass everything into the UDF as an argument.

Public Function AddToA1(rAddTo As Range, rA1 As Range) As Double

    AddToA1 = rAddTo.Value + rA1.Value

End Function

When Excel is building the calculation tree, it puts any cells with this function after rAddTo and rA1. When it gets to calculating this function, it sees that rAddTo and rA1 are not dirty (already calculated) so it calculates once and never looks back.

Public Function AddToA1(rAddTo As Range) As Double

    AddToA1 = rAddTo.Value + ActiveSheet.Range("A1").Value

End Function

In this function, you're using a value that was not passed to the function via its arguments. Excel puts this function after rAddTo, but it doesn't know that it should put it after A1. When it gets to calculating this function, rAddTo is properly calculated. But if A1 is not properly calculated because it lives further down the tree, then Excel reinserts this function after A1 where it will be calculated again.

I don't know all the inner workings of the calculation engine, but in my experience if you include every value you need in the arguments to the function, it will only calculate once.

Upvotes: 4

Related Questions