Returning an Array from a called function within a macro

How do I get a function to return an array. [I know that it should be
doable since the various matrix functions return an array.]

To be more precise I need to do this within a macro. I know how to do
it at the spreadsheet level.

I am running Excel 2004 on a MAC [OSX]

Thanks
Brujolito

0
brujolito (8)
5/18/2007 2:39:57 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
670 Views

Similar Articles

[PageSpeed] 40

One way:

    Public Function foo() As Variant
        foo = Array(1,2,3,4,5)
    End Function

You can then array-enter the function into, say A1:E1,

    =foo()

to get the values 1->5 into those cells, or use 

    =SUM(foo())     ===> 15

You can obviously get much more complicated...


In article <1179499196.938387.84040@o5g2000hsb.googlegroups.com>,
 "brujolito@gmail.com" <brujolito@gmail.com> wrote:

> How do I get a function to return an array. [I know that it should be
> doable since the various matrix functions return an array.]
> 
> To be more precise I need to do this within a macro. I know how to do
> it at the spreadsheet level.
> 
> I am running Excel 2004 on a MAC [OSX]
> 
> Thanks
> Brujolito
0
jemcgimpsey (6723)
5/18/2007 2:47:32 PM
Reply:

Similar Artilces:

Macro Question Again
I'v never had any dealings w/ macro before, so this is all new to me. I have a windows 98 computer that is running office 2000 and there is an excel macro on this pc that was created by someone years ago. I have to get this 98 machine of the network and replace it w/ a 2000/XP platform also running either office 2000/xp. So far i'v copied the macro and am able to run it, but its not gathering the correct info. It is collecting the correct info on the 98 machine, just not the one im replacing it w/. It looks like some of the fields are supposed to be populated w/ info in a text d...

Macro looping?
How do I code a macro so that it loops until it goes through all of the information on the excel file? This way I do not have to rewrite the same code for 20 lines one day and then 30 lines the next day. sub loopit() lastrow=cells(rows.count,"a").end(xlup).row for i= 1 to lastrow cells(i,1)=?? next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "klh84" <klh84@discussions.microsoft.com> wrote in message news:7E2B2C07-1D80-4D28-9D05-4BA024BF8669@microsoft.com... > How do I code a macro so that it loops...

Question on Returns to Vendor without PO & Receipt number
Our company switched to Great Plains Dynamics from Simply Accounting 3 months ago. We currently need to return some of the inventory items to our vendors. However, when we try to enter a transaction entry in Purchasing/Returns Transaction Entry, in the detail lines we are asked for the PO number or the receipt number. We are not allowed to go further or save without the PO number or receipt number. When we converted from Simply Accounting, all we entered was the beginning balance of the vendors and only the PO's which we expect to receive. The older PO's were not carried over. W...

Lookup Function in user form
I have a combo box (cboitem1) and a text box (txtprice1). I am using the code below to have txtprice1 fill with the corresponding price the worksheet: Private Sub Cboitem1_Change() If cboItem1 <> "" Then txtprice1.Value = Excel.WorksheetFunction.VLookup(cboItem1.Value, Sheets("Pizzas").Range("A1:B65536"), 2, False) End If End Sub It works perfectly when you enter an existing value in cboitem1. However if 'the user' accidently types a letter that is not the first letter of one of the existing items VB debugging pops up with error &qu...

Track in CRM function
Hello. Upon selecting the “Track In CRM” button from the CRM line in Outlook I have two questions based on situation: 1) If the contact exists in CRM it appears to attach the email but provides no notice unless you try to attach it again? True? 2) If the contact does not exist it attaches the email where? In v1.2 it opened a window and made the user create a record. I think it is attaching or tracking the email in CRM somewhere because trying to do it again, I'm asked if you want to attach/track it again. Does this make sense? By the way, we are using CRM 3.0 Thanks. After c...

URL within email
I wish to be able to send an e mail with an active URL so that when the recipient recieves e mail they are taken to the web address once it is clicked. Currently I get an error message unable to open Http://*****.co.uk cannot locate internet sever. Helpfull information appreciated ...

Set up a script for a live performance with a cue call sheet
How do I set up a single one-sided sheet to follow a live performance script and be able to write stand-by notes and cues in a column beside the script with a timecode format ...

