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

RE: Return and array from VBA to Excel

[Subject Prev][Subject Next][Thread Prev][Thread Next]
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
> 
> 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 
******* ****** ****** ****** ******* ****** ****** ******** 

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