VBA has been part of the Microsoft Office suite for many years. While VBA does not have all the functionality and power of a full-fledged VB application, it provides Office users with the flexibility to integrate and automate Office products.
One of the most powerful tools available in VBA is the ability to load an entire range of data into a single variable called an array. By loading data in this way, you can manipulate or perform calculations on that data range in various ways.
So what is a VBA array? In this article, we will answer this question and show you how to use it in your own VBA script
What is the VBA matrix?
It is very easy to use a VBA array in Excel, but understanding the concept of arrays can be a little tricky if you’ve never used one.
Think of an array as a box with sections inside. A one-dimensional array is a box with one line of sections. A 2D array is a rectangle with two partition lines.
You can put data in each section of this “window” in any order.
– /
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 dataset (one dimensional array), you have to write the following line.
Dim arrMyArray (1 To 6) As String
Later in your program, you can put data in any section of this array by specifying the section in parentheses.
arrMyArray (1) = “Ryan Dube”
You can create a two dimensional array using the following line.
Dim arrMyArray (1 To 6,1 to 2) As Integer
The first number is a row and the second is a column. So the above array can contain a range of 6 rows and 2 columns.
You can load any element of this array with data as follows.
arrMyArray (1,2) = 3
This will load 3 into cell B1.
An array can contain any data type just like a regular variable, such as strings, booleans, integers, floating point numbers, etc.
The number in brackets can also be a variable. Programmers typically use a For Loop to count all sections of an array and load data cells from a spreadsheet into an array. You will see how to do this later in this article.
How to Program a VBA Array in Excel
Let’s take a look at a simple program where you can load information from a spreadsheet into a multidimensional array.
As an example, consider a product sales table from which you want to retrieve the sales representative’s name, product, and total sales.
Note that in VBA, when you refer to rows or columns, you count the rows and columns starting from the top left corner from 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 in order to skip the header row, the row number in the first For look must start at 2, not 1. This means that you need to subtract 1 for the array row value when you load the cell value. to an array using Cells (i, j) .Value.
Where to insert the VBA array script
To place a VBA program script in Excel, you need to use a VBA editor. You can access it by choosing the Developer menu and choosing View Code under the Controls section of the ribbon.
If you don’t see the developer in the menu, you need to add it. To do this, select “File” and “Options” to open the Excel Options window.
Change the select command from the drop-down list to All Commands. Select “Developer” from the left menu and click the “Add” button to move it to the panel on the right. Check the box to enable it and click OK to finish.
When the code editor window opens, make sure the sheet that your data is on is selected in the left pane. From the left dropdown list select Worksheet and on the right select Activate. This will create a new routine called Worksheet_Activate ().
This function will run whenever the spreadsheet file is opened. Paste the code into the script panel inside this subroutine.
This script will loop through 12 lines and load the representative name from column 3, the item from column 4, and the total sales from column 7.
After both For loops have finished, the arrMyArray two-dimensional array will contain all the data you specified from the original worksheet.
Manipulate Arrays in Excel VBA
Let’s say you want to apply a sales tax of 5% to all final sales prices and then write all the data on a new worksheet.
You can do this by adding another For loop after the first one with a command to write the results to a new sheet.
For k = 2–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 “dumps” the entire array into Sheet2, with an additional line containing the total multiplied by 5% for the tax amount.
As a result, the 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 basic use of an array. You can create much larger arrays and perform sorting, averaging, or many other functions on the data you store in them.
If you want to get creative, you can even create two arrays containing a range of cells from two different sheets and perform calculations between the elements of each array.
Applications are only limited by your imagination.
–