Returning range object from custom functions

A

agarwaldvk

Can anyone tell me how to return range objects from custom functions?

The idea is this :=

If we were to use the built-in Excel function sum like "=sum(e4)", it
will return the value contained in the cell e4. No problem there!
However, if we were to use a function like "sum(address(5,4)), it
returns an error.

My interpretation to this is that the built-in Excel function
"address()" returns the address as a string and not a range object and
the built-in Excel function sum() requires a range object to be passed
as an argument to it. To overcome this limitation, my intention is to
be able to write a custom function whereby I can return a range object.
If I were to be able to do that, then the above problem could be
addressed like so :=

"=sum(myFunc(single cell range passed as argument for simplicity))" -
presume that my custom function myFunc returns the range object!

This function may seem useless at the moment but this is just the
starting point to what I eventually want to do, which I can explain if
someone really want to know but as such is not required to provide a
solution to my query.

Best regards


Deepak Agarwal
 
S

Stan Scott

To use the string returned by the ADDRESS function, use the INDIRECT
function, like this:

=SUM(INDIRECT(ADDRESS(5,4)))

Stan Scott
New York City
 
T

Tom Ogilvy

Public Function MyFunc(rng as Range, i as Long) as Range
set MyFunc = rng.offset(0,i)
End Function
 
A

agarwaldvk

Thanks guys. Scott, I have tried yours - works ok!
Tom, I am going to try your suggestion now. I am sure it wil
work!.Thanks again

Deepa
 
Top