Calculation order for UDF's

[Jacc]

I just put together a really slick little UDF that works brilliant. As usual when I enter the VBA world there is a lot of testing and troubleshooting before it works.

As part of testing I used the debug.print command to see what was going on with various variables. It made no sense at all, the values were all wrong!

So I tested debug.print c.row where c is one of the input cells to the UDF on the same row.
I expected to see 2, 3, 4, 5, 6, 7 etc as the UDF was just copied down a column.
To my surprice the row numbers where all scrambled???

What is going on here? Does this reveal the true calculation order based on other formulas in the workbook? Still, the order makes no sense.

[MrShorty]

I could replicate the behavior with a simple UDF like

Please Login or Register  to view this content.

I put =RAND() in column A (rows 1 to 149), and the UDF in column B. I suspect that this is revealing the actual order that Excel is calculating the spreadsheet. I do not claim to understand the internal workings of Excel's calculation engine. I agree, in this scenario, I don't understand why it does not just start at the top and work down. Even if I don't understand the algorithm behind it, I do notice that it seems to use the same calculation order each calculate event. It always started in row 146 (4th from the bottom) and did every 4th row on its way to the top. Then start near the bottom again and work towards the top in a different pattern, and so on. Interestingly, it always calculated row 1 last.

I thought it might have to do with multi-threading, where different copies of the UDF were being calculated in different threads, but I disabled multi-threaded calculation in Excel Options, and observed no change.

I suspect it does reveal something about the true order of calculation. I think it shows that Excel does not use a simple top to bottom left to right algorithm when it does not matter, though I cannot say what algorithm Excel is using to decide what order to perform the calculations.

[MrShorty]

I decided to try a couple of simple changes to see if I could change the calculation order.

I tried to force column A to calculate from top to bottom. A1 =RAND(), A2 and down =RAND()+A1. Column B's calculation order still seems random, but it is a different random than the previous test. Again, it is repeatable.

I tried to force column B to calculate from top to bottom with a similar change. B1 =half(A1,row()), B2 and down =half(A2+B1,row()). This time, the calculation order is in order from 1 to 149.

[Jacc]

Nice to get some feedback on this seemingly boring post! Even nicer that you could replicate it, I didn't expect either.

So at least this seems to be "normal" behavior by Excel then. Thanks for the feedback!

[Jacc]

Oh, I see. Well for the record my UDF did not contain ROW(), just a reference to a cell on the same row.

[rorya]

AFAIK, you cannot get VBA UDFs to use multiple-threads.

[Jacc]

I mean, I didn't use ROW() as input like you do.

[MrShorty]

So, I changed my test udf to pull the row from the argument

Please Login or Register  to view this content.

Now the calculation order seems to be top to bottom. So this changes the order Excel chooses to perform the calculation. Again, it is consistent from calculation event to calculation event.

[Jacc]

Yup, I recognize the patterns. This is what I get now:

Please Login or Register  to view this content.

(this has got to be my nerdiest post ever)

[Jacc]

I love this thread!