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

RE: Return and array from VBA to Excel

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