It should not matter:

Consider:

Function outrange(R As Range) As Double
MsgBox (R.Address)
outrange = R.Value
End Function

=outrange(Sheet2!A1:A1)
and
=outrange(Sheet1!A1:A1)
will both message $A$1

but the first call will return Sheet2's value and the second call will
return Sheet1's value

--
Gary''s Student


"Gil D." wrote:

Hello,

I have a function which gets 3 range parameter.
I am trying to get range names inside the function in order to use
Evaluate function.

Function cond_average(a, b, c)

x = "SumProduct(--(" & a.Address & "=" & b.Address & "), --(" &
c.Address & "< """"))"

MsgBox x

If Evaluate(x) = 0 Then
cond_average = 20
Else
cond_average = Application.SumIf(a, b, c) / Application.CountIf(a,
b)
cond_average = 1
End If

End Function

My problem is that in the message box I see that a.Address (for
example) displays only cells range but not the worksheet name. For
example:

When using: cond_average(Sheet1!A2:A5,Sheet2!A3,Sheet1!B2:B5)
a.Address is A2:A5 and not Sheet1!A2:A5
b.address is A3 and not Sheet2!A3
c.address is B2:B5 and not Sheet1!B2:B5

What is wrong ?

Thank you
Gil D.