problem returning a range from a function

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

0
Utf
2/24/2010 1:13:02 PM
excel.programming 6508 articles. 2 followers. Follow

7 Replies
859 Views

Similar Articles

[PageSpeed] 16

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.



"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
> 
0
Utf
2/24/2010 1:35:02 PM
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" <Keith@discussions.microsoft.com> wrote in message 
news:248423E5-1147-4AAA-BE62-F362B5C89F39@microsoft.com...
> 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
> 


0
Peter
2/24/2010 1:51:02 PM
On Wed, 24 Feb 2010 05:13:02 -0800, Keith <Keith@discussions.microsoft.com>
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
0
Ron
2/24/2010 2:06:13 PM
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 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.
> 
> 
> 
> "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
> > 
0
Utf
2/24/2010 3:47:02 PM
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" <Keith@discussions.microsoft.com> wrote in message 
> news:248423E5-1147-4AAA-BE62-F362B5C89F39@microsoft.com...
> > 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
> > 
> 
> 
> .
> 
0
Utf
2/24/2010 3:49:01 PM
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 <Keith@discussions.microsoft.com>
> 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
> .
> 
0
Utf
2/24/2010 3:49:01 PM
On Wed, 24 Feb 2010 07:49:01 -0800, Keith <Keith@discussions.microsoft.com>
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
0
Ron
2/24/2010 6:08:59 PM
Reply:

Similar Artilces:

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

Problems with creating a newsletter
I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. Thank you, Markis www.adreamforabetterworld.com ...

CSV File Problem
Can any one explain why the following problem occurs please? A colleague has a small list of data which is a csv file exported from SQL. Column One is a serial number from 1 to 29. Column 2 is a number of transactions which have occurred.(it should look like example A below in Excel) 1 475 1,475 2 1732 21,732 3 1670 31,670 4 2176 42,176 5 608 5,608 6 1579 61,579 7 43101 743,101 8 54512 854,512 9 51258 951,258 10 61050 1,061,050 A B If I double click on the csv file, it opens in Excel but c...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

Customize Current View: Filter Problem
Hello, Using Outlook 2002, I have created a category called "Hidden" i outlook. In my calendar, I put all the appointments I don't want t appear in this calendar (i.e. daily reminders). I then filter out all these "Hidden" items by selecting -- View--> Current View--> Customize Current View--> Filter--> More Choices--> Categories--> Hidden (the category I created)--> OK--> SQL--> Edit these criteria directly--> And then I change: ("DAV:isfolder" = false AND "DAV:ishidden" = false) AN ("urn:schemas-microsoft...

Month problem
I have some code where I calculate this month minus 2 month. This goes fine until I get to august. When I use DateSerial to deduct 2 month I get to July, and this makes no sence. I made the following testcode. Sub StrangeMonth() Dim MyDate As Date MyDate = #8/31/2010# MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 1, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 2, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart(&qu...

Rounding Problem
I am using the following formula: ROUND((BH24-BG24)*24,1) Where BH24 = 15:21, BG24 = 15:00 (times based on a 24 hour clock) The answer returned is 0.3, but it should be 0.4. If you round out to 2 places the answer is 0.35. Unless I am missing something 0.35 should round to 0.4. If I use Roundup, it rounds everything up. What am I missing? Hi this is due to Excel's representation of numbers (see: http://www.cpearson.com/excel/rounding.htm) In your case the formula =(BH24-BG24)*24 does not return 0.35 but 0.3499999999999 (Just test it and format the resulting cell with enough decima...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Resolving Email Address to existing contact problem
Has anyone else come across this issue in MSCRM3.0? - when you attempt to resolve an email address to an existing contact in CRM (that does not currently have an email address stored in their details) the contact resolves OK but does not update the email address in the contacts details. Is this by design, or have I found a bug? ...

Offline folder problems #2
Hi Guys, Basically I installed Outlook 97 on my office PC which is connect to microsoft exchange server. For some reason all of my messages were copied into offline folders, this causes a problem, whenever I open up outlook I get this message. Upload of offline changes could not be completed you do not have sufficient permission to perform this operation on this object. See the folder contact or see your system administrator. I do not administrator access on my user profile, every time I open outlook it resets everything I have changed (view etc) and gives that error message. How ...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

