Making a backup or copy of a Collection

[DonEB]

I'm trying to create a backup of a Collection so I can return it to its original state after I perform some tasks on it.

Below I've include a sample of my code which (1) creates a collection called Primary, and a second collection called Backup. From a worksheet Sheets("Sheet2").Range("E28:X28"), I take the number from each cell and populate the Primary collection (along with its KEY). Then I attempt to create a backup of the Primary collection. So far, this seems to work perfectly... until I attempt to remove values from the Primary collection and that causes an unexpected result to the Backup collection.

The unexpected result is that whatever I remove from the Primary also is getting removed from the Backup. So it appears that these two collections are somehow linked. However, I need the Backup to be just that... a backup of the ORIGINAL Primary collection so I can go back to the ORIGINAL data to perform an ongoing examination of different combinations. But I need to be able to get back to my original Primary collection in order to accomplish this.

Might anyone have a possible solution to my problem?

Sub TestCollections()
       ' Create collection
       Dim StrVal As String
       Dim collPrimary As New Collection, collBackup As Collection
       Dim c As Range
         
       '# Read 20 values to collection
       '# NOTE:  The following range would simply contain the values of 1, 2, 3.... thru 20; with no duplicates).  Ultimately, these can be any number, however, still not duplicates.  
       '#############################################################
        For Each c In Sheets("Sheet2").Range("E28:X28")
            '# Conversion to String takes place in order to add a "KEY"
            '###########################################################
            StrVal = CStr(c.Value)
    
            collPrimary.Add item:=c.Value, Key:=StrVal                         'This collection will contain ALL possible player numbers to be used
        Next
        
        '# Backup of collection created 
        '##########################################################################################################
        Set collBackup = collPrimary
        
        '# Using the KEY allows me to be specific about which numbers I would like to remove regardless of where they are within the collection
        '##########################################################################################################
        collPrimary.Remove "1"
        collPrimary.Remove "5"
        collPrimary.Remove "10"
        collPrimary.Remove "17"
            
End Sub

 

[Domenic]

Set collBackup = collPrimary

collPrimary holds a pointer to the collection. And so the above line of code actually assigns the pointer to collBackup. As a result, both variables point to the same collection Therefore, since you want a copy of the collection, and since you cannot retrieve the key from a collection, I suggest you use a Dictionary object instead.

First, set a reference to the Microsoft Scripting Runtime library...

Visual Basic Editor >> Tools >> References >> select/check Microsoft Scripting Runtime

Then you can do something like this...

    Dim dicPrimary As Scripting.Dictionary
    Set dicPrimary = New Scripting.Dictionary

    dicPrimary.Add key:="Key1", Item:="Value1"
    dicPrimary.Add key:="Key2", Item:="Value2"
  
    Dim dicBackup As Scripting.Dictionary
    Set dicBackup = New Scripting.Dictionary

    Dim currentKey As Variant
    For Each currentKey In dicPrimary.Keys
        dicBackup.Add key:=currentKey, Item:=dicPrimary(currentKey)
    Next

Hope this helps!

 

[DonEB]

collPrimary holds a pointer to the collection. And so the above line of code actually assigns the pointer to collBackup. As a result, both variables point to the same collection Therefore, since you want a copy of the collection, and since you cannot retrieve the key from a collection, I suggest you use a Dictionary object instead.

First, set a reference to the Microsoft Scripting Runtime library...

Visual Basic Editor >> Tools >> References >> select/check Microsoft Scripting Runtime

Then you can do something like this...

    Dim dicPrimary As Scripting.Dictionary
    Set dicPrimary = New Scripting.Dictionary

    dicPrimary.Add key:="Key1", Item:="Value1"
    dicPrimary.Add key:="Key2", Item:="Value2"
 
    Dim dicBackup As Scripting.Dictionary
    Set dicBackup = New Scripting.Dictionary

    Dim currentKey As Variant
    For Each currentKey In dicPrimary.Keys
        dicBackup.Add key:=currentKey, Item:=dicPrimary(currentKey)
    Next

Hope this helps!

Thank you Domenic... I appreciate your quick response and I think it may help. But I'm not familiar with utilizing Dictionary object, so I have a couple of follow up questions.

First, in my initial inquiry, I included VBA code (collPrimary.Remove "1") that showed what I would do to "remove" a value from a collection. Can you share with me the code I would need to use in order to remove a value from the Dictionary object?

Second, and not reflected in my initial inquiry, I have code (reflected below) that would allow me to take the remaining values within a collection and insert them back into a worksheet. I know that when removing an item/value from a collection it dynamically shifts all the data to remove any voids. Does working with the Dictionary object work similarly and/or what code might be used to replace this code.

                For n = 1 To collPrimary.Count
                       Worksheets("Sheet2").Cells(25, 4 + n).Value = collPrimary(n)
                Next n

 

[Domenic]

Can you share with me the code I would need to use in order to remove a value from the Dictionary object?

