Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another UDF Question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another UDF Question
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. -- Kind regards, Niek Otten Microsoft MVP - Excel "EricG" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another UDF Question
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 "Niek Otten" wrote: 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. -- Kind regards, Niek Otten Microsoft MVP - Excel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another UDF Question
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another UDF Question
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another UDF Question
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 "EricG" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|