Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Easy way to convert an Array to a Collection in VBA

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!

like image 403
Peter T. Avatar asked Sep 04 '12 07:09

Peter T.


People also ask

How do I change an array to a collection?

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.

How do I create a collection in Excel VBA?

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.”

Is a collection an array VBA?

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.


1 Answers

"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)
like image 101
Jon Egerton Avatar answered Oct 05 '22 19:10

Jon Egerton