Tuesday, 20 August 2013

Defining a dependency that is not one of the input parameters for a UDF?

Defining a dependency that is not one of the input parameters for a UDF?

In short: Is there a way to define a dependency that is not one of the
input parameters for a UDF?
This is my first post here. I'm learning VBA in Excel and I've seen a
large number of very talented coders posting here, so here it goes:
I want to know how to make a UDF that depends on a cell range, but the
user does not have to input this cell range. A simple example would be a
function that always takes cell "A1" and concatenates the input to A1 as
the output. If the range was statically referenced inside a UDF, then when
cell "A1" is erased by the user, the range wouldn't turn into "#REF".
If A1 = "Hello", and then the input is " World", I want the output to be
"Hello World".
I thought maybe I could call a function within a function and the
dependency tree would build based on that (see below), but my test
function won't update unless I change the input parameter for the first
function called (in1).
Test code:
Public Function test(in1 As String) As String
test = testdep(in1, Sheets("Sheet1").Range("A1"))
End Function
Private Function testdep(in1 As String, rng As Range)
testdep = rng.Value & in1
End Function
Ideas?
Edit1: To be more specific, I want the function to update the output value
when cell "A1" is changed, even though cell A1 is not an input parameter.
E.g. I change A1 to now be "blah" instead of "Hello", then the value would
update to "blah World". I would make the function volatile, but I have a
lot of functions like that and the calculation speed gets very groggy.

No comments:

Post a Comment