Excel VBA Array to Collection: Methods and Examples

VBA & macros · Updated November 21, 2026

To convert a VBA array to a Collection, create a Collection and add each array element in a loop. For example, assign Array("Red", "Green", "Blue") to a Variant, then use For i = LBound(values) To UBound(values) and call items.Add values(i) inside the loop. VBA has no built-in one-step conversion; the loop gives you control over order, keys, and how to handle the array’s shape.

Why convert an array to a Collection?

An array and a Collection both hold multiple values, but they suit different tasks. Arrays are useful when you know the data’s shape, want fast access by numeric index, or need to move a block of worksheet values into memory. A Collection is useful when you want an ordered group whose size can grow as you add items, or when you want to retrieve an item by a string key as well as by position.

For example, a macro might read a changing number of product names, keep selected names in a Collection, and pass that group to another procedure. You could also use a Collection when building a list gradually, without first calculating how large a dynamic array needs to be. Converting does not make the values inherently better or safer; it changes the container and its access rules.

A VBA Collection is a built-in object, so you do not need to add a reference to use it. It maintains insertion order, has a one-based numeric index, and provides an Add method. Unlike an array, it does not offer a direct assignment operation that copies all elements from an array at once.

Convert a one-dimensional array with a loop

This complete macro demonstrates the standard approach in Excel for Microsoft 365 on Windows. It creates an array, makes a new Collection, and adds the elements from the array’s actual lower bound through its upper bound.

Option Explicit

Sub ConvertArrayToCollection()
    Dim values As Variant
    Dim items As Collection
    Dim i As Long

    ' Array returns an array inside a Variant.
    values = Array("Red", "Green", "Blue")
    Set items = New Collection

    ' Use the array bounds rather than assuming they start at zero.
    For i = LBound(values) To UBound(values)
        items.Add values(i)
    Next i

    Debug.Print items.Count
    Debug.Print items(1)
End Sub

The Immediate window displays a count of 3 and then Red. To view it in the VBA editor, press Ctrl+G on Windows. On a Mac, the shortcut can depend on the keyboard and function-key settings; use the editor’s View menu to open the Immediate window if needed. To open the editor from Excel for Windows, press Alt+F11. On a Mac, use Option+F11 or the Developer tab’s Visual Basic command, depending on your keyboard setup.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a copied file gives you a safe way to recover if a macro later changes worksheet data unexpectedly.

How the conversion works

The loop visits the array one element at a time. LBound(values) returns the smallest valid index and UBound(values) returns the largest. Each call to items.Add appends one value, so the Collection’s item order follows the array’s iteration order. The destination Collection uses indexes starting at 1, even if the source array starts at 0 or another index.

In the example, values is a Variant containing an array. This is a convenient way to receive the result of the VBA Array function. The function commonly produces a zero-based array, but an Option Base statement can affect the default lower bound. Using LBound avoids relying on a particular starting index.

After conversion, access an element using a numeric position, such as items(1), or check how many elements were added with items.Count. The Collection is a separate container: adding its elements does not remove or alter the values in the original array. If you change an item in one container later, do not assume the corresponding item in the other container will change as well.

Use a reusable procedure

If several macros need the same conversion, put the loop in a function that returns a Collection. This example accepts an initialized, one-dimensional array stored in a Variant. It deliberately does not treat an unallocated dynamic array or a two-dimensional worksheet range as a valid input; those cases need separate handling.

Public Function ToCollection(ByRef source As Variant) As Collection
    Dim result As Collection
    Dim i As Long

    Set result = New Collection

    ' This routine expects an initialized one-dimensional array.
    For i = LBound(source) To UBound(source)
        result.Add source(i)
    Next i

    Set ToCollection = result
End Function

Sub UseArrayConversion()
    Dim values As Variant
    Dim items As Collection

    values = Array("North", "South", "West")
    Set items = ToCollection(values)

    Debug.Print items.Count
End Sub

The Set statements matter because a Collection is an object. The function returns the Collection object, not a numeric or text value. If you adapt this function for a larger project, document its input requirements or add explicit error handling for invalid input. A generic function that silently assumes every array is one-dimensional can be confusing when it receives data from a range.

Convert values from a worksheet range

Worksheet ranges introduce an important distinction: assigning a multi-cell range’s Value2 property to a Variant normally produces a two-dimensional array. Its first dimension represents rows and its second represents columns. A one-dimensional loop is therefore not suitable for a rectangular range.

For a range with multiple cells, use nested loops. This example adds values from A2:C4 to one Collection in row-major order: across the first row, then across the next row, and so on.

Sub RangeToCollection()
    Dim data As Variant
    Dim items As Collection
    Dim r As Long
    Dim c As Long

    ' A2:C4 is a multi-cell range, so Value2 returns a 2-D array.
    data = ThisWorkbook.Worksheets("Sheet1").Range("A2:C4").Value2
    Set items = New Collection

    For r = LBound(data, 1) To UBound(data, 1)
        For c = LBound(data, 2) To UBound(data, 2)
            items.Add data(r, c)
        Next c
    Next r

    Debug.Print items.Count
