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 Stringhas no usable bounds until it is allocated. CallingLBoundorUBoundbefore 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
- Excel Split Cell Data Into Multiple Columns
- Excel Split Cell by Space Delimiter: Methods and Tips
- Excel Split Cell Content Into Columns: Methods That Work
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Word VBA Mail Merge: How to Automate ItWord VBA can automate a mail merge by telling a prepared Word merge document to create a new…
- VBA Dictionary Count: What It Returns and How to Use ItIn VBA, read a dictionary’s item count with its Count property: dict.Count. It returns the number of key–item…
- VBA Sort Data in Excel: How to Build a Reliable MacroYes. In Excel for Microsoft 365 on Windows, VBA can sort a range or table by one or…
- Excel Number to Words: Formula, VBA, and How to Use ItExcel has no built-in English worksheet formula that converts an ordinary number into words. In Microsoft 365 for…
- VBA Macro to Send Email From Excel: Setup and CodeTo send email from Excel with VBA, automate classic Outlook for Windows: place the recipient, subject, message, and…
- VBA Sort Sheets Alphabetically: Macro and StepsYes. In Excel for Microsoft 365 on Windows, a short VBA macro can sort a workbook’s sheet tabs…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.