Passing an Array created in a Function or Subprocedure back to the calling Subprocedure

I want to have a Function or Subprocedure that can create an array with
52 specific values in it. I then want to call this Function or
Subprocedure from a main Subprocedure and be able to access the
contents of the array. Everything can be of type variant for the
purposes of this exercise. Can anybody help, please?

0
3/15/2006 5:39:36 AM
excel 39879 articles. 2 followers. Follow

7 Replies
430 Views

Similar Articles

[PageSpeed] 59

This worked ok for me:

Option Explicit
Sub testme()
    Dim myArr As Variant
    Dim iCtr As Long
    
    myArr = BuildArray(myArr)
    
    For iCtr = LBound(myArr) To UBound(myArr)
        MsgBox iCtr & ".  " & myArr(iCtr)
    Next iCtr
End Sub

Function BuildArray(SomeArray As Variant) As Variant
    Dim iCtr As Long
    ReDim SomeArray(1 To 52)
    For iCtr = 1 To 52
        SomeArray(iCtr) = iCtr & "--Hi there"
    Next iCtr
    BuildArray = SomeArray
End Function


Cloudfall wrote:
> 
> I want to have a Function or Subprocedure that can create an array with
> 52 specific values in it. I then want to call this Function or
> Subprocedure from a main Subprocedure and be able to access the
> contents of the array. Everything can be of type variant for the
> purposes of this exercise. Can anybody help, please?

-- 

Dave Peterson
0
petersod (12005)
3/15/2006 12:13:04 PM
Or maybe this is closer.  (I'm kind of confused over what calls what.)

Option Explicit
Sub testme()
    Dim myArr As Variant
    Dim iCtr As Long
    
    myArr = BuildArray(myArr)

    Call ShowArray(myArr)
End Sub

Function BuildArray(SomeArray As Variant) As Variant
    Dim iCtr As Long
    ReDim SomeArray(1 To 52)
    For iCtr = 1 To 52
        SomeArray(iCtr) = iCtr & "--Hithere"
    Next iCtr
    BuildArray = SomeArray
End Function

Sub ShowArray(SomeArray As Variant)
    
    Dim iCtr As Long
    If IsArray(SomeArray) Then
        For iCtr = LBound(SomeArray) To UBound(SomeArray)
            MsgBox SomeArray(iCtr)
        Next iCtr
    Else
        MsgBox "not an array"
    End If
End Sub




Cloudfall wrote:
> 
> I want to have a Function or Subprocedure that can create an array with
> 52 specific values in it. I then want to call this Function or
> Subprocedure from a main Subprocedure and be able to access the
> contents of the array. Everything can be of type variant for the
> purposes of this exercise. Can anybody help, please?

-- 

Dave Peterson
0
petersod (12005)
3/15/2006 12:15:55 PM
Hi again Dave,

This worked in my application. I'm trying to figure out how it works. I
think it works as follows:

1. When you declare "myArr" in the calling subprocedure "testme()", its
just a variable and not an array yet.
2. When you call the function "BuildArray" you pass it the variable
"myArr" and "BuildArray" turns the variable "myArr" into a variable
array when it re-declares it as such.
3. You then assign the created array to the function "BuildArray" which
is passed back to the calling variable "myArr".
4. What I don't understand is how the variable "myArr" turns into a
variable array when it hasn't been declared as such!
5. I understand the rest of the above.

(By the way, are you Australian? If not, where are you from?)

Thank you for the trouble you have gone to to help me. I really
appreciate your expertise in matters of VBA.

Regards,

Terry.

0
3/16/2006 5:17:32 AM
Hi Dave,

As I said in my reply to your first posting, that worked. No doubt this
would too, but what I wanted was what you initially provided.

Thanks again for your help.

Regards,

Terry.

0
3/16/2006 5:19:53 AM
#2.  I didn't need to pass it anything.  This worked just as well:

Option Explicit
Sub testme()
    Dim myArr As Variant
    Dim iCtr As Long
    
    myArr = BuildArray
    For iCtr = LBound(myArr) To UBound(myArr)
        MsgBox iCtr & ".  " & myArr(iCtr)
    Next iCtr
End Sub

Function BuildArray() As Variant
    Dim iCtr As Long
    ReDim SomeArray(1 To 52)
    For iCtr = 1 To 52
        SomeArray(iCtr) = iCtr & "--Hi there"
    Next iCtr
    BuildArray = SomeArray
End Function

This may make it easier to see how #4 works.  Since myArr is a Variant (which
can hold anything) and BuildArray passes back an array), then myArr becomes an
array.

