How to Convert VBA Collections to Array in Excel?
An object that can store a number of values whether it can be of string data type or integer which can be easily manipulated or iterated is called Collection. On the other hand, Array is also used to store the data but it is multidimensional but collections are single dimensions. Now, we will see how to convert the collection to an array for that we have to follow further steps:
Converting Excel VBA Collection to Array
Step 1: Press Alt + F11 to get the VBA box and select Insert and Module to write the code.
Step 2: Define a sub-procedure in VBE.
Step 3: Declare two variables one with the name “collection” as New Collection and another one with the name “arr” of size 3 type String.
Step 4: Now, we will add items to the collection with the help of the “Add” keyword.
Step 5: Adding items to the array from the collection using each loop.
Step 6: Use MsgBox to print the elements in the array.
Step 7: Press F5 to see the output.