Results 1 to 9 of 9
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Multiple Arrays (Excel 2003)

    I'm teasing around a problem
    I want to create an array, or rather a series of arrays. They will have variable numbers of rows and columns, so I can't place all the information in the same array. I do not know how many arrays will be required. Is there a method to define arrays on the fly?

    I was thinking of adding an incrementing number to the end of the array name
    Ie
    redim myArrayx
    but I'm having some trouble framing the proper syntax.

    any suggestions?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Multiple Arrays (Excel 2003)

    Depending on your requirements you could define a dynamic array of type Variant, and set its elements to arrays:

    Dim ArrayOfArrays()
    Dim Array1()
    Dim Array2()

    ReDim Array1(1 To 4)
    Array1(1) = 13
    Array1(2) = "Catharine"
    Array1(3) = #1/9/2007#
    Array1(4) = True

    ReDim Array2(1 To 2, 1 To 2)
    Array2(1, 1) = "east"
    Array2(1, 2) = "west"
    Array2(2, 1) = "home"
    Array2(2, 2) = "best"

    ReDim ArrayOfArrays(1 To 2)
    ArrayOfArrays(1) = Array1
    ArrayOfArrays(2) = Array2
    Debug.Print ArrayOfArrays(2)(2, 1)

    Or you could define a Collection whose elements are arrays. Collections are less structured than arrays, but you can easily add and remove arbitrary elements.

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Multiple Arrays (Excel 2003)

    thanks Hans - I will mull over your answer now that things have settled down.

    Just after I hit the post button, the building fire alarm went off! (Hey it wasn't that urgent!)
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Multiple Arrays (Excel 2003)

    must collections be stored in a class module?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Multiple Arrays (Excel 2003)

    No, you can create them both in standard modules and in class modules, at the module level or within a function/procedure.

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Multiple Arrays (Excel 2003)

    any online references you can point out (collection is such a generic word when googling)
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Multiple Arrays (Excel 2003)

    The VBA help has a description and an example of the Collection object.

    Googling for collection vba or for "collection object" should narrow down the results a bit.

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Multiple Arrays (Excel 2003)

    You are correct of course, my head must have still been freezing outside on the fire escape!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Multiple Arrays (Excel 2003)

    I decided to go the collection route.
    The following is a pared down version of one of the subs that creates and loads arrays into the collection.
    The collection is defined globally so the data stored within it can be transferred from sub to sub.

    Everything is working fine - I just thought loungers might be interested in an example of a collection in use.

    Option Explicit
    Dim ReferenceArray As New Collection

    Private Sub btn_RefData2_Click()
    Dim lngRowCount As Long
    Dim lngColCount As Long
    Dim varANTranspose As Variant 'transposed orientation for listbox
    Dim lngCounter As Long
    Dim ArrayInstance As Variant
    Dim ArrayName As String 'individual array name
    Dim lngArrayCount As Long
    Dim varArrayNames() As Variant 'source for Reference ListBox

    '1. Load data into an array (ArrayInstance)
    '2. Increment count of number of arrays
    '3. Add name of array to a different array (varArrayNames) that is enlarged with
    ' each array. This array is 2 rows by an expanding number of columns in size.
    ' The data in this array will have to be transposed before it can be displayed
    ' in the listbox on the form using the varANTranspose array
    '4. Add the array contained within ArrayInstance to the collection. Use the
    ' count of the number of arrays as the unique key identifying the array.
    '

    'count range dimensions
    lngRowCount = Range(frmMatchScoreSetup.RefEdit1.Value).Rows.Coun t
    lngColCount = Range(frmMatchScoreSetup.RefEdit1.Value).Columns.C ount
    'variable array name

    'Redim create sequencearray to size of selection
    ReDim ArrayInstance(lngRowCount, lngColCount)
    'load array
    ArrayInstance = Range(frmMatchScoreSetup.RefEdit1.Value).Value

    'increment array count
    wkbkResults.Activate
    Application.Goto reference:="ArrayCountR"

    lngArrayCount = ActiveCell.Value + 1
    ActiveCell.Value = lngArrayCount
    ' array name
    'start here
    ReDim Preserve varArrayNames(1 To 2, 1 To lngArrayCount) 'rows and columns need reorientation
    varArrayNames(1, lngArrayCount) = frmMatchScoreSetup.txt_ReferenceLabel.Value
    varArrayNames(2, lngArrayCount) = frmMatchScoreSetup.RefEdit1.Value


    'add array to collection with unique name
    ReferenceArray.Add Item:=ArrayInstance, key:=CStr(lngArrayCount)

    'Show details in Reference Listbox
    ReDim varANTranspose(lngArrayCount, 2)
    lngCounter = 1
    For lngCounter = 1 To lngArrayCount
    varANTranspose(lngCounter, 1) = varArrayNames(1, lngCounter)
    varANTranspose(lngCounter, 2) = varArrayNames(2, lngCounter)
    Next lngCounter

    frmMatchScoreSetup.ReferenceList.List = varANTranspose

    ''
    'the following snippet shows how to pull arrays out of the collection
    'in order to work with the contents.

    'count the number of arrays in each collection
    intRefCount = ReferenceArray.Count
    For intArrayCount = 1 To intRefCount
    'Determine dimensions of each array
    lngRowCount = UBound(ReferenceArray.Item(intArrayCount), 1)
    lngColCount = UBound(ReferenceArray.Item(intArrayCount), 2)

    ReDim varMinMaxArray(lngRowCount, lngColCount)
    varMinMaxArray = ReferenceArray.Item(intArrayCount)
    ReDim ArrayInstance(lngRowCount, lngColCount)

    ' do stuff with the contents of the varMinMaxArray

    Next intArrayCount 'end of Reference column manipulations
    End Sub
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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