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

Maybe you can force it to calculate:
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"EricG" wrote:

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

A forced recalculate doesn't always work. I suggest that you look at help on
the Application.Volatile method
--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"ryguy7272" wrote:

Maybe you can force it to calculate:
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"EricG" wrote:

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

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

"EricG" wrote:

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