I guess it was more muscle memory than anything to pass it the parm--it wasn't
necessary and shouldn't have been passed.  

I'm in the middle of the USA.

Cloudfall wrote:
> 
> Hi again Dave,
> 
> This worked in my application. I'm trying to figure out how it works. I
> think it works as follows:
> 
> 1. When you declare "myArr" in the calling subprocedure "testme()", its
> just a variable and not an array yet.
> 2. When you call the function "BuildArray" you pass it the variable
> "myArr" and "BuildArray" turns the variable "myArr" into a variable
> array when it re-declares it as such.
> 3. You then assign the created array to the function "BuildArray" which
> is passed back to the calling variable "myArr".
> 4. What I don't understand is how the variable "myArr" turns into a
> variable array when it hasn't been declared as such!
> 5. I understand the rest of the above.
> 
> (By the way, are you Australian? If not, where are you from?)
> 
> Thank you for the trouble you have gone to to help me. I really
> appreciate your expertise in matters of VBA.
> 
> Regards,
> 
> Terry.

-- 

Dave Peterson
0
petersod (12005)
3/16/2006 12:15:24 PM
Thanks again Dave,

Everything is crystal clear.

"Dim SomeArray(1 To 52)" works for me as well.

My Function is funtioning, it's Friday and my wife and I are playing
tournament no-limit Texas Hold'em tonight, and I'll be watching the
football on TV with a bet on my team after that, my wife's sister is
visiting from England (she's Australian married to an Englishman) and
we're having a party tomorrow night, so have a good weekend yourself
and thanks for all the help.

Regards,

Terry.

0
3/17/2006 12:28:40 AM
Good luck with that poker tourney.



Cloudfall wrote:
> 
> Thanks again Dave,
> 
> Everything is crystal clear.
> 
> "Dim SomeArray(1 To 52)" works for me as well.
> 
> My Function is funtioning, it's Friday and my wife and I are playing
> tournament no-limit Texas Hold'em tonight, and I'll be watching the
> football on TV with a bet on my team after that, my wife's sister is
> visiting from England (she's Australian married to an Englishman) and
> we're having a party tomorrow night, so have a good weekend yourself
> and thanks for all the help.
> 
> Regards,
> 
> Terry.

-- 

Dave Peterson
0
petersod (12005)
3/17/2006 12:56:28 AM
Reply:

Similar Artilces:

How to create a CustomerID field for custom entities
Hi, The customerid field is used in opportunity and case and it allows you to select an account or contact, Great. I want to create this field in a form for a new entity i have made, but i cant work out how. Please help. Hi Michael, Customer is special composite type which is not available for custom entities in CRM 3. You will need to add two separete lookup fields for account and contact. -- uMar Khan :: MS CRM MVP CRM Freelance Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.wordpress.com MVP :: https://mvp.support.microsoft.com/default.aspx/profile/umar.khan &...

Backing Up Outlook Express Emails
Does anyone know how to back up email's in Outlook Express? Backup your dbx-files For more assistance ask in an Outlook Express forum. This is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlook of the Office family. Here is the link for the right forum http://communities.microsoft.com/newsgroups/default.asp?icp=InternetExplorer Good Luck! -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Cindy" <anonymous@discussion...

Front End Exchange 2003 OWA for a Back end 2000
Hey everybody, At the minute we're in the process of upgrading our 30 odd servers to Windows Server 2003. The Domain Controllers are still running Windows 2000. My question is : Can I install an Exchange 2003 OWA on a Windows Server 2003 to connect to an Exchange 2000 Server in a Windows 2000 Domain (Native Mode) Do I have to upgrade the whole domain first ? What about notions of Front End / Back End ? We would like to do this to benefit from the new OWA (As it's sooooo cool :)) Thanks for your replies, Mike You can use an E2K3 FE to connecto to an E2K BE, but you only get OW...

