Excel UDF referencing a variable on the worksheet

[eikogs]

I have a User Defined Function that I am using to handle thousands of calculations on multiple worksheets. My UDF loads an array of values from a named range on the worksheet to preform the calculation. My issue is that the values I am pointing to on the worksheet can be different on each worksheet. When I select a new sheet the values on the ActiveSheet get applied to my UDF on all worksheets in the background. Is there an object I can reference to use the values that reside on the same worksheet as the UDF? Or some other work around?

I have multiple users that will use this UDF, so I want to limit the number of variables that need entered into the formula bar.

Here is a simplified chunk of my code:

Dim MyArray as Variant

MyArray = Range("myNamedRange")

If "this" then MyValue = MyArray (1,1)
If "that" then MyValue = MyArray (1,2)

 

[Rick Rothstein]

I have a User Defined Function that I am using to handle thousands of calculations on multiple worksheets. My UDF loads an array of values from a named range on the worksheet to preform the calculation. My issue is that the values I am pointing to on the worksheet can be different on each worksheet. When I select a new sheet the values on the ActiveSheet get applied to my UDF on all worksheets in the background. Is there an object I can reference to use the values that reside on the same worksheet as the UDF? Or some other work around?

I have multiple users that will use this UDF, so I want to limit the number of variables that need entered into the formula bar.

Here is a simplified chunk of my code:

Dim MyArray as Variant

MyArray = Application.Caller.Parent.Range("myNamedRange")

If "this" then MyValue = MyArray (1,1)
If "that" then MyValue = MyArray (1,2)

Try adding what I show in red above.

 

[eikogs]

Thank you! That was easier than I expected. Your help is very much appreciated.

 

[shg]

If you do that, Excel sees no dependence on MyNamedRange, so the formula will not recalculate when it changes.

 

[Rick Rothstein]

If you do that, Excel sees no dependence on MyNamedRange, so the formula will not recalculate when it changes.

Is there an alternative that will do what the OP wants and will recalculate the formula when MyNamedRange on that worksheet changes? I'm thinking adding Application.Volatile as a possibility, but that would make everyone of these UDF's volatile no matter what sheet they are on, correct?

 

[shg]

I think basic UDF hygiene is to pass all required arguments.

 

[Rick Rothstein]

I think basic UDF hygiene is to pass all required arguments.

So you are suggesting he modify his UDF to accept the sheet name as an argument and then modify each formula on each separate worksheet in order to pass that sheet name into the UDF... it is just a guess, but I think the OP was trying to avoid doing that.

 

[shg]

=MyUDF(myNamedRange)

 

[eikogs]

Thank you all for your continued discussion on this. For my case, the values in MyNamedRage are not directly accessible to the user. They get updated with other macros or selections. I am using a change event or line in the other macro to recalculate the sheet. Application.Caller.Parent. worked for me.