Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    return Array (Excel 2002)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: return Array (Excel 2002)

    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

  3. #3
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: return Array (Excel 2002)

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

    <pre> 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)</pre>


    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)

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: return Array (Excel 2002)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: return Array (Excel 2002)

    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.

  6. #6
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: return Array (Excel 2002)

    Thanks Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •