Append to public collection

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Append to public collection

Post by YasserKhalil »

Hello everyone
In the following sample code, I am trying to demonstrate an issue .. which is simply to append to public collection
At the start of the code cols is declared as public so as to store all the items that will be generated through the PublicSub function

Code: Select all

Dim cols As Collection

Sub Test()
    Dim e
    For Each e In Array("A", "B")
        Set cols = PublicSub(CStr(e))
    Next e
End Sub

Function PublicSub(s As String) As Collection
    Dim c As New Collection, i As Long
    If s = "A" Then i = 1 Else i = 6
    For i = i To i + 4
        c.Add i
    Next i
    Set PublicSub = c
End Function
The expected is to have a collection with 10 items but when trying this I got only the second iteration only

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append to public collection

Post by HansV »

In the loop

Code: Select all

    For Each e In Array("A", "B")
        Set cols = PublicSub(CStr(e))
    Next e
the collection cols is set to PublicSub("A") in the first iteration, and then it is set to PublicSub("B") in the second iteration, overwriting the result of PublicSub("A").

Here is a way to do it:

Code: Select all

Dim cols As Collection

Sub Test()
    Dim e
    Set cols = New Collection
    For Each e In Array("A", "B")
        Call PublicSub(cols, CStr(e))
        Debug.Print cols.Count
    Next e
End Sub

Sub PublicSub(c As Collection, s As String)
    Dim i As Long
    If s = "A" Then i = 1 Else i = 6
    For i = i To i + 4
        c.Add i
    Next i
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Append to public collection

Post by YasserKhalil »

Amazing. Thanks a lot my tutor
How can I assign the keys of the collection to an array. I tried a=Join(cols.Keys) but didn't work.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append to public collection

Post by HansV »

I'd use a Scripting.Dictionary object instead of a Collection object. The Dictionary object has an array Items and an array Keys.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Append to public collection

Post by YasserKhalil »

Thank you very much my tutor.
Best and Kind Regards

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Append to public collection

Post by LisaGreen »

Just an aside.... Dim in the declarations section is only public to that module.

To make a variable truly public across modules use the Public keyword.

HTH
Lisa