Returning range object from custom functions
 

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


---
Message posted from http://www.ExcelForum.com/

Returning range object from custom functions
 

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

"agarwaldvk " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

Returning range object from custom functions
 

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

--
Regards,
Tom Ogilvy

"agarwaldvk " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

Returning range object from custom functions
 

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

--
Message posted from http://www.ExcelForum.com