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 Variantfor the direct result ofArray. A scalar variable such asDim items As Stringis not the right container for that array. - Assuming the first index is always zero: The default lower bound can depend on module settings. Use
LBoundandUBound, 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
- Excel Text to Columns: How to Enter a Delimiter
- Excel Text to Columns: Can It Split Capital Letters?
- Excel Text to Columns: Split a Cell at Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Pivot Table Source Types ExplainedIn Excel VBA, the main PivotTable source types are xlDatabase for a worksheet range or table, xlExternal for…
- VBA Excel: Check a Cell’s FormatTo check a cell’s format in VBA, read the relevant property of its Range object: use Range("A1").NumberFormat for…
- VBA Excel Sort Rows: Code, Options, and Common FixesTo sort Excel rows with VBA, define the complete data range, choose a key column, and apply a…
- Excel VBA Find Method: Syntax, Examples, and TipsIn Excel VBA, use the Range.Find method to search a specified range and return the first matching cell;…
- VBA Function Structure: Syntax, Examples, and ScopeA VBA function is a procedure that performs work and returns a value. Its basic structure is Function…
- VBA Calendar UserForm: Build a Date Picker in ExcelA VBA calendar UserForm is a custom date picker that lets someone choose a date inside an Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.