Passing Excel NAMED Range to VBA

[Guest]

Given a NAMED range called "Sheet1_Table" that refers to "Sheet1!e1:h4" and
the value "Sheet1_Table" in cell C1, how can I get the following VBA function:

Function say_what(ByRef input_arg As Variant) As Variant
If TypeName(input_arg) = "Range" Then
say_what = input_arg.Rows.Count & " by " & input_arg.Columns.Count
Else
say_what = TypeName(input_arg)
End If
End Function

to return the same result for =say_what( indirect(c1) ) and =say_what( c1 )?

say_what( indirect(c1) ) returns a correct "4 by 4" but say_what( c1 )
returns an incorrect "1 by 1".

Thank you,



How can I get

 

[Trevor Shuttleworth]

I'd say what the two calls return are correct.

You could also try: =say_what( Sheet1_Table ) = 4 by 4

Regards

Trevor

 

[Guest]

Hi Trevor,

Thank you for your interest!

You are correct - i.e., both calls return results and =say_what(
Sheet1_Table ) returns 4 x 4. However, my question is: How do I make
=say_what( c1 ) produce the same result as =say_what( INDIRECT(c1) )? That
is, what do I need to do in the say_what finction to emulate the INDIRECT
function?

Thanks again, BG

 

[Trevor Shuttleworth]

BG

I'm not sure you can do what you want to do. When you pass the function the
range C1 you *are* passing it a 1x1 range, that is one cell. When you pass
it INDIRECT(C1), you are saying "pass the function the range *pointed to* by
the cell C1", in other words, Sheet1_Table ... which is a 4x4 range.

Thought of testing Range(input_arg) but I'm not making any progress. Sorry

Regards

Trevor

 

[Guest]

Thank you for your efforts!

BG

I'm not sure you can do what you want to do. When you pass the function the
range C1 you *are* passing it a 1x1 range, that is one cell. When you pass
it INDIRECT(C1), you are saying "pass the function the range *pointed to* by
the cell C1", in other words, Sheet1_Table ... which is a 4x4 range.

Thought of testing Range(input_arg) but I'm not making any progress. Sorry

Regards

Trevor