Failure to Pass Arguments to a UDF in VBA for Excel 2007

Question

I have a complex iterative spreadsheet in which I want to use a UDF to calculate the heat transfer coefficient of a heat exchanger. The function has 5 arguments (2 strings and 3 doubles). The strings and 1 double pass just fine. The remaining two don't seem to go through at all. A watch on problematic arguments shows them to be equal to zero. I tried redefining the arguments as variants, and then I can find the value I want in the "value2" field. However, as soon as I try to access it (via varName.Value2) it disappears (the watch suddenly lists the field as "Empty").

The cells passed into the function for the problematic arguments are heavily dependent on the rest of the spreadsheet, and their value changes almost every iteration.

Has anyone seen this before and know of a workaround?

Answer 1

Hi Steven, I use UDFs with +6 input variables (the standard types as well as variants), and I have noticed (this is my interpreation of what happens) that sometimes the excel calc engine tries to trigger the UDF b4 all the input values actually become calced and available. From the debug view this is seen as the function making "false starts" and stopping to execute mid-procedure (thread is lost) so debugging-wise it shows a strange behaviour.

However, when written into a cell the function still calculates and returns correctly. Another key thing when using UDFs is to make sure that it modifies nothing but its return value, anything outside of that straight away breaks its execution (although admittedly it does not sound like this is the problem that you have). Hope this helps and good luck!