problem returning a range from a function

[Keith]

Hi,
It has been years that I’ve been looking for the following, and I’m still
looking.
I have a function that will be used in a spreadsheet. I want to have one
range (Range1) be the input range, with the result provided in a second range
(Range2). The function call would be this….

Public function MyRangesFunction (Range1 as range, Range2 as range)

Some code here


MyRangesFunction = some_value (wish it could be a specified range)

End function


The only way I’ve been able to do this is via a subroutine and some fancy
coding that speaks directly to the active sheet, etc.

Is there any way that I can easily return a range from a function?

Thank you,

Keith

 

[Mike H]

Hi,

A function can only directly change the cell it was called from and that
restriction applies to any sub routine called by your function.

The only way I’ve been able to do this is via a subroutine and some fancy
coding that speaks directly to the active sheet, etc.

Would you share this code with us?

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Peter T]

Not sure what you are really trying to do but maybe something like this -

Function rngOffset(rInput As Range, _
rowOffset As Long, colOffset As Long) As Range

Set rngOffset = rInput.Offset(rowOffset, colOffset)

End Function

for testing, put some numbers in C1:C3 and in this formula in other cell
=SUM(rngOffset(A1:A3,0,2))

If(?) the function is intended as a UDF (as proposed above) note that a UDF
can only return a value (or a reference). It cannot modify any input
argument, as it could if called by another procedure in VBA. Also a UDF
cannot change the interface in any way (a few tricks aside).

Regards,
Peter T

 

[Ron Rosenfeld]

Hi,
It has been years that I’ve been looking for the following, and I’m still
looking.
I have a function that will be used in a spreadsheet. I want to have one
range (Range1) be the input range, with the result provided in a second range
(Range2). The function call would be this….

Public function MyRangesFunction (Range1 as range, Range2 as range)

Some code here


MyRangesFunction = some_value (wish it could be a specified range)

End function


The only way I’ve been able to do this is via a subroutine and some fancy
coding that speaks directly to the active sheet, etc.

Is there any way that I can easily return a range from a function?

Thank you,

Keith

If I understand you correctly, you want your results returned into a number of
different cells.

In order to do that, with a function, you'll need to write it as an array
function (e.g. similar to LINEST which returns its results into an array of
cells).

When you enter the function, you'll need to either enter it as an array, over
the cells into which you want to have the results or; if the result cells are
not contiguous, then as multiple entries into those cells using the INDEX
function to return the answer you require.

So something like:

======================================
Public Function MyRangesFunction(InputDataRange as Range) as Variant
dim vResults()

somecode that returns e.g. 10 results

redim vResults(9)
for i = 0 to 9
vResults(i) = your_code_result i
next i

MyrangesFunction = vResults
End Function
========================================

You then array-enter this function into your "Range2" (or OutputDataRange)

--ron

 

[Keith]

Hi Mike,
Thank you. I'll see if I can dig out some of that code. It might take a
day or so.
Keith

 

[Keith]

Hi Ron,
Thank you. This looks promising. I'll work on this and see if I can make it
work for me.
keith

 

[Keith]

Hi Peter,
Thank you. this gives me some ideas to think about. will see if it leads
to a solution for me.
keith

 

[Ron Rosenfeld]

Hi Ron,
Thank you. This looks promising. I'll work on this and see if I can make it
work for me.
keith

Glad to help. Post back if you run into problems.
--ron