Return to index: [Subject] [Thread] [Date] [Author]

RE: Return and array from VBA to Excel

[Subject Prev][Subject Next][Thread Prev][Thread Next]
Got it. Thanks.

Rajendran
--- Brian McDonald <mcdonald(--nospam--at)exponent.com> wrote:

> It works fine for me.
> 
> 1. Select an n-rows by n_cols block of cells (say 5
> rows by 3 columns)
> 2. Enter "=array_test4(n_rows,n_cols)" (say
> "=array_test4(5,3)")
> 3. Press control+shift+enter keys simultaneously
> 4. The selected block should now be populated by
> Jake's formula
> 
> I believe that array_test4() returns a pointer to
> the data as opposed to the
> data itself, and therefore it does not matter if you
> are pointing to a 2d
> array, scalar, string, whatever, as long as you
> treat it consistently in
> your spreadsheet.
> 
> Brian
> 
> -----Original Message-----
> From: Padmanabhan Rajendran
> [mailto:rakamaka(--nospam--at)yahoo.com] 
> Sent: Thursday, May 26, 2005 10:11 AM
> To: seaint(--nospam--at)seaint.org
> Subject: RE: Return and array from VBA to Excel
> 
> 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
> 
=== message truncated ===



		
__________________________________ 
Yahoo! Mail 
Stay connected, organized, and protected. Take the tour: 
http://tour.mail.yahoo.com/mailtour.html 


******* ****** ******* ******** ******* ******* ******* ***
*   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 
******* ****** ****** ****** ******* ****** ****** ********