Storing Many Values with Arrays
An array holds many values under one variable name, accessed by an index. Instead of a hundred separate variables, one array stores a hundred elements. VBA supports fixed-size arrays whose bounds are set at declaration and dynamic arrays that can be resized at run time with ReDim.
Cricket analogy: A batting order is an array of eleven players indexed by position—element 1 the opener, element 11 the tail-ender—one named lineup holding many players you access by number.
Declaring and Indexing Arrays
Dim nums(4) As Integer creates a fixed array; by default indices run 0 to 4, giving five elements. Access them with nums(0) through nums(4). You can set explicit bounds—Dim scores(1 To 12)—and build multi-dimensional arrays like Dim grid(1 To 3, 1 To 3). LBound and UBound return the lowest and highest valid indices.
Cricket analogy: Declaring an over as balls(1 To 6) fixes six deliveries you address by number, and a scorecard grid indexed by innings and player is a two-dimensional array.
Sub ArrayBasics()
Dim scores(1 To 5) As Integer
Dim i As Long
scores(1) = 88
scores(2) = 92
scores(3) = 75
scores(4) = 60
scores(5) = 100
For i = LBound(scores) To UBound(scores)
Debug.Print "Score " & i & ": " & scores(i)
Next i
End SubDynamic Arrays and ReDim
Declare a dynamic array with empty parentheses—Dim data() As Double—then size it later with ReDim data(1 To n) once n is known. ReDim by itself discards existing contents; ReDim Preserve keeps them but can only resize the last dimension of a multi-dimensional array. Resizing repeatedly with Preserve is costly, so grow in chunks when possible.
Cricket analogy: A squad list you cannot size until selection day is a dynamic array; ReDim Preserve is adding a player without losing those already picked, while plain ReDim wipes the sheet clean.
By default array indices start at 0. Putting Option Base 1 at the top of a module makes them start at 1 instead. Rather than assuming bounds, always loop with LBound and UBound so your code works regardless of the base or how the array was sized.
Arrays and Worksheet Ranges
The fastest way to process large worksheet data in VBA is to read a range into a Variant array in one shot—arr = Range("A1:D1000").Value—process it in memory, then write it back with Range(...).Value = arr. This two-dimensional array is 1-based with rows as the first dimension and columns as the second, and it avoids thousands of slow individual cell reads.
Cricket analogy: Rather than asking the scorer for each run one ball at a time, you photocopy the whole scorecard at once; reading a range into an array is that single bulk grab instead of a thousand slow lookups.
Sub BulkUppercase()
Dim arr As Variant
Dim r As Long, c As Long
arr = Range("A1:D1000").Value ' one fast read
For r = 1 To UBound(arr, 1)
For c = 1 To UBound(arr, 2)
arr(r, c) = UCase(arr(r, c))
Next c
Next r
Range("A1:D1000").Value = arr ' one fast write
End SubReDim without Preserve erases every existing element—use ReDim Preserve when you need to keep data. Remember that Preserve can only resize the LAST dimension of a multi-dimensional array; trying to resize an earlier dimension raises a run-time error. Also, accessing an index outside LBound..UBound throws 'Subscript out of range' (error 9).
- An array stores many values under one name, accessed by index.
- Fixed arrays set their size at declaration; indices default to 0-based unless Option Base 1 is set.
- Multi-dimensional arrays like grid(1 To 3, 1 To 3) store tabular data.
- Dynamic arrays use empty () at declaration and ReDim to size at run time.
- ReDim Preserve keeps existing data but can only resize the last dimension.
- Reading a range into a Variant array and writing it back in one shot is far faster than cell-by-cell access.
Practice what you learned
1. What does ReDim Preserve do?
2. What is the default lower bound of a VBA array?
3. Which dimension can Preserve resize in a two-dimensional array?
4. What is the fastest way to process 10,000 cells in VBA?
5. What error results from indexing outside an array's bounds?
Was this page helpful?
You May Also Like
Loops in VBA
Master repetition in VBA with counter-driven For...Next, collection-driven For Each, and condition-driven Do loops.
Subs and Functions in VBA
Understand VBA's two procedure types—action-performing Subs and value-returning Functions—plus arguments, scope, and UDFs.
Conditionals in VBA
Learn how VBA evaluates Boolean expressions to branch code using If...Then...Else, ElseIf chains, and Select Case.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics