Return to index: [Subject] [Thread] [Date] [Author]
RE: Return and array from VBA to Excel
[Subject Prev][Subject Next][Thread Prev][Thread Next]- To: seaint(--nospam--at)seaint.org
- Subject: RE: Return and array from VBA to Excel
- From: Padmanabhan Rajendran <rakamaka(--nospam--at)yahoo.com>
- Date: Thu, 26 May 2005 10:10:37 -0700 (PDT)
- Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys
I don't understand: If array_data is declared as a 2-D array, what output will be generated by the ending statement: array_test4 = array_data? I copied and pasted the code in a module. When I run it, the cell value is 0 regardless of what was chosen for n_rows and n_cols. Rajendran --- Brian McDonald <mcdonald(--nospam--at)exponent.com> wrote: > Bill - you beat me to the punch - I was just about > to post this, which works > just fine for different size arrays: > > Function array_test4(n_rows, n_cols) > ReDim array_data(n_rows, n_cols) > > For i = 1 To n_rows > For j = 1 To n_cols > array_data(i, j) = i + j / 100 > Next j > Next i > > array_test4 = array_data > > End Function > > > _____ > > From: Bill Allen, S.E. [mailto:T.W.Allen(--nospam--at)cox.net] > Sent: Thursday, May 26, 2005 8:15 AM > To: seaint(--nospam--at)seaint.org > Subject: RE: Return and array from VBA to Excel > > > > Brian - > > > > You can solve this with a Redim statement: > > > > Redim array_data (n_rows, n_cols) > > > > It's not good to lose sleep over stuff like this :o) > > > > > T. William (Bill) Allen, S.E. (CA #2607) > > > ALLEN DESIGNS > > > Consulting Structural Engineers > > > <http://www.allendesigns.com/> > http://www.AllenDesigns.com > > > V (949) 248-8588 > > * > > F (949) 209-2509 > > > > -----Original Message----- > From: Brian McDonald [mailto:mcdonald(--nospam--at)exponent.com] > Sent: Thursday, May 26, 2005 8:08 AM > To: seaint(--nospam--at)seaint.org > Subject: RE: Return and array from VBA to Excel > > > > I think the problem is with dynamic allocation of > the array, rather than > returning an array from a function. For instance, > this crippled version of > your program seems to work: > > > Function array_test3() > n_rows = 2 > n_cols = 2 > > > > Dim array_data(2, 2) As Double > > > > For i = 1 To n_rows > For j = 1 To n_cols > array_data(i, j) = i + j / 100 > Next j > Next i > > > > array_test3 = array_data > > > > End Function > > > > If you select a 2x2 block of cells and set it equal > to =array_test3(), it > successfully returns the array (remember to use > ctrl-shift-rtn). Note that > I also use Option Base 1 to skip the 0 subscript. > > > > Thanks a lot Jake - now I won't be able to sleep > until I get this to work:) > > > > Brian McDonald > > Exponent > > _____ > > From: Jake Watson [mailto:jwatson(--nospam--at)utahisp.com] > Sent: Thursday, May 26, 2005 5:46 AM > To: seaint(--nospam--at)seaint.org > Subject: Return and array from VBA to Excel > > I am trying to get VBA for Excel 2003 to return an > array from a custom > function to a worksheet. Following is some useless > sample code. Does > anyone know how to get the array out of VBA and into > a worksheet? > > > > VBA Code: > > > > Function array_test(n_rows, n_cols) > > Dim array_data As Variant > > For i = 1 To n_rows > > For j = 1 To n_cols > > array_data(i, j) = i + j / 100 > > Next j > > Next i > > array_test = array_data > > End Function > > > > > > Then I enter enter {=array_test(5,5)} in cell A1 and > get "#VALUE!". Any > suggestions would be appreciated. > > > > Jake Watson, P.E. > > Salt Lake City, UT > > > > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ******* ****** ******* ******** ******* ******* ******* *** * Read list FAQ at: http://www.seaint.org/list_FAQ.asp * * This email was sent to you via Structural Engineers * Association of Southern California (SEAOSC) server. To * subscribe (no fee) or UnSubscribe, please go to: * * http://www.seaint.org/sealist1.asp * * Questions to seaint-ad(--nospam--at)seaint.org. Remember, any email you * send to the list is public domain and may be re-posted * without your permission. Make sure you visit our web * site at: http://www.seaint.org ******* ****** ****** ****** ******* ****** ****** ********
- Prev by Subject: RE: Return and array from VBA to Excel
- Next by Subject: RE: Return and array from VBA to Excel
- Previous by thread: RE: Return and array from VBA to Excel
- Next by thread: RE: Return and array from VBA to Excel
- About this archive
- Messages sorted by: [Subject][Thread][Author][Date]