What is an Array?
• An array is a way to structure multiple pieces
of data of the same type and have them
readily available for multiple operations
• In this way they resemble Excel ranges
• As with ranges, you can perform operations
on an array in an efficient way using loops
Why use arrays when you have
• You might not want to do the calculations on a
sheet in the workbook; the array is selfcontained and you don’t risk wiping out other
data or revealing intermediate computations
to the user
• It can be very much faster to manipulate data
in an array rather than in a range, especially if
you are dealing with large amounts of data
• You might want three or more dimensions
VBA Arrays
• VBA arrays are declared much like variables:
Dim name As String ‘creates a normal variable
Dim nameArray(20) As String ‘creates an array
• The second Dim creates an array of 20 elements,
numbered from 1 to 20, similar to part of a row
or column
• You can optionally set your module to start
numbering at 0 instead of 1; this is better for
some kinds of code, but we’ll stick with the
Alternate Array Declarations
• Instead of using the simplest form of
declaration, as on the previous slide, you can
Dim testArray(1 To 20) As Double
• Or use a different lower bound:
Dim anotherTestArray(1955 To 2020) As String
Arrays vs Variables
• A variable is a place to store a value; a place in
the computer’s memory
• You reference the memory location by using
the name of the variable
• An array stores a sequence of values
• You reference an element by using the array
name and an index
Arrays vs Variables
Variable num of type Double occupies some location in memory
Array numArray of type Double has 6 elements and occupies 6
times the memory of one variable of type Double. Here,
numArray(3) has value 3.5.
Things to watch out for with arrays
• You must be careful not to try to put more
items in the array than it will hold (overflow).
Doing so should cause a runtime error but
may instead just result in mysterious bugs
• You should not try to index beyond the end of
an array. This should also cause a runtime
error, but might just result in getting a strange
answer back.
Array Size to Use
• Try to make your array big enough to handle
any reasonable expectation of how your
program will be used
• In many languages you are out of luck if you
guess wrong and run out of space in your
array. In VBA though we have the ReDim
• This is a very unusual feature of VB
• It lets you change the size of an array while
the program is running!
• Example:
ReDim Preserve exampleArray(1 To newSize)
• Without the Preserve, all the data in the array
is lost when you ReDim.
Dynamic Arrays
• You can also declare an array without giving it
an initial size. This is called a dynamic array.
(The empty parens tell VBA it’s an array)
Dim testDynamic() As String
• Later in the program, when you know how big
you need it to be, you can use ReDim to set
the size:
ReDim testDynamic (1 to size)
• [this slide used info from]
More Dimensions
• We’ve been looking at one-dimensional arrays,
but an array can have two (or more!) dimensions
• This is the array version of nesting. In some
languages you literally have an array of arrays,
but VBA uses more dimensions to achieve the
same effect
• Example:
Dim arrayName (1 To 3, 1 To 4) As Integer
arrayName(1, 2) = 20
Showing the whole array
Dim arrayName (1 To 3, 1 To 4) As Integer
arrayName(1, 2) = 20
‘row 1, column 2
The ArrayRangeDemo
• You will find most of the code in this
presentation in the workbook called
• Indexing is important when working with both
arrays and ranges
• The demo illustrates indexing in two
dimensional arrays and ranges; since it is
easiest to see what is happening with ranges,
most of the code modifies them
Copying A Range to an Array
• This is something you might want to do if you are
going to do extensive computations on the values in
the range
• It is possible to do this directly with one statement, if
everything is declared just right (for info see
• We’ll do it cell by cell using a nested loop
• Here is our array declaration:
Const DIM1 As Integer = 8 'length
Const DIM2 As Integer = 10 'width
Dim demo2DArray(1 To DIM1, 1 To DIM2) As Double
• And the range:
Dim twoDArea As Range 'global
Sub Workbook_Open()
Set twoDArea = Range(Cells(1, 1), Cells(DIM1, DIM2))
End Sub
Copy Range to Array
Sub RangetoTwoDArray()
Dim rowNdx As Integer, colNdx As Integer
For rowNdx = 1 To DIM1
For colNdx = 1 To DIM2
demo2DArray(rowNdx, colNdx) = Cells(rowNdx, colNdx).Value
Next colNdx
Next rowNdx
End Sub
Row vs Column
• For some reason, in a two-dimensional array
or range, it is customary to have the outer
loop go row by row and the inner loop go
across the columns
• The next few slides show you this method and
also how to go column by column instead
Filling the Range by Rows
Sub FillRangeByRow()
Dim rowNdx As Integer, colNdx As Integer, count As Integer
count = 1
For rowNdx = 1 To DIM1
For colNdx = 1 To DIM2
Cells(rowNdx, colNdx) = count
count = count + 1
Next colNdx
Next rowNdx
End Sub
The Result
Note how the numbers increase across
the rows first.
Filling the Range by Columns
Sub FillRangeByColumn()
Dim rowNdx As Integer, colNdx As Integer, count As Integer
count = 1
For colNdx = 1 To DIM2
For rowNdx = 1 To DIM1
Cells(rowNdx, colNdx) = count
count = count + 1
Next rowNdx
Next colNdx
End Sub
The Result
Note how the numbers increase
down the columns first
Using Random Data
• The demo has examples of filling the range (or
array) with random data, either the basic
random numbers which range between 0 and
1, or a modified version that generates
random numbers between 1 and 6
• It also includes code for doing some data
manipulation while the data is in the array;
give it a try!

Arrays - Portland State University