Excel VBA Hard-Coded Arrays: Syntax, Types, and Examples

VBA & macros · Updated November 21, 2026

Yes. In Excel VBA, you can hard-code a list of values with the Array function and assign it to a Variant, such as items = Array("North", "South", "West"). For an array with a specific element type or bounds, declare the array and assign its elements individually.

What a Hard-Coded Array Means in VBA

A hard-coded array is a collection of values written directly into VBA code rather than read from worksheet cells, a file, or user input. It is useful when the values are short, stable, and part of the macro’s logic: for example, a list of status labels, department names, or fixed thresholds. If the values change frequently, storing them in worksheet cells is usually easier to maintain.

VBA arrays are indexed collections. Each element has a position, called a subscript, and your code can read or change an element by referring to that position. This is different from a range of cells, where a value is identified by a worksheet address such as A1.

The Simplest Method: Use Array

The Array function creates an array from the values supplied in its argument list. Assign its result to a Variant; a variable declared only as String, Long, or another ordinary scalar type cannot hold the array returned by this function. Here is a complete procedure that prints each item in the Immediate window:

Sub ShowHardCodedArray()
    Dim items As Variant
    Dim i As Long

    ' Store fixed labels in a Variant array.
    items = Array("North", "South", "West")

    ' Use the actual bounds rather than assuming an index starts at zero.
    For i = LBound(items) To UBound(items)
        Debug.Print items(i)
    Next i
End Sub

To view output from Debug.Print, open the Visual Basic Editor and choose View > Immediate Window, or press Ctrl+G in the editor on Windows. On Mac, the Visual Basic Editor is available through the Developer tab or Tools > Macro > Visual Basic Editor, depending on the Excel interface; keyboard shortcuts can differ by Mac keyboard and settings. Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z.

Bounds and Indexing

An array’s lower bound is its first valid index, and its upper bound is its last. Many examples use zero-based indexing, but code should not rely on that unless the bounds are explicitly controlled. The module-level Option Base statement can affect the default lower bound used for arrays in that module, and it must appear before procedures. The safe general approach is to call LBound and UBound, as in the example above.

These functions also make loops resilient if the list changes. A loop written from 0 to 2 will fail or skip values if the array later has a different number of elements or starts at a different lower bound. For a nonempty array, UBound(items) - LBound(items) + 1 gives the number of elements. If a variable might not yet contain an initialized array, check that condition before calling the bounds functions; otherwise VBA can raise an error.

Choosing Between Variant and Typed Arrays

A Variant is convenient because it can hold the array returned by Array. The individual values can be strings, numbers, or other Variant-compatible values, and VBA can work with them without a separate declaration for each element type. That convenience comes with less strict type checking than a typed array.

For a fixed list of numbers where type clarity matters, declare a typed array and assign each element. This takes a few more lines but makes the intended storage type explicit:

Sub ShowTypedArray()
    Dim scores(0 To 2) As Long
    Dim i As Long

    ' Assign each value to a Long element.
    scores(0) = 12
    scores(1) = 18
    scores(2) = 25

    For i = LBound(scores) To UBound(scores)
        Debug.Print scores(i)
    Next i
End Sub

In this example, the array always has three elements, indexed 0 through 2. A fixed-size typed array is a good fit when the item count and data type are known when you write the code. If the number of items should change at runtime, use a dynamic array instead.

Dynamic Arrays and Changing the List

A dynamic array is declared without bounds and given bounds later with ReDim. This is useful if code calculates the required number of entries or builds a list conditionally. The following procedure creates three string elements and fills them one at a time:

Sub BuildDynamicArray()
    Dim cities() As String

    ' Allocate three elements, indexed from 0 through 2.
    ReDim cities(0 To 2)
    cities(0) = "Dublin"
    cities(1) = "Lima"
    cities(2) = "Osaka"

    Debug.Print cities(1)
End Sub

ReDim Preserve can resize a dynamic array while retaining existing values, subject to VBA’s array-dimension rules. In a multidimensional array, only the last dimension can be resized with Preserve. Repeatedly expanding an array one element at a time can also be inefficient for large lists, so if data comes from a worksheet, reading a suitable range into a Variant array may be simpler and faster than constructing every element in a loop.

