problem building Function with worksheet name and range

[caroline]

hello,
I have a list of worksheets and would like to return certain values from
different ranges that I have named on each sheet
I have created this function by it does not work
=getvalue(G283,Parameter1,1,2) would return the value of row 1 column 2 in
range "Parameter1" in Sheetname written in G283.

Any idea? thanks a lot

Public Function GetValue(Ws As String, Pr As String, X As Double, Y As
Double) As Variant
Application.Volatile
Dim Ob1 As Object
Ob1 = Worksheets(Ws)
Dim Rn As Range
Set Rn = Ob1.Range(Pr)
GetValue = Rn(X, Y).Value
End Function

 

[paul.robinson]

Hi
This worked for me

Public Function GetValue(Ws As String, Pr As String, X As Double, Y As
Double) As Variant
GetValue = Worksheets(Ws).Range(Pr).Cells(X, Y).Value
End Function

No need to create the intermediate objects. You can't do Rn(X, Y). The
Cells(X,Y) refer to row X and column Y in Range(Pr).

regards
Paul

 

[Dave Peterson]

I think you're going to have to make a choice.

You're either going to have to pass strings to your function:
=getvalue(G283,"Parameter1",1,2)

And use something like:

Option Explicit
Public Function GetValue(Ws As String, _
Pr As String, _
X As Double, _
Y As Double) As Variant

Application.Volatile

Dim Ob1 As Worksheet
Dim Rn As Range
Dim myCell As Range

Set Ob1 = Nothing
On Error Resume Next
Set Ob1 = Worksheets(Ws)
On Error GoTo 0

If Ob1 Is Nothing Then
GetValue = "Missing worksheet!"
Else
Set Rn = Nothing
On Error Resume Next
Set Rn = Ob1.Range(Pr)
On Error GoTo 0

If Rn Is Nothing Then
GetValue = "Missing Range Name"
Else
Set myCell = Nothing
On Error Resume Next
Set myCell = Rn(X, Y).Cells(1)
On Error GoTo 0

If myCell Is Nothing Then
GetValue = "Invalid offset's"
Else
GetValue = myCell.Value
End If
End If
End If

End Function


============
Or pass it a range (like an address). But since a range already knows its
parent (the worksheet that owns it), you don't need to (and can't!) specify the
worksheet. (Well, I guess you could pass it as a parameter and ignore it if you
wanted...)

Option Explicit
Public Function GetValue(Pr As Range, _
X As Double, _
Y As Double) As Variant

Application.Volatile

Dim myCell As Range

Set myCell = Nothing
On Error Resume Next
Set myCell = Pr(X, Y).Cells(1)
On Error GoTo 0

If myCell Is Nothing Then
GetValue = "Invalid offset's"
Else
GetValue = myCell.Value
End If

End Function

Then you'd call your function like this if Parameter1 is a workbook level name:
=getvalue(Parameter1,3,5)

or if Parameter1 were a sheet level name:
=getvalue(Sheet2!Parameter1,3,5)