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)
caroline wrote:
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
--
caroline
--
Dave Peterson