UDF Frustration - Won't Update

E

EricG

Okay, so I have a UDF which refers to a range on the same sheet. Something
like "=My_UDF(A3:G45)". The UDF pulls data from the range and performs some
calculations, returning a number to the cell containing the UDF.

One of the cells in the referred-to range as a RAND() function in it.
Whever I hit F9 (calculate), the value in this cell changes. However, my UDF
is not triggered. I know this because a) the value in the cell doesn't
change, and b) the break point in the UDF is never reached.

I would expect my UDF to be triggered any time that one of the values in its
range changes. I can manually type in a number to the cell with RAND() in
it, and the UDF updates. Why does it not update when due to the calculation
event?

Thanks,

Eric
 
K

K_Macd

A forced recalculate doesn't always work. I suggest that you look at help on
the Application.Volatile method
 
E

EricG

I think I figured it out. Even though I was sending a range to the UDF, the
value that was being placed in the cell occupied by the UDF was only
dependent directly on one value in that range. If I changed the value
calculation of the UDF to include the cell with the RAND() function, then the
UDF would update every time I did a calculation of the sheet.

Example:

=My_UDF(myRange)

In the UDF, I was setting My_UDF = myRange(24,2), but the cell with the
RAND() function was in myRange(26,2). When I changed to My_UDF =
myRange(24,2) + myRange(26,2), the UDF started updating automatically.

Thanks for your inputs!

Eric
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top