Excel VBA Array: How to Use Arrays in Excel

Kasia Mikoluk

Excel VBA (Visual Basic for Applications) is where spreadsheets meet programming. Although not as complex or powerful as the average programming language, Excel VBA can get very difficult to grasp for all but the most determined of learners. At the same time, the applications and capabilities of VBA are enormous. If you want to truly master Excel, learning VBA is a must. This course on advanced Excel will help you get started.

In this tutorial, we will learn more about one of Excel’s most powerful features, the VBA array, and how to use it in our spreadsheets.

What is an Array?

The dictionary meaning of array is “an ordered arrangement”. In programming and Excel, the meaning is quite similar, except that an array here refers to an “ordered arrangement of data”.

Arrays are primarily used to group or classify data of similar type. In function, it’s similar to a variable, except that a variable can only hold a single item, while an array can hold multiple items.

For example, if I have a list of animals that I want to assign variables to, I can do the following:

A = “Horse”

B = “Dog”

C = “Cat”

D = “Cow”

E = “Duck”

This is just long and tedious. Instead, I can use an array like this:

animals = array(“Horse”, “Dog”, “Cat”, Cow”, “Duck”)

So instead of declaring five separate variables, we declared only one array that could hold all five of our items.

The best part? We can refer or extract any particular item whenever we want. This makes arrays especially powerful for programming.

Arrays in Excel

Arrays are the tool power users turn to when built-in Excel functions fail them. Arrays can be used to perform tasks seemingly impossible to undertake using ordinary formulas. They might sound complicated, but once you get the hang of them, you’ll use them in all your spreadsheets.

Like variables, arrays in VBA are declared using Dim. Besides the array name, you also need to specify the number and type of values the array will store.

The full syntax looks like this:

Dim ExampleArray(6) As String

Where:

Dim = Command for declaring variables/arrays

ExampleArray = Array name

(6) = Number of values to be stored in the array*

As String = The type of data stored in the array

* In VBA, as in most programming languages, count starts from 0. Hence, (6) actually means that there are 7 stored values (from 0 to 6). If you want to count from 1 instead, you would write (1 to 6), like this:

Dim MyArray(1 to 6) As String

Learn more about arrays in Excel in this advanced online training course for Excel 2013.

How to Use Excel VBA Array

The best way to understand how arrays work in Excel is to create one ourselves.

Step 1: Enable Developer Tab

The first step is to enable the Developer tab in Excel. This will enable us to create formulas and macros.

Go to File -> Options -> Customize Ribbon.
In the Main Tabs, make sure that Developer is selected.

Step 2: Enable Macros

Before we can start creating our array, we will need to enable macros (which are disabled by default for security purposes).

Go to File -> Options -> Trust Center
Click on Trust Center Settings

In the window that pops up, click on Macro Settings
Select Enable all macros

Step 3: Create a Button

Before writing our little VBA program, we will first create a button that can run it.

Open the Developer tab and click on ‘Insert’
Select ‘Button’ under ‘Form Control’ as shown:

Click and drag anywhere in the worksheet to create your button.

The Assign Macro dialog box will pop up. Here, you can give a custom name to your button, or you can leave it as is. Once you’ve selected the name, click on New

The Microsoft Visual Basic for Applications window will pop open. You can also access it by press ALT + F11.
In the main code editor window, type in the following program, right after ‘Sub Button1_Click()’:

Dim CustomerName(1 to 10) As String
For i = 1 to 10
CustomerName(i) = InputBox(“Please Enter the Customer Name”)
Cells(i, 1) = CustomerName(i)
Next

Save the program by clicking on the save icon. Enter a name in the save dialog box and make sure to choose Excel Macro-Enabled Workbook

Your program is now associated with the button. Pressing the button will trigger the VBA program

New to Excel? This fast track course on Excel will help you get started.

Step 4: Run the Program

Close the VBA window (ALT + F11) after saving the program. You will now see your original worksheet with the button.

Click the button (if the button is not enabled, try clicking on any cell to deselect it before clicking). A prompt asking you to enter “Please Enter Customer Name” (or whatever else you wrote in the program) will pop-up.

Since we set our range in the formula from 1 to 10, the prompt will ask for our input 10 times. Whatever values you enter in the dialog box will automatically fill up the first column:

That’ it! You’ve successfully created a VBA macro using an array!

Breaking it Down: Understanding the VBA Array Formula

Before we leave, let’s take another look at the VBA array formula we used above:

Sub Button1_Click()
Dim CustomerName(1 To 10) As String
For i = 1 To 10
CustomerName(i) = InputBox("Please Enter the Customer Name")
Cells(i, 1) = CustomerName(i)
Next
End Sub

Let’s try to understand it a little better:

Sub Button1_Click()

This command is basically used to refer to the button we created in our workbook. Button1 is the name of our button; Click() is the action that triggers it.

Dim CustomerName(1 to 10) As String

This is our actual array. Here:

Dim = Command used to assign variables and arrays

CustomerName = Name of array

(1 to 10) = The number of values stored in the array.

As String = This tells Excel that the values to be stored are String, not numbers (numbers would be Integer)

Moving on

For i = 1 to 10

This is a VBA for loop. It tells VBA to go through the values 1 to 10 sequentially.

CustomerName(i) = InputBox("Please Enter the Customer Name")

Here, CustomerName(i) cycles through i (from 1 to 10), assigning each the value entered in the InputBox.

Cells(i, 1) = CustomerName(i)

This tells Excel to enter the values of i accepted in the previous line in the first column. If you wanted to enter them in the second column, you would write Cells(i, 2)

Next

Every For loop must end with a Next command.

End Sub

This signals that the program is over.

Now that you know how to use an array, why not take a course like Advanced Excel Training that will introduce you to more advanced Excel concepts, including how to make use of VBA arrays.