Arrays are simply the use of multiple rows and columns in Excel. Generally, this happens through selecting them. The benefit of arrays is that you can perform calculations on several rows and columns of data at once. Below is a picture of an array.

The Purpose

Our purpose is going to be to take an array and multiple all the values by 3. In order to do this, we will have to use nested for loops and learn some terminology.

The for loops are difficult to master if you are new to coding. However, what we need is for VBA to loop through all the rows and loop through all the columns completing the calculation we want. This is commonly done by labeling the rows as the letter “i” in coding and columns as the letter “j”. Below is a picture of what I have just said.

For the actually coding here are the variables we need to declare in our subroutine

• i for rows
• j for columns
• nr for number of rows
• nc for number of columns

We need to count the rows and columns so the for loop knows when to stop.

Below are the functions we will use

• A function that counts the rows
• A function that counts the columns
• A function that selects the cells
• Multiply by 3 the selected cells

Below is the actual code

Sub timesThree()
Dim i As Integer, j As Integer, nr As Integer, nc As Integer
nr = Selection.Rows.Count
nc = Selection.Columns.Count
For i = 1 To nr
    For j = 1 To nc
    Selection.Cells(i, j) = Selection.Cells(i, j) * 3
    Next j
Next i
End Sub

Here is what the code does

1. Line one gives the subroutine a name
2. Line 2 declares the variables
3. Line 3 and 4 tell VBA to count the number of rows and columns
4. Line 5-10 is the for loop. i and j are set to 1 initially and continue until whatever value nr and nc have. Whatever position the selected cell is at is multplied by three.

Below is a picture of the before and after effects of using this code on an array.

In the picture above you can see the array is 3×3 and selected. The code is to the right. In the bottom right you have the locals window which will provide information on the variables that are created. Below is the output

Here you can see that all the values in the array have been multiplied by three as planned. The highlighted yellow in the code indicates that we are at the end of the code and are ending the subroutine. The information in the locals window is hard to read but simply shares information on the values in the various variables that were created.

Conclusion

With this information, it is possible to create arrays and manipulate them in Excel VBA. As already mentioned, this information must be used in combination with other techniques to truly be useful for the average developer.