Vlookup returns "0"
I am using the following formula =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE and it is returning a zero if there is no data found in that cell. If there is no data found I would like it to display nothing. How can I do this? Carolyn, There are a couple ways so do this. One is to test it, which makes for doing the VLOOKUP function twice. 1) =if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)=0, "" , VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)) This gets a little messy. Better yet, and simpler is to hide the original cell (column, whatever), an...

OLK 2k7
Outlook is behaving strangly with the "through the selected account" option. Each time I restart Outlook the rule fails. When I go in to check on the rules I get told that the rule is "invalid". and the "SELECTED" account is no longer selected. Each time the criteria the account needs to be selected by changes. For example with the following data Account Name Email Account mailserver.domain1.com user@domain1.com mailserver.domain2.com user@domain2.com One time I go in and it's asking me to select the account ...

outlook 2k2 problem
I am using Outlook 2002 with SP3, and i have not had any problems up until a week ago. My password does not seem to save even though i have typed it in correctly in the e-mail acct settings ,and have checked "save password. It saves it as long as i have Outlook open, but as soon as i close it, and then later open it again, my password is gone and Outlook asks me for my e-mail password. So, i re-type and i check "save password". But if i decide to close Outlook (say for the evening, and shut down my computer or something), my password disappears the next time i open Outlo...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

Outlook 2007 Search always returns no matches
Any search I do in Outlook 2007's new search box returns no matches. I watched the Indexing Status as it progressed, and now it says "Outlook has finished indexing all of your items" But, searching on anything, even the word "The" or "a" returns no results. I'm searching all Outlook items. I'm on an Exchange Server via VPN. Anyone have any suggestions? I uninstalled Office and re-installed and the problem went away... "Tom" <none@none.com> wrote in message news:%23yGP6NPOHHA.2140@TK2MSFTNGP03.phx.gbl... > Any search I do i...

Mother of a sumproduct (ish) problem!
At least it is to me - now I humbly beseech your magic :) A1 to A10 contain 10 unique letters, e.g. A,Q,E... B1 to B10 contain 10 unique letters, e.g. Z,B,A... C1 to C100 contain nonunique letters, e.g. E,E,F,E,Q,Q... D1 to D100 contain nonunique letters, e.g. A,T,F,A,X,B... E1 to E100 contain values, e.g. 9,1,3,5,7,2... In pseudo logic, for x = 1 to 10 I want to find each Ax:Bx pairing in Cy:Dy and sum E when matched. So I want to sum Ey for the rows where [Cy is "A" and Dy is "Z"], plus each Ey where [Cy="Q" and Dy="B"], etc. for the sample...

Hyperlink problem #3
I've got two workbooks on a shared drive with hyperlinks linking the two. When a user clicks on the hyperlink on the first workbook, it takes him to the second workbook. Fine. However, when the user clicks on the hyperlink in the second workbook to go back to the first, the error message says that that workbook is already open and it cannot open two files with the same name. Help is appreciated! I just tried a small test in xl2002 and it worked ok for me. I use Insert|Hyperlink to create the links. Are you sure that the hyperlinks point at the file you want--same folder and e...

CRM 3.0 Login Problems
Some specific users are constantly getting prompted for CRM login in Outlook. We are using desktop client (online) online. This happens randomly. We have CRM 3.0 with rollup update 2 and IE7. We have also applied this fix http://support.microsoft.com/default.aspx/kb/934243/en-us. Also added the crm site to local intranet zone. Please help. Thanks. set the authentication in IE check rollup update 2 -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "raj" wrote: >...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Spam is a big big problem
Spam is a big big problem ...

Problem with Database Wizard
I'm trying to generate a diagram based on the contents of an Access database, using the database to provide x,y coords for instances of a Master shape. It seems I need to run the wizard twice, first to link a master, then to generate the drawing. The first bit appears to work OK, but when I do the second bit, Visio says that there is no master in the stencil that it can use. But I know the master is connected, because if I modify the database, then refresh the shapes, they change accordingly. Does anyone have any idea why this isn't working for me ? I'm using Visio 20...

Strange problem
We have been rolling out new Windows 7 workstations (all new computers) at work over the last few months and a strange problem has developed. Prior to Windows 7 we were using Windows XP with Office 2007. The problem is, we have a workbook which uses a UserForm for filling in all required information. Prior to rolling out the new Windows 7 workstations, the workbook and UserForm were working flawlessly (for over a year) and they still work flawlessly on the first 3 Windows 7 workstations but on the last 2 workstations we rolled out if you open the UserForm and leave it open for ...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...