You can use the Remove method of the Dictionary object to remove the key/item pair by specifying the key...

dicPrimary .Remove Key:="Key1"

See the following article...

Remove method (Dictionary object)

Office VBA reference topic

learn.microsoft.com

Second, and not reflected in my initial inquiry, I have code (reflected below) that would allow me to take the remaining values within a collection and insert them back into a worksheet. I know that when removing an item/value from a collection it dynamically shifts all the data to remove any voids. Does working with the Dictionary object work similarly and/or what code might be used to replace this code.

                For n = 1 To collPrimary.Count
                       Worksheets("Sheet2").Cells(25, 4 + n).Value = collPrimary(n)
                Next n

With the Dictionary object, you can certainly retrieve an item by index or key...

dicPrimary.Items(0)  'specify item by index number with indexing starting at 0

dicPrimary.Item("Key1") 'specify item by key

So your loop could be replaced with the following...

    For n = 0 To d.Count - 1
           Worksheets("Sheet2").Cells(25, 5 + n).Value = dicPrimary.items(n)
    Next n

However, with the Dictionary object, we have a more efficient alternative available. We can use the Items method to return an array of items, and transfer them to a worksheet all at once. For example, we can do the following...

Worksheets("Sheet2").Cells(25, 5).Resize(, dicPrimary.Count).Value = dicPrimary.items

Hope this helps!

 

[DonEB]

collPrimary holds a pointer to the collection. And so the above line of code actually assigns the pointer to collBackup. As a result, both variables point to the same collection Therefore, since you want a copy of the collection, and since you cannot retrieve the key from a collection, I suggest you use a Dictionary object instead.

First, set a reference to the Microsoft Scripting Runtime library...

Visual Basic Editor >> Tools >> References >> select/check Microsoft Scripting Runtime

Then you can do something like this...

    Dim dicPrimary As Scripting.Dictionary
    Set dicPrimary = New Scripting.Dictionary

    dicPrimary.Add key:="Key1", Item:="Value1"
    dicPrimary.Add key:="Key2", Item:="Value2"
 
    Dim dicBackup As Scripting.Dictionary
    Set dicBackup = New Scripting.Dictionary

    Dim currentKey As Variant
    For Each currentKey In dicPrimary.Keys
        dicBackup.Add key:=currentKey, Item:=dicPrimary(currentKey)
    Next

Hope this helps!

Thank you Domenic. Sorry for the delay in providing a "green checkmark" but it took me a few attempts to try and integrate the recommended coding you provided in both of your responses. I believe I have successfully adapted the code and all seems to be working as I would have hoped. Once again, thank you for your time and expertise!!

 

[Domenic]

That's great, I'm glad I could help, and thanks for your feedback!

Cheers!

 

[DonEB]

You can use the Remove method of the Dictionary object to remove the key/item pair by specifying the key...

dicPrimary .Remove Key:="Key1"

See the following article...

Remove method (Dictionary object)

Office VBA reference topic

learn.microsoft.com

With the Dictionary object, you can certainly retrieve an item by index or key...

dicPrimary.Items(0)  'specify item by index number with indexing starting at 0

dicPrimary.Item("Key1") 'specify item by key

So your loop could be replaced with the following...

    For n = 0 To d.Count - 1
           Worksheets("Sheet2").Cells(25, 5 + n).Value = dicPrimary.items(n)
    Next n

However, with the Dictionary object, we have a more efficient alternative available. We can use the Items method to return an array of items, and transfer them to a worksheet all at once. For example, we can do the following...

Worksheets("Sheet2").Cells(25, 5).Resize(, dicPrimary.Count).Value = dicPrimary.items

Hope this helps!

Domenic.... my apologies. Everything you've suggested seems to be working just fine and I thought I would be able to take the knowledge you shared and make any other adjustments as might be necessary. And I was able to do so ... except for one.

The last thing you shared with me was how to "resize" a worksheet. And yes.... that code works fine. Unfortunately, I also have to place the values in a column and I can't seem to figure out how to make that work.

My old code would have looked like the following and did work:

Worksheets("Sheet2").Cells(4 + n, 32).Value = collPrimary(n)

I attempted to use "resize" as you recommended, tried to do a little research on how to place values in a column but it fails to produce the outcome I was looking for. The code I tried to use was:

 Worksheets("Sheet2").Cells(5, 32).Resize(dicPrimary.Count).Value = dicPrimary.Items

Thanks for any final corrections or suggestions you may be able to make to help make this work.
Don

 

[Domenic]

You can simply transpose your items...

Worksheets("Sheet2").Cells(5, 32).Resize(dicPrimary.Count).Value = Application.Transpose(dicPrimary.Items)

Cheers!

 

[DonEB]

You can simply transpose your items...

Worksheets("Sheet2").Cells(5, 32).Resize(dicPrimary.Count).Value = Application.Transpose(dicPrimary.Items)

Cheers!

Perfect!! Thanks again!!

 

[Domenic]

You're very welcome, cheers!