Add Object to beginning of collection VBA

Question

This may have been asked before but I can't seem to find it...

I have a class I created and a collection for each object of that class, when I added these objects to my collection I did not give each item a key and rather let VBA do that automatically.

Code below doesn't seem to work for me...

ediData.StyleCollection.Add StoreStyleData(Range("A" & i & ":M" & i)), Before:=0

Tried this as well and not working either!

ediData.StyleCollection.Add StoreStyleData(Range("A" & i & ":M" & i)), Before:=1

QUESTION:

How can I add an item to the beginning of a collection rather than the end? If I can't do that, how can I loop through a collection from the end instead of the start of it?

EDIT:

Below I've included the Function for StoreStyleData

Private Function StoreStyleData(rng As Range) As cPOStyle

   Set StoreStyleData = New cPOStyle
   With rng
      StoreStyleData.XRef = .Cells(1).value
      StoreStyleData.Season = .Cells(2).value
      StoreStyleData.Style = .Cells(3).value
      StoreStyleData.Color = .Cells(4).value
      StoreStyleData.Size = .Cells(5).value
      StoreStyleData.RetailPrice = .Cells(6).value
      StoreStyleData.Category = .Cells(8).value
      StoreStyleData.PO850Price = .Cells(9).value
      StoreStyleData.XRefPrice = .Cells(10).value
      StoreStyleData.Units = .Cells(11).value
      StoreStyleData.SubTotal = .Cells(12).value
      StoreStyleData.Description = .Cells(13).value
   End With

End Function

COLLECTION WITHIN THE CLASS

''''''''''''''''''''''
' StyleCollection Property
''''''''''''''''''''''
Public Property Get StyleCollection() As Collection
   If pStyleCollection Is Nothing Then Set pStyleCollection = New Collection
   Set StyleCollection = pStyleCollection
End Property
Public Property Let StyleCollection(value As Collection)
   Set pStyleCollection = value
End Property

I suppose one solution is to add each item with the unique identifying myself, but I'm trying to avoid that as all my code is working great however my collection is in reverse — Maldred, Dec 29 '17 at 18:52

chillin · Accepted Answer · 2017-12-29T19:53:37.833

1

Maybe try:

StyleCollection.Add Item:=StoreStyleData(Range("A" & i & ":M" & i)), Before:=1

Does the below allow you to loop through collection backwards:

For i = StyleCollection.Count to 1 Step -1

'Debug.print StyleCollection.Items(i) or StyleCollection(i)

Next i

Untested, written on mobile.

Edit 1:

If StyleCollection.Count > 0 then

StyleCollection.Add Item:=StoreStyleData(Range("A" & i & ":M" & i)), Before:=1

Else

StyleCollection.Add StoreStyleData(Range("A" & i & ":M" & i))

End if

edited Dec 29 '17 at 19:53

answered Dec 29 '17 at 19:30

chillin

4,391
1
8
8

`StyleCollection.Add Item:=StoreStyleData(Range("A" & i & ":M" & i)), Before:=1` Gave me "*Invalid procedure call or argument*" – Maldred Dec 29 '17 at 19:38
1

Consider the edit. You could also consider assigning whatever StoreStyleData returns to a variable, validating it (so you know it is what you think it is), then adding the variable to the collection; just to see if that works. – chillin Dec 29 '17 at 19:56

JohnyL · Answer 2 · 2017-12-29T19:25:49.667

0

If you want to get last item first, use backward loop:

Dim x As Integer
Dim cls As New MyClass
Dim itm As Item
For x = MyClass.Items.Count To 1 Step -1
    Set itm = MyClass.Items(x)
Next

EXAMPLE

WorkersCollection class:

Private col As New Collection

Sub AddWorker(w As Worker)
    col.Add w
End Sub

Property Get Workers() As Collection
    Set Workers = col
End Property

Worker class:

Private m_Name As String
Property Get Name() As String
    Name = m_Name
End Property
Property Let Name(v As String)
    m_Name = v
End Property

Test method:

Sub Test()

    Dim w As Worker
    Dim x As Integer
    Dim wcol As New WorkersCollection

    For x = 1 To 5
        Set w = New Worker
        w.Name = "Name" & x
        wcol.AddWorker w
    Next

    For x = wcol.Workers.Count To 1 Step -1
        Debug.Print wcol.Workers(x).Name
    Next

    'Output:
    'Name5
    'Name4
    'Name3
    'Name2
    'Name1

End Sub

edited Dec 29 '17 at 19:25

answered Dec 29 '17 at 18:56

JohnyL

6,894
3
22
41

How does this work with a `Collection`? I'm not sure I understand, my class has a collection object `StyleCollection` – Maldred Dec 29 '17 at 19:10
@Maldred Added explanation – JohnyL Dec 29 '17 at 19:26
Ah, I see... However I don't have a method to add to the class in the `Collection` as I am doing it in my `Modules`... I've edited my question above – Maldred Dec 29 '17 at 19:46
@Maldred So, what's the difference with my example? – JohnyL Dec 29 '17 at 19:54
I guess what's making me a little confused is that I have two collections working together here and ideally I'd like to add an item to the collection at the beginning and not at the end instead of just printing it out backwards. I have a collection of Class `ediData` and within that Class I have another collection `StyleCollection` of another Class – Maldred Dec 29 '17 at 19:56
@Maldred This doesn't matter. As I wrote, there's no need to reinvent a wheel - just loop what you need backwards. I have nothing more to add :) – JohnyL Dec 29 '17 at 20:00

Add Object to beginning of collection VBA

2 Answers2