create a mass of mailboxes
hi and thanks in advance for your help, I've an exchange 2003 and I have to create a great number (3000) of account and mailboxes. I want to use a script to create them starting from a xls or csv file. There are same script that create the account in a progressive way but I can't use them. Is there something different to use? lucadb Try this link and refer to the section "Active Directory tools and application programming interfaces" http://www.microsoft.com/technet/prodtechnol/exchange/guides/e2k3InterOpMig/39d728f1-62e1-44aa-93c8-930fed46f4e1.mspx "lucadb"...

Combining cells to create a formula
Hi, I have two cells that I want to combine to have a working formula B1 = sum B2 = d3 b3 = =b1&"("&b2&")" D3 = 10 The result in b3 is : sum(d3) How do I get this to result in the actual value in D3. I know I can simply write =D3, but the actual reason for combining the two cells is more complicated than explained here. Thanks Hi I think you are perhaps looking for the Indirect function =INDIRECT("D"&ROW(3:3)) would return the value contained in D3 As you copy down, the formula would alter to 4, 5 etc. represent D4, D5 etc. -- Regards R...

How do I send a welcome message to a new created mailbox automatically?
My exchnage send a message automatically but I need to change the message where do I change it? Thanks, Ron On Tue, 21 Jun 2005 16:35:53 -0700, "Ron" <rony@geotestinc.com> wrote: >My exchnage send a message automatically but I need to change the message >where do I change it? > >Thanks, >Ron > Exchange doesnt do that unless you have something scripted. If you are referring to the welcome message in Outlook, its called welcome.msg , but I dont think its been used since Outlook 2002. So where would I plug in the script? Also I searched for welcome.ms...

Creating a template using Publisher
I need help creating a neighborhood directory template using Publisher and merging an Excel spreadsheet into it. It keeps printing multiple pages of the first page after I merge. What version Publisher? If you have 2003 use the catalog merge for your directory. http://office.microsoft.com/en-us/assistance/CH010504381033.aspx Otherwise you need to setup your page as though it is labels. Determine the size of the area you need for each entry, select labels in page setup, type the size you determined in the page setup. Adjust the gaps and margins when you are ready to print (2000 and bel...

Creating a "Do_Not_Reply" e-mail address
I need to create a do-not-reply@domain.com address that any user that I give permission to on the domain can send as by putting the above address in the "From:" field of their Outlook client and the external recipient will receive this and only see that it came from the do-not-reply address and not the send on behalf of user. Also what would be the best way to block or not receive the e-mails even if an external user does reply to it? On Fri, 13 Apr 2007 20:56:22 -0700, "D-a-n_L" <dlajoie@hotmail.com> wrote: >I need to create a do-not-reply@domain.com addr...