Tracked changes function misbehaving
I have a simple workbook (2002) in which the Track Changes feature is enabled. End users have reported periodic problems with the contents of cells "mysteriously" changing. In process of investigating these, have found that when tracked changes are highlighted, the comments that describe changes are not always associated with the correct cell. Example, comment on cell B114 states "Changed cell B114 from '<blank>' to '12345'". In fact, contents of cell B114 is "67890" and contents of cell B104 is actually "12345". Has anyone...

how do I recover charts within an excel workbook?
The workbook had many charts that were previously visible. Now, upon opening the file, the charts are no longer visible. How to I reactivate them to appear? thanks ...

Outlook macro question
I have two email accounts in Outlook 2003. I know about the Accounts drop list in new emails. But is it possible to create a few macros so that I end up with two buttons where each runs a macro that starts a new email using a specific account? One button would start a new email using account 1 and the other button would start a new email using account 2. If so, could someone please give me some pointers? Thanks. Outlook doesn't provide any direct way to change the account for an = outgoing message in versions before Outlook 2003. See=20 http://www.outlookcode.com/codedetail.aspx?id=3D88...

Find and Replace within an XML file
I was wondering if there is a simple method of replacing characters within the xml document. I'm looking at writing a Biztalk Pipeline that escapes illegal characters before they are processed by a HL7 assembler. I.E. replace all instances of &amp; or & with \T\ . I thought about converting to a string and using the replace function, but that would mean knownig all the HTML codes for characters (as the escape characters are to be dynamically chosen by the user). Where as I could via xpath ??? replace all instances of the original character such as &. Hope it makes sen...

ON_UPDATE_COMMAND_UI handler does not get called
Hello, For some reason, my ON_UPDATE_COMMAND_UI handler does not get called. I am using the usual syntax: in the .cpp file: ON_UPDATE_COMMAND_UI(ID_EDIT_CLEAR, OnUpdateEditClear) void CDrawView::OnUpdateEditClear(CCmdUI* pCmdUI) { pCmdUI->Enable(!m_selection.IsEmpty()); } in the .h file: afx_msg void OnUpdateEditClear(CCmdUI* pCmdUI); The other handlers (for example for ID_EDIT_CUT) work normally Thanks for advice Vaclav "Vaclav" <vjedlicka@atlas.cz> wrote in message news:u5Wc3LKJIHA.2480@TK2MSFTNGP05.phx.gbl... > Hello, > > For some reason, my ON_UPDATE_...

Adding Hyperlink to multiple values within a cell
My spreadsheet contains a list of people. The cell next to each nam contains multiple numeric values for identifying a specific piece o information. I would like to be able to click on one of those number (value) and a comment window pop up with the information associate with it, or be hyperlinked to the information further down th speadsheet. I want to avoid using multiple cells for this. Is this possible? Thank -- t2tru ----------------------------------------------------------------------- t2true's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=387 View this...

macro to generate next number
Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! Hi, Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still suffici...

List of kernel mode compatible functions
Hi, Is there a list of kernel mode compatible C/C++ functions (preferably for WDK 7600)? Thanks, Jan Wouldn't that be the WDK Help file? Thomas F. Divine "Jan M" <jan_m@jerseymail.co.uk> wrote in message news:e7fhXcanKHA.1548@TK2MSFTNGP02.phx.gbl... > Hi, > > Is there a list of kernel mode compatible C/C++ functions (preferably for > WDK 7600)? > > Thanks, > > Jan > Your right of course. I just didnt look hard enough. Jan ...

Set Out of Office (or a rule) to only function on weekends
Hello, I am trying to set up so that every weekend my internal e-mail gets forwarded to my home(smartphone) account. I can only find options to run a rule or out of office assistant to run for specific date ranges. How do I set up either a rule or the out of office assistant to 'push' mail to me only on weekends? HCA.CWT.RN wrote on Fri, 02 April 2010 09:04 > Hello, I am trying to set up so that every weekend my internal e-mail gets > forwarded to my home(smartphone) account. I can only find options to run a > rule or out of office assistant to run for spec...

FORECASTING SALES (please let this be a worksheet function)
Hi I have recently been given the task by my boss to forecast one of ou key customers monthly usage until monthly until end of 2006, I am starting with this customers indidual branches monthly usage fo the past 3 years, Some branches will have opened and closed during thi time, And i am looking to be able to predict monthly usage for the nex 18 months Has anyone got any idea's on the best way to forcast within excel, I a currently using trend (fomulae given to me on this sight) but people ar saying this is not the best way Exponentionally has been mentioned by a few of my collegues but...

