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

RE: Return and array from VBA to Excel

[Subject Prev][Subject Next][Thread Prev][Thread Next]
Hello:

I have to ask, what are you folks finding to do with matrices?
The last time I thought about them was in school.  

Mark

"Bill Allen, S.E." <T.W.Allen(--nospam--at)cox.net> wrote:

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

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