Debugging a UDF - Strangeness?

[TomCon]

Can somebody explain/clarify this behavior for me?

I have a UDF in cell AK3 (as well as many other calls to same UDF). So, i set a watchpoint to break if parent.caller.address="$AK$3". I change the cell formula in AK3, and it hits the watch value and breaks. This workbook is large, and the recalc takes a few minutes, but eventually ends. When the watch point break is hit, i set a breakpoint at the "End Function" line, continue execution forward to that line, and in the immediate window, use ?function_name to see the value of the function. It seems correct.

But, when i get back to the sheet after recalc completes, the cell value is NOT the same as the value i saw in the immediate window for the function value.

What could possibly be changing? The watch point is not hit again, so how is the end resultant value that ends up in the sheet cell, not the same as what i see in the immediate window?

Appreciate any explanation/clarification!

Thanks,
tom

 

[Sektor]

Parent.Caller won't work 'cause in this case "Parent" is Workbook, and it doesn't have "Caller" property. You should write this:

Application.Caller.Address
' Or
Application.ThisCell

 

[TomCon]

I did some experiemnts. I find that all of the following will return the same text string as their result, the address of the calling cell. This can be observed by putting a breakpoint in the UDF.

Parent.caller.address
Application.caller.address
Application.thiscell.address

At this point, i have deleted the sheets with what i would call "hard to understand behavior" and i am trying to recreate them to see if i see the same issue. But, if anybody has any further thoughts that would explain what i observed, please let me know.

Thank you!
Tom

 

[xenou]

What is the UDF?

ξ

 

[mikerickson]

...i set a breakpoint at the "End Function" line, continue execution forward to that line, and in the immediate window, use ?function_name to see the value of the function. It seems correct.

But, when i get back to the sheet after recalc completes, the cell value is NOT the same as the value i saw in the immediate window for the function value....

When you hit the "End Function" breakpoint, do you F5 to contintue?

 

[TomCon]

Well, i did eventually use F5 to continue. First i did ?function_Name in the immediate window when the breakpoint was hit, and that is the value I expected was being returned to the sheet as the value for the cell. But, as i said, after waiting a while (the recalc of this workbook takes a few minutes), eventually when recalc finished, the cell value was not the same value i saw in the immediate window as the value of the function. But, the watchpoint was still set, so i do not believe the cell recalced again after i hit the breakpoint once. At least that was my interpretation which is why i was mystified.