One-Dimensional Lists, Multiple Dimensions, and Worksheet Ranges

The ordinary Array call is a convenient way to write a one-dimensional list. It does not provide a direct syntax for a rectangular, two-dimensional array such as a table with several rows and columns. For that structure, declare a two-dimensional array and assign each position, or read a rectangular worksheet range into a Variant. Keep the row and column bounds in mind: the first subscript identifies the first dimension and the second identifies the next.

When a macro transfers a two-dimensional Variant array to a worksheet range, the array dimensions should match the target range dimensions. A one-dimensional result from Array is not automatically a two-dimensional column or row array just because it is assigned to a range. For a small fixed list, writing values to cells one by one may be straightforward; for a larger block, prepare a matching two-dimensional array and assign it to the range in one operation.

When Hard-Coding Is a Good Choice

Hard-coded values are easiest to justify when they are few, unlikely to change, and meaningful as part of the program itself. Examples include a short set of permitted codes, fixed labels used in a report, or a small mapping that has no need for end-user editing. Keeping these values together in one array can make code clearer than repeating the same literal in several procedures.

Move the values out of VBA when ordinary workbook users need to edit them, when the list is long, or when the list changes as business rules change. A worksheet table is often more transparent than code: users can review it, sort it, and update it without opening the Visual Basic Editor. A named range or a structured Excel table can also make the source data easier to identify and maintain. For workbook-distributed macros, remember that VBA code requires a macro-enabled file format such as .xlsm and that recipients may need to allow macros under their organization’s security settings.

Common Errors and Misconceptions

  • Declaring the receiver with the wrong type: Use Dim items As Variant for the direct result of Array. A scalar variable such as Dim items As String is not the right container for that array.
  • Assuming the first index is always zero: The default lower bound can depend on module settings. Use LBound and UBound, or explicitly declare bounds in a typed array.
  • Confusing an array with a collection of worksheet cells: An array exists in VBA memory. It does not create cells, write values to a sheet, or update itself when a worksheet changes.
  • Expecting an Array call to create a two-dimensional table: The usual call supplies a one-dimensional sequence. Build a multidimensional array explicitly or obtain one from a rectangular range.
  • Editing a hard-coded list in several places: Duplicated literals are easy to let drift apart. Keep a shared list in one location, or use a worksheet source if people need to maintain it.

Another common misunderstanding is that putting values in an array automatically makes a macro safer or faster. Arrays are a useful way to organize data in memory, but they do not validate the values. If values come from a user or worksheet, check their type and content before relying on them.

Frequently Asked Questions

Can I hard-code a string array in Excel VBA?
Yes. Assign a call such as Array("Open", "Pending", "Closed") to a Variant, or declare a dynamic array as String and assign each element after using ReDim.

Does VBA Array start at zero or one?
Do not assume either bound in general code. Module settings such as Option Base can affect default bounds, so use LBound to find the first valid index and UBound to find the last.

Can I put an array directly into a worksheet range?
Yes, but the array’s shape must suit the target range. A one-dimensional array is not automatically a two-dimensional block. For a rectangular range, use a matching two-dimensional array or read the data from a rectangular range.

Should I hard-code values or store them on a sheet?
Use code for short, stable values that define the macro’s behavior. Use a worksheet table or range when users should be able to edit the list, when it is lengthy, or when it changes regularly.

Can I use Array to create a typed Long array?
Array returns a Variant array, not a declared Long() array. To ensure elements are stored as Long values, declare a Long array with bounds and assign its elements explicitly.

Practical Rule of Thumb

For a short fixed list, assign Array(...) to a Variant and loop from LBound to UBound. For fixed-size numeric or string data that benefits from stronger type clarity, declare a typed array and set its elements. For data that users maintain or that changes often, keep it in the workbook rather than embedding it in code. Choose the form that makes both the values and their maintenance requirements obvious to the next person who reads the macro.

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.