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

RE: Return and array from VBA to Excel

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

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