return Array (Excel 2002)

Topic

New Reply

Hi all,

My question is about Arrays, i have an multi array of 7 X 5, is it possible to call the array and return one column of the array? I can see the Array in the Local window, but what ever I try to make it return comes back empty. Any threads that might give me a solid direction?

Thanks,
Darryl.

Reply | Quote

Replies

You can do something like this

‘ A two-dimensional array
Dim arr(1 To 2, 1 To 3) As Variant
‘ A one-dimensional array
Dim col(1 To 2) As Variant
‘ Loop counter
Dim i As Integer

‘ Populate the items of the two-dimensional array
arr(1, 1) = “a”
arr(1, 2) = “b”
arr(1, 3) = “c”
arr(2, 1) = 9
arr(2, 2) = 8
arr(2, 3) = 7

‘ Fill one-dimensional array with 2nd column of the two-dimensional array
For i = 1 To 2
col(i) = arr(i, 2)
Next i

Reply | Quote

Thank you all for your help,

I guess my question should be, is it possible to return from a function call an array column so that the that the procedure that creates the array also returns it to the calling procedure, basically can you send one array over the moduler level, so that life of the array still holds value in another procedure?

Thanks,
Darryl.

Reply | Quote

There are different ways you can pass arrays to other procedures functions:

1) A function can indeed return an array:

Function ArrayFunction()
Dim arr(1 To 5)
‘ Some code to populate the array
arr(1) = “john”
arr(2) = 97
…
ArrayFunction = arr
End Function

2) Declare an array as a private or public variable at the top of a module. Private = known anywhere in the same module, public = known in all modules in the same workbook. For example:

Public arr(1 To 5)

You can now refer to arr anywhere in the code in the same workbook.

Reply | Quote

Thanks Hans

Reply | Quote

You can use the WorksheetFunction Index to return a specific row or column of a 2-Dimensional array.

    Rem returns third column as a 2-D (n rows X 1 column) array
myColumnA = Application.Index(myArray, 0, 3)

    Rem returns third column as a 1-D array
myColumnB = Application.Transpose(Application.Index(myArray, 0, 3))

    Rem returns the second row as a 1-D array
myRow = Application.Index(myArray, 2, 0)

This fails with larger arrays.
On my Mac it fails if myArray has more than 4095 (=2^12 – 1) elements. It returns a TypeMismatch error.
The Windows versions have a much larger limit on WorksheetFunction.Index.

Your 5X7 is well within either limit.

(Mac OS10.5 Excel2004)

Reply | Quote