Eric,

This is an interesting post. I'm not able to fully answer without a bit more
info. You said "I want to trap changes to cells that affect my UDF"? Now, you
can do this using the change event with the code below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A2:G45")) Is Nothing Then
'Do something
MsgBox "Something changed in cells A2:G45."
End If

End Sub

Unless you can use this to solve your problem, I don't think this provides
the full solution. Can you answer the following, so that I can understand the
architecture of your problem further:

- Is your UDF being called in a cell, or via VBA code?
- If used in a cell, is it used in more than one cell?
- If used in different cells, does each instance reference a different range?
- What object are you trying to manipulate with the UDF?

Your problem in using the code I showed above, is that your UDF will be run
before the worksheet change event is fired, so could be tricky to do what you
want.

From my basic understanding of what you're doing, could you break up your
UDF so that you separate the calculation of the value that the UDF returns
from the element that manipulates this 'object' you talk about? I say this
because it sounds like the value the UDF returns doesn't depend on what cells
were changed, whereas the manipulation of this 'object' does. If you can
separate these operations, you could then move the 'object' manipulation part
to be triggered by the worksheet change event (and just have your UDF
calculate whenever any cell in the range changes).

Let me know if that helps, or you need to shed more light on the problem.


--
----------------------------
Please rate this post if it answers your question.

Thanks,

Chris
http://www.ProfessionalExcel.com


"EricG" wrote:

I have a UDF that refers to a range of data, for example, =My_UDF(A2:G45). I
would like to be able to relate a change in any cell in that range to the UDF
that is using that range, so that I can update the object the UDF is
controlling. I am trying to avoid using Application.Volatile because that is
overkill, but I want to "trap" changes to cells that affect my UDF.

For example, if I change the value of cell B24, then I would like to be able
to go into the Worksheet_Change event, relate the change in B24 to the UDF
that is referencing that cell (through the range A2:G45), and then do some
other stuff related to that UDF.

Question: Is there a way in VBA to determine what functions are referencing
any particular cell on a worksheet? I would like to be able to generate a
list of all the functions that use a particular cell. If my UDF uses a cell
that changes, I want to do some selective updating of the object controlled
by the UDF.

Thanks,

Eric