VBA Array Variables

We covered an introduction to variables and constants in our VBA Data Types – Variables and Constants introductory tutorial. In this tutorial, we are going to look at what array variables are and how you can use them in your code.

What is a VBA Array Variable?

A VBA array variable stores a list or group of elements. A VBA array variable can also be thought of as a group of variables, stored under the same name and having the same data type. All the elements in the array must be the same type. An example would be a list of fruits. An array can store text or numbers. You refer to an element in an array using its index number. You can declare an array variable using the Dim, Static, Public or Private keyword.

Static Array Variable

A Static array variable has a fixed size or set number of items in the list. You would declare a Static array variable in the following way:

Sub DeclaringAStaticArrayVariable()

Dim Shoes(1 To 3) As String

Shoes(1) = "Boots"
Shoes(2) = "Sandals"
Shoes(3) = "Sneakers"

End Sub

You can display a certain element in the array by referring to its index number as shown in the code below:

Sub DeclaringAStaticArrayVariable()

Dim Shoes(1 To 3) As String

Shoes(1) = "Boots"
Shoes(2) = "Sandals"
Shoes(3) = "Sneakers"

Debug.Print Shoes(1)

End Sub

If you press F5 to run your code you would get the following result in the Immediate Window:

Dynamic Array Variable

The size of a Dynamic array variable changes at runtime and is set in other words at runtime only. You don’t initially declare the number of items or elements in the array as for the Static array variable, but you use the ReDim keyword to specify the size or the number of items that a Dynamic Array has. You can then resize your array using the ReDim keyword:

Sub DeclaringADynamicArrayVariable() 

Dim differentvegetables() As String 

ReDim differentvegetables(3) 

differentvegetables(1) = "carrots" 
differentvegetables(2) = "pumpkin" 
differentvegetables(3) = "butternut" 

MsgBox Join(differentvegetables, vbCr) 
ReDim differentvegetables(4) 

differentvegetables(1) = "carrots" 
differentvegetables(2) = "pumpkin" 
differentvegetables(3) = "butternut" 
differentvegetables(4) = "cabbage" 

MsgBox Join(differentvegetables, vbCr) 

End Sub

When you run this code, you get the message box with all the items in the array that was specified using the ReDim keyword, then you get another message box showing the updated number of items in the array.

The result is:

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!