How to get range name inside fuction

[Gil D.]

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.

 

[Guest]

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

 

[Gil D.]

Hello,

Thank you for your help.
It solved my problem.

Gil D.

 

[Tom Ogilvy]

You need to do more testing. That won't solve your problem

Function cond_average(a, b, c)

x = "SumProduct(--(" & a.Address(0,0,xlA1,True) _
& "=" & b.Address(0,0,xlA1,True) & "), --(" & _
c.Address(0,0,xlA1,True) & "<> """"))"

should be what you are after.


It is true the the range reference is located on a specific sheet, but since
you are using the address property, this would not be visible to the
evaluate function unless you specified the sheet name - just like you have
to do in the worksheet.