1

I'm currently trying to add a collection I made to an array of collections. It keeps throwing errors when I go to add my collection to an array of collections I made. The collection is being added to array in the second for loop near the bottom. Maybe I declared it wrong or its just not possible to create an array of collections. I wasn't given any errors when declaring one so, idk. Any ideas?

Update: The error it is giving me is Object variable or With block variable not set. I am trying to to add a collection I made to an array of collections

Update2: Changed some code based on suggestions. Currently trying to figure out how to access Collection stored in array of Collection

Private Sub CommandButton2_Click()
    Dim currentWorksheet As Worksheet
    WS_Count = ActiveWorkbook.Worksheets.Count

    Dim rows As Integer
    rows = WS_Count - 3
    Dim itemsFoundList() As String
    Dim itemsSold() As Integer
    Dim numItems As String
    Dim counter As Integer
    Dim d As Integer

    Dim masterArray() As Collection
    ReDim masterArray(0 To WS_Count)

    Dim itemList As Collection

    counter = 1
    d = 1

    For i = 3 To WS_Count - 1
        Set currentWorksheet = ActiveWorkbook.Worksheets(i)
        Set itemList = New Collection
        numItems = numberOfItems(currentWorksheet, "Drink", "I2", "I18")
        ' MsgBox " " & numItems
        ReDim itemsFoundList(0 To CInt(numItems))
        ReDim itemsSold(0 To CInt(numItems))

        itemsFoundList = itemsFound(currentWorksheet, "Drink", "I2", "I18", CInt(numItems), "A")
        itemsSold = itemsSoldFound(currentWorksheet, "Drink", "I2", "I18", CInt(numItems), "E")

        itemList.Add itemsFoundList
        itemList.Add itemsSold
        itemList.Add currentWorksheet.Name


            Set masterArray(counter) = itemList

            'How to access Collection stored in Array of Collections?'

        counter = counter + 1
     Next i
End Sub
Rob
  • 133
  • 3
  • 16
  • 4
    "keeps throwing errors" is not a valid way to describe a problem. What is the error message? – GSerg Jun 13 '19 at 15:50
  • what you trying to do? and what you want to get? – Dmitrij Holkin Jun 13 '19 at 16:01
  • 3
    You should [remove the parentheses in all those `Add`s](https://stackoverflow.com/a/4004039/11683), and you should initialize the elements of `masterArray` because it contains `WS_Count + 1` `Nothing`s. – GSerg Jun 13 '19 at 16:09
  • I think the issue is with `masterArray(counter).Add`. The `.Add` is not a method of arrays. To assign into arrays would be `Set masterArray(index) = CollectionObj` – ArcherBird Jun 13 '19 at 16:09
  • @ArcherBird `.Add` is not a method of arrays, but it is a method of collections, and `masterArray(counter)` returns a collection. – GSerg Jun 13 '19 at 16:10
  • I guess I'm just confused why masterArray is an array at all. You never add anything to the array. This is just a collection of collections, no? `masterArray(counter)` accesses `Nothing`, right? And you can't `.Add` to `Nothing` – ArcherBird Jun 13 '19 at 16:13
  • 1
    @ArcherBird Is an array of `Long`s just a collection of `Long`s? It is in a certain sense, not necessarily in all senses that an array imposes though. One does not generally add to an array either, only set its elements. You "add" to an array by resizing it, which is done by `ReDim masterArray(0 To WS_Count)`. After that the array has `WS_Count + 1` elements. All these elements are `Nothing`, but their type is `Collection`. – GSerg Jun 13 '19 at 16:16
  • @ ArcherBird I was able to add my collection to the array by using your method. Would i go about accessing the stored collection by ` array = masterArray(0).Item(1) ` – Rob Jun 14 '19 at 00:34
  • 1
    @Archer re _I think the issue is with masterArray(counter).Add. The .Add is not a method of arrays._ That line is the issue, but not for the reason you suggest. `masterArray(counter)` _is_ a Collection, just one that hasn't been initialised yet. – chris neilsen Jun 14 '19 at 00:39
  • 1
    You have removed the parentheses. Now all you have to do is to add `Set` to `masterArray(counter) = itemList` and remove the entire pointless `'Accessing stored Collection'` block. – GSerg Jun 14 '19 at 09:17
  • @GSerg do you know how to access the collection once its stored in the array of Collections? – Rob Jun 14 '19 at 14:40
  • Like you were doing it. `masterArray(counter)`. – GSerg Jun 14 '19 at 14:54
  • @GSerg like this ` masterArray(counter).Item(1) ` ? – Rob Jun 14 '19 at 15:09
  • Or `masterArray(counter)(1)`. – GSerg Jun 14 '19 at 15:22
  • @GSerg Sweet thanks!! It works – Rob Jun 14 '19 at 15:25

1 Answers1

0

  1. It is impossible to form an array of collections in Excel VBA. If some array will be declared as Variant it can accept other arrays and it is possible to form a jagged array, but if some created collection will be tried to add to this array as an element, the program during runtime (not during compile-time) will throw an error message Run-time error '450' - the image error picture

  2. In your example, you formed a collection of collections. To access a separate element of a collection that is included in a collection of collections you need initially extract the separate collection from a collection of collections and then you'll be able to access a separate element of this extracted collection.

Sharunas Bielskis
  • 1,033
  • 1
  • 16
  • 25