Menu
Is there an easy way to populate a Collection with all Values from an Array in VBA? e.g. something like
Dim c As New Collection
Dim a(10) As Variant
...
c.AddAll a
A simple solution would be of cause to iterate over the array, but I would expect that a modern language offers such methods out of the box ...
Dim c As New Collection
Dim a(10) as Variant
...
For Each item in a
c.Add item
Next item
Thanks for any hints!
403
To convert array-based data into Collection based we can use java. util. Arrays class. This class provides a static method asList(T… a) that converts the array into a Collection.
To get started with collection first, we need to declare the variable as “Collection.” Since the collection is an object variable, we need to set the object reference by creating a new instance. Now with the variable, we can access all the methods of collection variable “Col.”
Collections in VBA are objects that can store groups of related items, much like an array. Unlike arrays, a single collection can store items of different types because each item in a collection is stored as a Variant.
"modern language" is where your problem lies - VBA/VB6 aren't really modern - neither have been advanced much for some years.
If you need to do it a lot, write a function to do the looping:
Sub AddAll(ByVal c as Collection, a as Variant)
For Each item in a
c.Add item
Next item
End Sub
or if you want a new collection each time:
Function ToCollection(a as Variant) As Collection
Dim c As New Collection
For Each item in a
c.Add item
Next item
Set ToCollection = c
End Function
and then use it:
Dim c As New Collection
Dim a(10) as Variant
...
AddAll c,a
or
Dim a(10) as Variant
Dim c as Collection
...
Set c = ToCollection(a)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