End Sub

With a three-row by three-column range, the Collection contains 9 items. Blank cells are also added as values; the loop does not skip them. If blanks should be ignored, test each value before calling Add, and decide whether a cell containing a formula that returns an empty string should count as blank for your task.

A single-cell range is a special case: its Value2 result is a scalar rather than the two-dimensional array returned for a multi-cell range. The nested-loop example assumes the range contains more than one cell. If the address can sometimes refer to one cell, handle that case separately, or loop directly through the range’s cells and add each cell’s Value2 value.

Preserve order, add keys, and handle bounds

For a straightforward conversion, call Add without a key. The Collection assigns positions in the order items are added, which is the simplest choice when you need an ordered list. If you supply a key, you can also retrieve an item by that key. Keys are strings, so an array of numeric IDs can be converted using CStr when creating each key.

For instance, this loop stores each value under a key based on its source index:

For i = LBound(values) To UBound(values)
    items.Add Item:=values(i), Key:=CStr(i)
Next i

You can then use the matching string key, such as items("0"), if the source array’s first index is 0. A Collection does not provide a simple built-in list of all its keys, so keep a separate key list if you need to enumerate keys later. Also, Collection keys must be unique: attempting to add another item with a key already in use raises an error. If duplicate values are acceptable, that is not a problem when you omit keys; duplicate keys and duplicate item values are different things.

Do not hard-code a loop from 0 to the number of items minus 1 unless you have confirmed those are the array’s bounds. Arrays may start at a different index, and an array populated from a range is usually two-dimensional. For a multidimensional array, specify the dimension in LBound and UBound, then use one loop per dimension, as in the range example.

Common problems and how to avoid them

  • Using an array index on a Collection: An array and a Collection can have different lower bounds. Collection positions start at 1, so read converted items using positions from 1 through items.Count.
  • Calling bounds on an uninitialized dynamic array: An array declared with Dim values() As String has no usable bounds until it is allocated. Calling LBound or UBound before allocation can raise an error. Allocate it first, or handle the uninitialized case separately.
  • Passing a two-dimensional range array to a one-dimensional routine: Use nested loops and specify both dimensions. A range’s row and column structure does not automatically flatten into a one-dimensional array.
  • Adding duplicate keys: A Collection cannot contain two items under the same key. Omit the key, make it unique, or choose a different structure if duplicate identifiers are part of the data.
  • Confusing empty cells with missing items: A loop that adds every range value also adds blanks. Decide whether to keep blank entries, skip them, or replace them with a marker before conversion.

When the input can vary, it is worth checking its type and shape before the loop rather than trying to make one short routine handle every possible input. In VBA, IsArray can tell you whether a Variant contains an array, but it does not by itself guarantee that a dynamic array is allocated or that it has the number of dimensions your procedure expects. Clear input requirements make conversion code easier to maintain.

Collection or array: which should you use?

Keep the data as an array when you need to process a rectangular block efficiently, preserve row and column positions, or work directly with values read from a worksheet. Arrays are often the natural format for bulk worksheet operations because Excel can transfer a range to a Variant array and back in a small number of operations. Flattening that array into a Collection loses the original two-dimensional layout unless you also store row and column information.

Choose a Collection when the list grows or shrinks as your code runs, when insertion order is useful, or when string-key access helps describe the items. A Collection is also convenient for a modest list that you build one element at a time without managing a dynamic array’s allocation and resizing.

A Dictionary is another option when lookup by key is central and you need dictionary-specific features. It is not the same object as a VBA Collection, and its availability and setup can differ across Windows and Mac environments. For a simple ordered list, a Collection avoids that extra consideration. For frequent lookups by key, compare the required behavior and compatibility before choosing; converting to a Collection merely because the source is an array is not automatically an improvement.

Frequently Asked Questions

Can VBA convert an array to a Collection in one command?
No built-in command converts an entire array to a Collection in one step. Create a Collection and add each element in a loop, using the array’s bounds.

Does the Collection keep the array’s lower bound?
No. The Collection’s numeric positions begin at 1. The loop uses the source array’s bounds to read its values, but the resulting Collection is indexed independently.

Can I convert a range directly to a Collection?
Not as a direct assignment. Read the range’s values and add them in a loop. A multi-cell range normally supplies a two-dimensional array, so use nested row and column loops if you want to flatten it into one ordered Collection.

Can a Collection contain duplicate values?
Yes, duplicate item values are allowed when you add items without keys. If you use keys, each key must be unique; duplicate keys cause an error even when the associated values differ.

Can I use this VBA code in Excel for the web?
No. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros. Use the Excel desktop application. VBA is also available in Excel for Mac, although shortcuts, macro security prompts, and some platform-specific features can differ.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.