How to pass String and Range in UDF-CodePudding

For calculating purposes, I need to pass a worksheet name and specific (single) cell range in UDF and use them. As there is no way to pass the worksheet directly as an UDF argument, I would like to pass the worksheet name instead as string and the Range as the UDF arguments. In order to demonstrate the problem in simpler way, I have showed the following code only to get value of the range of that worksheet name by below:

Option Explicit
Function test(ws As String, r1 As Range) As Variant
test = Sheets(ws).r1.Value
End Function

Now If I enter =test("MySheet",C2) in another worksheet of the same workbook, it is showing #Value!. However, if I change the Vba code to the following:

Option Explicit
Function test(ws As String, r1 As Range) As Variant
test = Sheets(ws).Range("C2").Value
End Function

it is showing correct value form the C2 of MySheet worksheet. So I assume the problem is in passing the Range and using it in the Vba code. What is that problem? Anybody can help?

CodePudding user response：

If you are passing a Range object, then it already knows what worksheet it's in. You cant change what worksheet it references like that.

Instead, pass 2 strings if you want it to be dynamic.

Function test(ws As String, r As String) As Variant
  test = Worksheets(ws).Range(r).Value
End Function

and then:

=test("MySheet","C2")

You can, however, just use that cell's address as the string:

Function test(ws As String, r As Range) As Variant
  test = Worksheets(ws).Range(r.Address).Value
End Function

so you can use:

=test("MySheet",C2)