Another UDF Question

[EricG]

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

 

[Niek Otten]

Hi Eric,

If you don't mind, your post is a bit "vague" in some places;

<do some other stuff> What stuff>
<changes to cells that affect my UDF> affect HOW?
<object the UDF is controlling> ?????

If you include all cells that influence the result of your UDF in the
argument list (and then, of course, in the list of arguments of the function
call in the worksheet), then everything should work OK.
No need to trap events.
Note that functions can return a value to replace their call, nothing else.
They can not change cells, formats, really nothing at all. That is, as long
as they are called from worksheets, directly or indirectly.

If that doesn't answer your question, please post back and explain what you
are trying to achieve.

 

[EricG]

Nick,

Thanks for the response. Here are some more specifics:

1. The UDF takes the data in the referenced range and draws a dial gage
from it. The gage has from one to three scales, each scale having its own
needle pointing to the current value. The gage is the object I refer to.
The final value of the UDF is not relevant in any way. I use the UDF to draw
and update the gage.

2. In an earlier question, I tried to understand why my gage would not
update (redraw) when I had the RAND() function in one of the cells in that
gage's range. The RAND() function changed the value of the cell, but the UDF
did not trigger. I discovered that if I included the value of that cell in
the UDF's result, the gage would update. I could also add
Application.Volatile to the UDF, but that forces every UDF on the sheet to do
a complete update every time anything changes. However...

3. If one of the values in the gage's range changes, I don't always want to
redraw the entire gage - way too slow, so I'm okay with the fact that
updating one cell in the range will not necessarily force the gage to redraw.
Instead, I want to determine if the value of one of the gage's needles
changed (a specific cell in the range referred to by the UDF), and then only
adjust the needle's rotation. The gage is composed of a group of drawing
objects with a consistent naming convention that helps me refer to individual
components.

The trick is that when the Worksheet_Change event occurs (or
Worksheet_Calculate?), I want to see which cell or cells changed, figure out
which UDF is referencing those cells, determine which part of the gage is
impacted, and only update that part.

BTW,

If you include all cells that influence the result of your UDF in the
argument list (and then, of course, in the list of arguments of the function
call in the worksheet), then everything should work OK.

The above is not entirely true when the cells contain volatile formulas,
unless the change in those cells changes the value (result) of the UDF. At
least it seems to work that way.

Thanks for your help,

Eric

 

[ProfessionalExcel.com]

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]

Chris,

Thanks for taking the time to help. See my response to Niek about what my
UDF is trying to do. This project I'm working on has no specific purpose
just yet - I'm just tinkering, but hopefully it will prove useful in the end!
Specific answers to your questions:

1. The UDF is called in a cell on a worksheet.
2. It can appear in several cells on several worksheets, as the end user
desires.
3. Each instance does not necessarily refer to a unique range - there could
be complete or partial overlap of ranges between UDFs.
4. See my response to Niek for a description of the object.

Regards,

Eric

 

[ProfessionalExcel.com]

Eric,

You should be able to take advantage of the "DirectDependents" method (a
method of the range object), which returns a range object containing all
ranges that are direct dependents of a range (cell).

Using the code below, you can loop through the direct dependents of the
changed range via the Worksheet_Change event. Please note this only works
with dependents in the same worksheet. The code also sets a public range
variable, which you could use in the UDF to determine what needs updating in
your gauge object.

A side thought to all this is, I would try to make your UDF as efficient as
possible. Doing this could negate the need to do what you're trying to do
i.e. if the UDF is very quick, you don't have to be selective on what it
does. My main thought was, could you not determine the gauge settings and
check against the values in your range, before you made the changes to your
object? That's assuming you don't already do this. Also, keep in mind things
like:

- Convert your range that's passed to the UDF to an array and use this
instead of repeatedly referring to the range.
- Turn off things like screen updating, calculation, events where feasible.

Public rngJustChanged As Range
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngDirectDependencies As Range
Dim rngDD As Range

Set rngJustChanged = Target
Set rngDirectDependencies = rngJustChanged.DirectDependents

'Loop through direct dependent cells
For Each rngDD In rngDirectDependencies
'Run your UDF contained in the range rngDD.
'In the UDF you can use the range rngJustChanged, which
'is a public variable containing the cell just changed
Next

End Sub

I hope you can use this, or my suggestion of working on optimisation was a
useful one.

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

Thanks,

Chris
http://www.ProfessionalExcel.com