Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem returning a range from a function
Hi,
It has been years that Ive been looking for the following, and Im 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 Ive 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem returning a range from a function
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 Ive 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. "Keith" wrote: Hi, It has been years that Ive been looking for the following, and Im 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 Ive 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem returning a range from a function
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 "Keith" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem returning a range from a function
On Wed, 24 Feb 2010 05:13:02 -0800, Keith
wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem returning a range from a function
Hi Mike,
Thank you. I'll see if I can dig out some of that code. It might take a day or so. Keith "Mike H" wrote: 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 Ive 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. "Keith" wrote: Hi, It has been years that Ive been looking for the following, and Im 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 Ive 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem returning a range from a function
Hi Ron,
Thank you. This looks promising. I'll work on this and see if I can make it work for me. keith "Ron Rosenfeld" wrote: On Wed, 24 Feb 2010 05:13:02 -0800, Keith wrote: Hi, It has been years that Ive been looking for the following, and Im 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 Ive 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem returning a range from a function
Hi Peter,
Thank you. this gives me some ideas to think about. will see if it leads to a solution for me. keith "Peter T" wrote: 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 "Keith" wrote in message ... 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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem returning a range from a function
On Wed, 24 Feb 2010 07:49:01 -0800, Keith
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem - Lookup returning value of last cell in range when no matchoccurs | Excel Programming | |||
IF Function Returning Range | Excel Discussion (Misc queries) | |||
IF Function returning range | Excel Discussion (Misc queries) | |||
Problem with XIRR function returning #NUM! | Excel Worksheet Functions |