How to Populate an Array with Excel VBA

Arrays are great way to quickly gather data, put it into Excel’s memory and then manipulate this using VBA code. In this post I am going to show you how to populate an array with Excel VBA.

So for this example lets assume you have some data in the cell range A1 to B10 and you want to put this into an array to then use elsewhere within a routine. To start with you need to open the VBE for Excel and then add a new module. Once you have done this put the routine (or macro) below into the module.

Populate Array in Excel

Sub MyFirstArray()

Option Base 1
Dim MyArray(10, 2) As Long
Dim X as Long
Dim Y as Long
For X = 1 To 10 Step 1
For Y = 1 To 2 Step 1
MyArray(X, Y ) = Cells(X, Y).Value
Next Y
Next X

End Sub

Then when you run this code it will copy the data in the cell range to the memory. Okay, lets run through this line by line and explain what it does.

Option Base 1
Normally when you populate an array with data, the array will start at zero. So for example the first piece of data from cell A1 would be referenced in the Array at 0,0. This can make it very confusing. So, if you include Option Base 1 it tells Excel to start the array at 1.

Dim MyArray(10, 2) As Long
Here we are declaring the array, it’s data type, and its dimensions, to Excel. So we have called the array MyArray, then told Excel that it will 10 by 2 in size. For ease explaining think of this as ten rows by two columns. Then finally we have told Excel that the data the array will contain will be Long data. See this post about data types in Excel.

Dim X as Long
Dim Y as Long
Here we are declaring two variables, X and Y, so that we can use them to gather data and then populate the array. We have to use variables because we will be getting Excel to gather the data using a For Loop so it knows where to start and where to finish. The X and Y represent row and column numbers and will increment upwards.

For X = 1 To 10 Step 1
For Y = 1 To 2 Step 1
These two lines tell Excel that we wish to start a loop for it to go round. The first loop is for X and this represents the row numbers. The second loop is for Y which represents the columns. Notice we have told Excel that X must only go from 1 to 10 and Y must go to 1 to 2 only. So we are using two loops.

MyArray(X, Y ) = Cells(X, Y).Value
This line here populates the array. So in the previous two lines we initially declared X as 1 and Y as 1. So if we remove the X and Y from MyArray(X, Y ) = Cells(X, Y).Value the line would read MyArray(1, 1 ) = Cells(1, 1).Value. So it is telling Excel that the first data to be stored in the array at position 1,1 must be taken from Cell 1,1 (A1).

Next Y
Next X
These two lines here tell Excel to repeat the previous code (as we declared two loops) in a set order. But it will it will first increment the Y value to 2 so when it loops it would read like this without the X and Y, MyArray(1, 2 ) = Cells(1, 2).Value. Once it has done this it has reached the end of the Y loop. So it then increments the X value by one place. Because X the X loop is first it also resets the Y loop. So this routine would populate the array in the following order using the two for loops:

A1,A2
B1,B2
C1,C2
D1,D2
E1,E2
F1,F2
G1,G2
H1,H2
I1,I2
J1,J2

Once you have the array populated you can then start handling the data and manipulating it. But that is for another post!

3 thoughts on “How to Populate an Array with Excel VBA

  1. Jonny Dent

    Hi,

    Useful artical, but unfortunately incorrect.

    Declaring like this:

    Dim MyArray(10, 2) As Long

    Will actually make an array of 11 rows by 3 columns…

    Jonny

  2. andrew walker Post author

    Hi Jonny

    Thanks for your comment. However on this occassion it is in fact 10 by 2 and not 11 by 3 as you suggest. This because when you use Option Base 1 it forces the array not to start at zero but one, hence you get 10 by 2.

    If I were to use the macro without Option Base 1 then yes it would indeed be an array 11 rows by 3 columns.

    Thanks for writing.

  3. Dan

    I keep on getting error messages. I copied and pasted the exactly how it shows above and keep getting “Invalid Inside Procedure” and Option Base 1 is highlighted. The fix is to put it outside the Sub. Then I fix it and there is another error message “Type mismatch” and this line gets highlighted – MyArray(X, Y ) = Cells(X, Y).Value – Helpful the topic but not the example.

Leave a Reply

Your email address will not be published. Required fields are marked *