Excel VBA - Create an Array - 3 ways
Ill show you three different ways to create an array in Excel VBA and Macros and how to get the data out of those arrays.
Arrays are types of variables that can hold multiple separate values. This allows you to store a lot of data in one variable so that you can later go through the variable and use what you need. This is much better than creating lots of separate variables to store the same type of information.
The first way to create an array is the simplest, we just use the Array() function in VBA:
Sub CreateArrayTest()
'first way to add an array
myarray = Array("red", "green", "blue")
mysecondarray = Array(10, 20, 30)
'output values from the arrays
MsgBox myarray(0) & " " & mysecondarray(2)
End Sub
This is the simplest way to create an array variable in VBA and Macros for Excel.
The array variables are myarray and mysecondarray. These arrays are simply variables which use the Array() function in order to be turned into arrays. Notice that the variables are not even declared at the top of the Macro (though that is good practice, it is not required here).
Download the accompany Excel spreadsheet from the side-menu or below this tutorial in order to test this and the following macros.
These two arrays, one to show you how to store text values and the other to show you how to store numbers are almost the same except that text must be surrounded with double quotation marks. You can just as easily store text with numbers in the same array variable.
You will also notice the output line:
MsgBox myarray(0) & " " & mysecondarray(2)
This shows you two important things.
You access values stored in arrays using numbers surrounded by parenthesis. These numbers are called indexes because they index the information stored in the array variable and allow you to retrieve what you need.
The second thing to notice is that, by default, the indexes start with 0 (zero) and NOT the number 1.
As such, the output from the MsgBox function above will be red 30.
The second way to create an array is to explicitly declare the desired Index Values:
Sub CreateArrayTest2()
'second way to add an array
'using the below method, we must declare that these variables will be arrays
Dim myarray(2) As Variant
Dim mysecondarray(2) As Variant
myarray(0) = "red"
myarray(1) = "green"
myarray(2) = "blue"
mysecondarray(0) = 10
mysecondarray(1) = 20
mysecondarray(2) = 30
'output values from the arrays
MsgBox myarray(2) & " " & mysecondarray(0)
End Sub
You will notice two big changes in this code. First, we must declare the variables as arrays:
Dim myarray(2) As Variant
Dim mysecondarray(2) As Variant
This is the same as declaring a normal variable type in a Macro except that we must tell Excel that this is an array variable. This is done but including the (2) after each variable.
The 2 simply means that there will be three slots where information can be stored in this array variable.
REMEMBER: Array variable indexes or slots start with 0 (zero) and NOT 1.
You can basically put any number in place of the 2; you could put 50 if you wanted to store 51 values or 1 if you wanted to store 2 values. This is kind of confusing and Ill show you how to fix this issue in the next example.
But, now that you know how to create the array variable, let us put data into it.
That takes us to these lines of the macro:
myarray(0) = "red"
myarray(1) = "green"
myarray(2) = "blue"
mysecondarray(0) = 10
mysecondarray(1) = 20
mysecondarray(2) = 30
This time we do not use the Array() function. Instead, we individually place each element inside of the array using the desired index number.
We want the text green to be stored in myarray at index 1 so we use this line:
myarray(1) = "green"
After this, we output the data into a message box just like in the previous example macro.
The third way to create an array in VBA is to declare the start and ending index numbers for each array:
Sub CreateArrayTest3()
'third way to add an array
'force the array 'counter' to start with 1 instead of zero
Dim myarray(1 To 3) As Variant
'second way to add an array
myarray(1) = "red"
myarray(2) = "green"
myarray(3) = "blue"
'output a value from the array
MsgBox myarray(3)
End Sub
Notice, the array variable declaration here looks different than in the previous example. Here it is:
Dim myarray(1 To 3) As Variant
What this says is that the myarray array variable should have its index number start at 1 and end at 3.
This is much more intuitive than having it start at 0 and end at 2 in order to store three items, as it was in the previous example.
In this case, the very first item red will be stored at index level 1 and nothing will be stored at index level 0.
You could basically have the index numbers be any range (5 to 10), (10 to 20), (50 to 100) or whatever you need. The main thing is to try and make everything as intuitive as possible so that you still understand what your Macro VBA is doing when you look at the code a year later.
As for the rest of this macro, it functions the same as in the previous two examples.
Remember, arrays in VBA can be confusing, BUT, they will save you a lot of time once you get comfortable using them. Download the spreadsheet accompanying this tutorial (below or in the side-menu) and you can follow the examples presented here.
I hope this tutorial was helpful! :)
Question? Ask it in our Excel Forum
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)