In Excel VBA, declare a string array with Dim names() As String, give it a size with ReDim names(0 To 2), then assign text to its elements. For a quick list, use Array("Red", "Green", "Blue") in a Variant variable; that creates an array whose elements are Variants, not a declared String() array.
What a VBA string array is
An array stores multiple values under one variable name, with each value reached through an index. A string array is useful when a macro needs to hold names, labels, file paths, worksheet names, or other text temporarily. Instead of creating separate variables such as name1, name2, and name3, you can loop through names(0), names(1), and names(2).
VBA arrays can be fixed-size or dynamic. A fixed-size declaration sets the bounds when the variable is declared. A dynamic array is declared with empty parentheses and sized later using ReDim. The examples here are for VBA in Excel for Microsoft 365 on Windows; the core array syntax is also available in Excel for Mac.
Create a small array with ReDim
For a clearly typed array whose size is known, declare it as String and set its bounds. This example creates three elements, with indexes 0 through 2:
Sub CreateStringArray()
Dim colors() As String
' Allocate three string elements, indexed 0 through 2.
ReDim colors(0 To 2)
colors(0) = "Red"
colors(1) = "Green"
colors(2) = "Blue"
Debug.Print colors(1) ' Prints Green
End Sub
The bounds are part of the array’s structure. ReDim colors(0 To 2) creates three slots; it does not mean the array has indexes 1, 2, and 3. You can instead choose ReDim colors(1 To 3) if that indexing better matches a worksheet or another part of your code. State the bounds explicitly when you want the code to be easy to read.
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. To run this example in Excel for Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, paste the code, then run the procedure from the editor or the Macros dialog. On Mac, the Visual Basic Editor is available from the Developer tab or Tools > Macro > Visual Basic Editor, depending on the ribbon setup; keyboard shortcuts can differ by keyboard and macOS settings.
Use Array() for a quick list
The VBA Array function is convenient when the values are already known. It returns an array held in a Variant, so declare the receiving variable as Variant:
Sub UseArrayFunction()
Dim colors As Variant
Dim i As Long
colors = Array("Red", "Green", "Blue")
For i = LBound(colors) To UBound(colors)
Debug.Print colors(i)
Next i
End Sub
This form is concise, but it is not the same as declaring Dim colors() As String. The returned array is a Variant array, and its elements are Variants containing the supplied values. That is usually fine for a short list or when passing values around flexibly. If your code needs a true String() array, allocate one and copy the values:
Sub CopyValuesToStringArray()
Dim source As Variant
Dim names() As String
Dim i As Long
source = Array("Ava", "Noah", "Mia")
ReDim names(LBound(source) To UBound(source))
For i = LBound(source) To UBound(source)
names(i) = CStr(source(i))
Next i
Debug.Print names(0) ' Prints Ava with the default Option Base
End Sub
In a module with the default Option Base 0, this array starts at zero. If the module uses Option Base 1, the lower bound behavior for an unqualified Array call can differ. Using LBound and UBound rather than assuming a particular starting index makes loops more dependable.
Choose fixed-size or dynamic arrays
A fixed-size array is suitable when the number of elements is known and will not change. For example, Dim weekdays(1 To 5) As String declares five string slots. A dynamic array is more adaptable when the size depends on data in a range or on user input:
Sub GrowStringArray()
Dim words() As String
ReDim words(0 To 1)
words(0) = "first"
words(1) = "second"
' Preserve existing values while extending the last bound.
ReDim Preserve words(0 To 2)
words(2) = "third"
End Sub
ReDim allocates or changes the dimensions of a dynamic array. Without Preserve, resizing clears existing values. With Preserve, VBA retains them, but for a multidimensional array you can change only the final dimension while preserving data. The example is one-dimensional, so extending its upper bound is allowed.
Repeatedly enlarging an array one item at a time with ReDim Preserve can be inefficient for large lists, because VBA must preserve and move existing values each time. If you know the number of items in advance, allocate once. If you do not, consider collecting data in a VBA Collection or another suitable structure, then copying it into an array when needed.
Build an array from comma-separated text
Split is a practical way to turn delimited text into an array. It returns a Variant containing a one-dimensional array of substrings. For example, this code separates a comma-delimited line and trims spaces from each item:
Sub SplitTextIntoItems()
Dim items As Variant
Dim i As Long
items = Split("Red, Green, Blue", ",")
For i = LBound(items) To UBound(items)
items(i) = Trim$(CStr(items(i)))
Debug.Print items(i)
Next i
End Sub
The printed values are Red, Green, and Blue. The delimiter must match the text: use a semicolon for semicolon-separated text, for example. Split does not automatically remove spaces, handle quoted commas as CSV fields, or convert the result to a declared String() array. If the input is a real CSV file with quoted fields, splitting on commas alone is not a complete CSV parser.
Read worksheet text into a string array
To collect values from a worksheet range, size the array from the range and copy each cell. This example reads A2:A5 on a worksheet named Data and makes an array indexed from 1 through the number of rows:
Sub ReadNamesFromRange()
Dim ws As Worksheet
Dim sourceRange As Range
Dim names() As String
Dim rowNum As Long
Dim cellValue As Variant
Set ws = ThisWorkbook.Worksheets("Data")
Set sourceRange = ws.Range("A2:A5")
ReDim names(1 To sourceRange.Rows.Count)
For rowNum = 1 To sourceRange.Rows.Count
cellValue = sourceRange.Cells(rowNum, 1).Value2
If IsError(cellValue) Then
names(rowNum) = ""
ElseIf IsEmpty(cellValue) Then
names(rowNum) = ""
Else
names(rowNum) = CStr(cellValue)
End If
Next rowNum
Debug.Print names(1)
End Sub
Here the source range has four rows, so names has indexes 1 to 4. A blank cell becomes an empty string, and a cell containing an Excel error is also represented as an empty string to avoid passing an error value to CStr. Change that behavior if errors should be reported or skipped instead. If the range is a single cell, direct range-to-Variant assignments need special handling because a one-cell range value is not shaped like a multi-cell two-dimensional array; the cell-by-cell approach above avoids relying on a bulk array assignment.
Loop safely through array bounds
Use LBound and UBound to find an array’s lower and upper indexes. Do not assume every array starts at zero or one. Bounds can vary according to how the array was declared, how it was returned, and any Option Base statement in the module. A loop that uses the actual bounds works for either base:
Sub PrintArrayItems()
Dim items As Variant
Dim i As Long
items = Array("North", "South", "West")
For i = LBound(items) To UBound(items)
Debug.Print items(i)
Next i
End Sub
Use a Long counter for array indexes in ordinary Excel VBA code. It is also helpful to check whether a dynamic array has been allocated before calling its bounds in code that handles optional or not-yet-filled arrays. An unallocated dynamic array does not behave like an allocated array with zero elements, so code should not blindly assume its bounds are available.
Common errors and how to avoid them
- Type mismatch when assigning Array() to String().
Array()returns a Variant array. Store it in a Variant, or copy its elements into an explicitly declared String() array. - Subscript out of range. An index is outside the declared bounds. Check the bounds used in
ReDim, and useLBoundandUBoundin loops. - Values disappear after resizing.
ReDimnormally discards existing values. UseReDim Preservewhen retaining values is intended and the array dimensions permit it. - Unexpected spaces after Split. Splitting at a comma does not remove spaces next to the delimiter. Apply
Trim$to each result when appropriate. - Unexpected values from worksheet cells. A cell can contain an error, be empty, or hold a number or date rather than text. Decide how to handle each case before converting values with
CStr. - Macro does not run. Save macro-enabled work as an
.xlsmfile, and only enable macros in workbooks from sources you trust. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use desktop Excel for those tasks.
When a collection or worksheet range is a better fit
An array is a good choice when you need indexed access, predictable processing, or a compact set of values for a procedure. A VBA Collection can be more convenient when items are added one at a time and the final count is not known. A worksheet range may be preferable when the values should remain visible and editable to workbook users. These options serve different purposes: arrays are in-memory structures, while a range is workbook data and a Collection is a flexible VBA container.
For a short hard-coded list, a Variant assigned from Array() is often the least typing. For a typed list of strings with known bounds, use a String() array. For a list read from cells, size a dynamic String() array from the range and define what blanks and errors should mean. Choosing based on how the values are created and used is more useful than treating one array style as universally best.
Frequently Asked Questions
How do I declare an array of strings in VBA?
Use Dim names() As String for a dynamic array, then allocate it with ReDim names(0 To 2). Use Dim names(1 To 3) As String when the size is fixed.
How do I initialize a string array in one line?
For a quick list, write Dim names As Variant followed by names = Array("Ava", "Noah", "Mia"). That produces a Variant array, not a declared String() array. Copy the values into a String() array if that distinction matters in your code.
Does a VBA array start at zero or one?
It depends on how the array is created and the module’s Option Base setting. Explicit bounds such as ReDim names(1 To 3) make the choice clear. Use LBound and UBound when looping over arrays whose bounds may vary.
How can I add another item to a string array?
For a dynamic one-dimensional array, increase its upper bound with ReDim Preserve, then assign the new element. For example, after an array runs from 0 to 1, ReDim Preserve names(0 To 2) retains the first two values and makes index 2 available.
Can Excel for the web run VBA arrays?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; the array concepts are VBA language features rather than worksheet formulas.
Related
- Excel Text to Columns: Why Leading Zeros Disappear
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Input Box With Multiple Inputs: Practical OptionsExcel’s VBA InputBox collects one response per prompt, not several separate fields in a single dialog. To gather…
- Array Errors in Excel VBA: Causes and FixesAn “array error” in Excel VBA is not one specific error: it usually means code has requested an…
- Excel Macro Error? Common Causes and FixesAn Excel macro error usually means VBA is blocked, the code cannot find an expected workbook, worksheet, or…
- VBA Dictionary Add Item: Syntax and ExamplesIn Excel VBA, add an item to a Scripting.Dictionary with dict.Add key, item; create the dictionary first, and…
- Excel VBA Absolute Value: How to Use AbsIn Excel VBA, use the built-in Abs function to return a number’s absolute value: Abs(number). For example, Abs(-12.5)…
- Excel VBA Sort Order: Ascending, Descending, and CustomIn Excel VBA, set a sort field’s Order to xlAscending or xlDescending to choose its direction. For a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.