Using a OR() like function in an IIF statement
Hello, I am trying to create an IIF statement to test if the first character in a field is a 1,2,8 or 9. Something like the following: IIf(Left([possible_SO_match],1)="1 or 2 or 8 or 9",[Possible_SO_Match],"No Match") Is there a way to create it without going to a 4 level nested IIF statement? Thanks, Kerry -- Message posted via http://www.accessmonster.com kkulakow via AccessMonster.com wrote: > Hello, I am trying to create an IIF statement to test if the first > character in a field is a 1,2,8 or 9. > Something like the following: > > IIf(Left([possib...

Simple question about text within a cell
I can't seem to find the option that prevents text from covering the adjacent cell. For example if I type the folling in cell A1: All I want for Christmas is my two front teeth. Obviously that will overflow over the cell B1 (if there is no data in B1). I don't want to resize my column, I only want the cell to show as much text in A1 as possible without covering cell B1. I believe I've seen the option to do what I'm looking for but I can't seem to find it and I can't figure out how to look it up in the HELP file. Thanks in advance. Rick My simple solution ha...

macro warning
I have a workbook that consists of 15 worksheets. One for each month and then a summary page for the year plus a couple of worksheets containing charts. Every time I open it I'm prompted that the workbook contains macros. I haven't created any macros. I go to tools>macros and bring up the window no macros are shown. Anybody have any ideas why this is happening? Not really a show stopper but rather irritating. gls858 One possibility is that you need to delete any empty modules that may have housed macros. Hit ALT+F11 and this will open the VBE (Visual Basic Editor) Top left you wi...

HELP! Record Macro in TOOLS > MACRO is gone!
I was in Customize function trying to enable the Stop Recording toolbar and now in TOOLS > MACRO the Record Macro option is gone. I'm working on a project and I need this back. Can someone help? I'm using Excel 2003. Thanks, Joe M. Try this: In the customize window > click the Options tab > push the reset toolbars & menus button. Exit excel and re-load it Micky "Joe M." wrote: > I was in Customize function trying to enable the Stop Recording toolbar and > now in TOOLS > MACRO the Record Macro option is gone. I'm working on a ...

macro-import another xls file
Hi, i need a macro to import another xls file.When i run macro i need a window to appear and to browse to the file i need to import. I allways open another file, so i want to browse for the file. Can this be done? Thanks! Try some code like the following: Sub AAA() Dim FName As Variant Dim Filter As String ' Excel 2003 workbooks Filter = "Excel Files (*.xls),*.xls" ' Excel 2007 workbooks workbooks Filter = "Excel Files (*.xlsx;*.xlsm;*.xlsb),*.xlsx;*.xlsm;*.xlsb" ' Excel 2003 and 2007 Filter = "Excel Files (*.xls;*.xlsx;*.xlsm;*.xlsb),*...

Name range within a name range
In Excel is it possible to create a name range that contains multiple name ranges. As an example, I have created several name ranges with one cell reference, such as Sales(A2), COS(A5), ADM(A7), SELLING(A8), MRKT(A9). I would like to create a name range that I could include the all the name ranges and call it GROSS_PROFIT. If later I add another range I would like to be able to just add the range name to GROSS_PROFIT, such as R&D. Also the reason I chose to use named ranges is my cell references can change from month to month, I find it easier to create formulas using name refer...

Excel, Macro designed to send mails via Outlook : question on certificate
Hi everybody, I created in Excel a macro designed to send specific mails via Outlook. Because an Outlook dialog box asked me all the time whether I authorize the transmission or not, I created a certificate using MS Office "SelfCert". So far so good. My macro is signed. However, Outlook keeps on asking me everytime to authorize the transmission of the mails. What should I do / check ? Anybody ? Many thanks in adavance for your time and kind help. Daniel Everything you wanted to know about sending emails fro Excel using Outlook. http://www.rondebruin.nl/sendmail.htm Go...

remove carriage return in multiple cells
Hello, I have an excel sheet with lots of data. Unfortunately lots of cells contain carriage returns. Is there a way to remove the carriage returns in those cells (more returns in a cell possible)? Thanks, Andy > Andy Select your range, then CTRL+H. In Find What, type 0010 (number keypad) while holding down ALT. Click Replace All. HTH, Andy Try running this macro: Sub RemoveCRLF() Cells.Replace Chr(10), "" Cells.Replace Chr(13), "" End Sub Afterward you may want to turn off Wrap Text (under Format, Cells, Alignment) for the affected cells. -- Jim...