Creating a Microsoft Words document from an existing Excel spreads
Hi all, I need your kind guidance on how to create a new microsoft words document using an existing excel spreadsheet? At present, I have an excel spreadsheet, say "data.xls" containing data for example 6 fields such as COMPANY NAME, DATE, ITEM1, ITEM2, ITEM,3 & QUOTATION TOTAL. Under these 6 fields are some records of my customers. COMPANY NM DATE ITEM1 ITEM2 ITEM3 QUOTATION TTL ---------------- ------- -------- ------- -------- ------------------- ABC Limited 3 Apr xxxxx xxxxx ...

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Missing destructor calls on exit
Sorry if this is well covered ground, but many of the tactics I tried to employ from other threads didn't pan out (and most importantly allow me to understand _why_ this is happening). I have a doc/view mdi app. My runtime library is multi-threaded debug DLL. On exit of the application (via file..exit or the X button), I get a bunch of memory leaks. Some from a modeless dialog (whose destructor is not called), the CMainFrame itself (all 2K), the CDocManager, CMultiDocTemplate, and a CCmdTarget (these are created by the CWinApp I believe). A breakpoint gets hit in my CMainFrame (which ...

Can I create sub-functions in a cross-functional flowchart?
Trying to create a template for my organization to map processes to. Goal is to map the organization to the 'function bars' in Visio's cross-function flowcharts. However, I would like to create some 'sub-functions' for lower organizational elements (i.e. divisions under a department). Is this possible?? ...

SUMIF function not calculating from closed worksheet
We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade each department would access a spreadsheet that would load data from a common lookup spreadsheet The loading process was performed as follows: The lookup file and each department file would be opened to update the link for that month and then saved and closed. When the department head would open the department file, a question would appear asking if they wanted to update the link, if they answered no the spreadsheet would open and display the calculated data. If they answered yes, the file would o...

Passing a variable as a parameter value instead of a literal string to a child report?
In the Navigation section (Jump to URL), I have the following: ="javascript:void(window.open('http://localhost/reportserver?/Reports +Folder/My +Report&rs:Command=Render&StartDate=01/01/2009&FinishDate=12/31/2010'))" This works as designed - a new window pops up with the "My Report" report and the StartDate and FinishDate of 01/01/2009 and 12/31/2010 are passed respectively. But what is the syntax for changing the "01/01/2009" and "12/31/2010" to variables that point to the StartDate and FinishDate parameters of the PAR...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

