What is a VBA array in Excel and how do you program one?

VBA has been part of the Microsoft Office suite for many years. While it doesn’t have the full functionality and power of a full VB application, VBA gives Office users the flexibility to integrate Office products and automate the work you do in them.

One of the most powerful tools available in VBA is the ability to load a whole set of data into a single variable called an array. Loading data in this way allows you to manipulate that range of data or perform calculations in various ways.

So what is a VBA array? In this article, we’ll answer that question and show you how to use one in your own VBA script.

What is a VBA array?

Using a VBA array in Excel is very simple, but understanding the concept of arrays can be a bit complicated if you’ve never used one.

Think of an array as a box with sections in it. A one-dimensional array is a box with one line of sections. A two-dimensional array is a two-line box of sections.

You can place data in any section of this “box” in any order you like.

At the beginning of your VBA script, you need to define this “box” by defining your VBA array. So to create an array that can hold one set of data (a one-dimensional array), you would write the following line.

Dim arrMyArray(1 To 6) As String

Later in your program, you can place data in any section of this array by referring to the section in parentheses.

arrMyArray(1) = "Ryan Dube"

You can create a two-dimensional array using the following rule.

Dim arrMyArray(1 To 6,1 to 2) As Integer

The first number represents the row and the second the column. So the above array can contain a range with 6 rows and 2 columns.

Here’s how to load each element of this array with data.

arrMyArray(1,2) = 3

This loads a 3 into cell B1.

An array can contain any type of data as a regular variable, such as strings, boolean, integers, floats, and more.

The number in parentheses can also be a variable. Programmers commonly use a For Loop to count through all the sections of an array and load data cells in the spreadsheet into the array. You can read how to do this later in this article.

How to program a VBA array in Excel

Let’s look at a simple program in which you want to load information from a spreadsheet into a multidimensional array.

As an example, let’s look at a product sales spreadsheet, where you want to get the seller’s name, item, and total sales from the spreadsheet.

Note that when you reference rows or columns in VBA, you are counting rows and columns from the top left corner by 1. So the rep column is 3, the item column is 4, and the total column is 7.

To load these three columns across all 11 rows, you need to write the following script.

Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer
For i = 2 To 12
For j = 1 To 3
arrMyArray(i-1, j) = Cells(i, j).Value
Next j
Next i

You will notice that to skip the header row, the row number in the first For look must start at 2 instead of 1. This means that you need to subtract 1 from the array row value when you load the cell value into the array using Cells ( i, j). Where the.

Where to insert your VBA array script

In order for a program to put a VBA script in Excel, you need to use the VBA editor. You can access it by selecting the Developer menu and select View code in the Controls section of the ribbon.

If you don’t see the developer in the menu, add them. To do this, select File and Options to open the Excel Options window.

Change the dialing commands from the drop-down list to All assignments† Select Developer in the left menu and select the To add to move it to the right pane. Check the box to enable it and select Okay to finish.

When the Code Editor window opens, make sure the sheet that contains your data is selected in the left pane. Select worksheet in the left drop-down list and Activate right. This creates a new subroutine called Worksheet_Activate().

This function is executed when the spreadsheet file is opened. Paste the code into the script window in this subroutine.

This script works through the 12 rows and loads the rep’s name from column 3, the item from column 4, and the total sale from column 7.

Once both For loops are completed, the two-dimensional array arrMyArray will contain all the data you specified from the original sheet.

Manipulating Arrays in Excel VBA

Suppose you want to apply 5% sales tax to all final sales prices and then write all the data on a new sheet.

You can do this by adding another For loop after the first, with a command to write the results to a new sheet.

For k = 2 To 12
Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

This will “unload” the entire array into Sheet2, with an additional row containing the total multiplied by 5% for the tax amount.

The resulting sheet will look like this.

As you can see, VBA arrays in Excel are extremely useful and just as versatile as any other Excel trick.

The above example is a very simple use for an array. You can create much larger arrays and perform sorting, averaging, or many other functions based on the data you store in them.

If you really want to get creative, you can even create two arrays with a set of cells from two different sheets and do calculations between elements of each array.

The applications are only limited by your imagination.

Leave a Reply

Your email address will not be published.