Arrays and spreadsheets

Most data tables have more rows than columns. For instance an old style phonebook has only 3  columns – name, address and phone number – but it has thousands of rows. Each row is a unit of data called a record.  A new row is simple to add, but adding another column is a problem.  The data is read the same way a book is read, across the page then down.

A 2 dimensional array in vba is a variable that holds data like a table. The first dimension is the number of rows. It will usually be the larger number. The second dimension is the number of columns.  A 52 week calendar would be dimensioned – calendar_array(1 to 52, 1 to 7) as string.

experienced programmers may find these generalizations annoying, but i think they will help lesser experienced people write array loops off the top of their head without resorting to references. The plan is to finish with a working example that solves two big needs in our autocad graphing calculator program, and only to discuss the features of arrays we are going to use.

The cells property of the worksheet uses the same format – Cells (row_no, column_no). The cells property is one-based. Row and column numbers start with one. Arrays should be dimensioned to start with one to keep the looping simple. Excel has a shortcut single line method to write the contents of an array to a spreadsheet, or to write the contents of a spreadsheet to an array.  Often though a loop is needed for some processing purpose.  There is a standard structure for looping through a table or an array.  Two loops, one inside the other, are used. The inner loop reads the row contents and does the work. The outer loop drops down to the next row.  The outer loop increments row numbers. The inner loop increments column numbers and processes one record at a time. The variables i and j are traditional.

Here is an example program.  It has a loop within a loop to read integers into the array. Then it has another loop in a loop to write the array to the spreadsheet.  The second double loop could be replaced with excel’s shortcut( below), but as is it shows how cells are referenced.

Dim rng As Range
Set rng = Range(“a1:g52”)
rng = my_arr

the reverse also works, if dimensioned as variant

dim my_arr as Variant

my_arr = rng


Sub array_52x7()
Dim i As Integer, j As Integer, x As Integer
'2d array is like a table of data
'row x col
Dim my_arr(1 To 52, 1 To 7) As Integer
'MsgBox LBound(my_arr, 1)
'MsgBox UBound(my_arr, 1) 'gives 52
'MsgBox LBound(my_arr, 2)
'MsgBox UBound(my_arr, 2) ' gives 7

'i outer loop row numbers
'j inner loop column numbers
'my_arr(rows, cols)
'data fills row left to right then top to bottom
'(1,1) (1,2) (1,3) etc then drops down
'(2,1) (2,2) (2,3) etc
x = 1

For i = LBound(my_arr, 1) To UBound(my_arr, 1)
For j = LBound(my_arr, 2) To UBound(my_arr, 2)
my_arr(i, j) = x
x = x + 1
Next j
Next i

For i = LBound(my_arr, 1) To UBound(my_arr, 1)
For j = LBound(my_arr, 2) To UBound(my_arr, 2)
Cells(i, j) = my_arr(i, j)
Next j
Next i

End Sub

Even though these are static arrays – their dimensions are explicitly called out – LBound and Ubound are convenient to control the loop, and become necessary with dynamic arrays.  The second parameter of Ubound and Lbound is the dimension of the array. 1 is the row, 2 is the column.

Rows are listed first in Cells and Arrays, they are the outer loop.

Columns are listed second, they are the inner loop.

In practice many if not most Arrays are dynamic. The program has a variable number of data to work with. In a graphing calculator program, the user selects the range of data and the increment creating a different number of plotted data points on each run.  The array is dimensioned as

Dim pt() as Double

The compiler knows it is an array and the dimension is to be given at runtime. When the calculation is made in the program how many data points are needed, the array is Re-dimensioned.

ReDim pt(1 To numpts * 2)  ‘store x and y for one pt

ReDim will size the array and destroy any data in the array, which is usually what is desired, a fresh set is to be written.  However there is a method and keyword used to preserve existing data where the upper bound of the index is increased by one each time through the loop.

for i = 1 to numpts

Redim Preserve pt(1 to i)

some function and test ...

next i








Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.