More than 7 If Functions Example is IF(A2="Bongaigaon", B2*40, ..
i want to use if Command in which 19 times If Function to be used for Example of the Function is =IF(A2="Bongaigaon", B2*40, IF(A2="Dhemaji", B2*100, IF(A2="Dhubri", B2*50, IF(A2="Dibrugarh", B2*65, IF(A2="Golaghat", B2*50, IF(A2="Guwahati", B2*20, IF(A2="hojai", B2*50, IF(A2="Jorhat", B2*60, If(A2="Kharupetia", B2*50, up to 19 times Hi - You really need to create a table with the 19 names in one column and, in the column to the right, the vaues associated with each name. That would ...

Creating a worksheet in Spanish
Hello, Is there a way to create a worksheet on MS XP Pro running Office 2003 Pro all in Spanish? Thank You Kent Hansen Hi Kent All valid formulas -except the ones from the Analysis Toolpack- will automatically translate to the language of the Excel it's opened in. So any spanish speaking Excel will translate formulas to spanish, no matter which version it was written in. The rest, text constants and such, has to be typed or pasted. Excel will not translate this herself. HTH. Best wishes Harald "Kent" <Kent@discussions.microsoft.com> skrev i melding news:ACCCBA3A-53B4...

5 steps to create a publication,the workplan
I need to know at least 5 steps to creating a workplan in pulisher. homework? 1- turn on computer 2- open Publisher 3- File | New 4- File | Page Setup 5- Design your thing 6- File | Save your thing oops thats 6 "thinking" <thinking@discussions.microsoft.com> wrote in message news:1B4E2A10-BCB0-473D-B52A-EFCBB658D856@microsoft.com... |I need to know at least 5 steps to creating a workplan in pulisher. Thinking? This newsgroup is dedicated to solving problems with MS Office Publisher. Not doing your class assignment. "thinking" <thinking@discussions.micros...

ComboBox passing values from selected
In my form (Items_frm) I have a combo box (Itemcbx) that displays 4 columns (Item, Makebuy, Revision, IsActive) but is bound to the first column. However, I would like to pass the other values from the remaining columns to other fields in my form after the selection. Is this possible? Can someone provide a sample code? You can use the Column() attribute. Me.Itemcbx.Column(1) etc. Note that the index for combo box columns is zero-based so 0 is the first, 1 is the second and so on... Steve "Angel G" wrote: > In my form (Items_frm) I have a combo box (Itemcbx) that displ...

Money 30 Day Money Back Guarantee; instructions.
I tried out Money 2004 Small Business with an idea I might switch from quicken 2003 Home and Business. For a number of reasons that involve primairly invoices and Stock transaction updates, I'm sticking with Quicken. I purchased Money at Staples. At that time I asked the clerk about returning the product and he said I 'd have to deal directly with Microsoft. On the Money box there is a Money-Back 30 Day Guarantee Seal and that's all. No specific instructions as to what to do, where to send etc. If someone has this information please share it or directl me to where I might it...

Incomplete Escaping Functionality??
Hello All... I am in an app that needs to write out an XML document for transmittal to an outside organization. All good and fine... I create the XmlDocument object, append all my nodes, and values etc etc... and it all works. Now I go to save the file... I tried two methods... MyXmlDocument.Save(filename) and My.Computer.FileSystem.WriteAllText(filename, MyXmlDoc.OuterXml, False) The problem comes in with XmlDocument.OuterXml. According to XML, there are 5 characters which need to be escaped... Ampersand, LessThan, GreaterThan, Apostrophe and DoubleQuote. XmlDocument.OuterXml, esca...

Creating PDF files in MFC
Hi, I want to create PDF files in MFC. Does anyone know how should I do it? Thanks in advance. Check out the following http://sourceforge.net/projects/pdfcreator/ "Shahoo" wrote: > Hi, > I want to create PDF files in MFC. Does anyone know how should I do > it? > Thanks in advance. > > Here's another to try out: "Shahoo" <shahookamangar@gmail.com> wrote in message news:1175272390.190193.274530@n59g2000hsh.googlegroups.com... > Hi, > I want to create PDF files in MFC. Does anyone know how should I do > it? > Thanks in advanc...

Serializing array of userdefined types
Hello, I want to have a class that contains only a collection of another class. For example: public __gc class Alignment { public: Alignment(); ... }; // Probably using XmlIncludeAttribute such as this is wrong... [System::Xml::Serialization::XmlIncludeAttribute(__typeof(Alignment))] public __gc class Alignments : public System::Collections::ArrayList { public: Alignments(); ... }; I figured I could use Alignments like this: Alignment* alignment = new Alignment; Alignments* alignments = new Alignments; alignments->Add(alignment); BTW, I am using XmlSerializer and XmlTextWriter....

Can I create a CScrollView popup window?
Hi, My application displays video from a firewire camera. The video is displayed in a CScrollView derived window that I create in CMainFrame::OnCreate() like this: m_pImageView = new CImageView; m_pImageView->Create(NULL, NULL, WS_CHILD | WS_VISIBLE, rClient, this, AFX_IDW_PANE_FIRST, NULL)) Now, when the user stops the video, they can click a toolbar button that pops up a CWnd derived window that displays all of the image data on a particular line of video. So, if the camera is spewing out 8-bits per pixel in the 800x600 mode, the popup window would display the 800 bytes associated w...

Back End Insecure (and don't you hate when that happens!)
A2000, used the splitter and security wizard, ended up with _BE not secured. When I try to open App_BE.mdb when joined with app's WIF, it asks for password. This is good. But when I open App_BE.mdb from the System.mdw shipped with Access it *doesn't* ask for password, just opens the database. This is bad. Any idea what is going wrong and how I can fix it? > Any idea what is going wrong and how I can fix it? Well, you never did have to use the security wizard to secure a database. The security wizard does not do anything that you can't do manually (it does not modify ...