Creating Local Arrays in Excel VBA

Local arrays are arrays that are stored in Excel but are not necessarily seen in the worksheet. We will now look at two ways in which we can create local arrays in Excel using VBA. These two ways are

Hardcoded by the developer
Inputted by the user

Hardcoded

Below is an image of what we want to create.

The numbers in green are for labeling the rows and columns. What is really going into VBA is the numbers in black. For example row 1 column 1 contains the number 10, row 1 column 2 contains 12, etc. To do this we need to create a subroutine that makes an array. Below is the code

Option Base 1
Sub newArray()
Dim myArray(2,2) As Integer
myArray(1,1) = 1
myArray(1,2) = 2
myArray(2,1) = 3
myArray(2,2) = 4
MsgBox myArray(1,1)

Here is what we did

Line 1 we set an option called Options Base 1. If we do not o this, VBA adds an extra column to the array called 0. THis is not wrong but can cause problem later if you are not aware this can happen.
Line 2-3 we intitate the sub routine and Dim the array with a 2×2 strcuture
Line 4-6 are used to set each cell in the array to a specific value.
Line 7 outputs the value in Row 1 column 1 as proof the subroutie worked

Below is the output

The one in the message box is from row 1 column 1 of the array. The output below is from the locals window in the developer.

This image provides evidence that the array works. You can see that each position of the array has a stored value.

User Inputted Array

It is also possible for users to input values into an array. Doing this requires input boxes and the use of nested for loops, both of these were discussed in a prior post. Below is the code.

Option Base 1
Sub makeArray()
Dim myArray(2,2) As Integer
Dim i As Integer, j As Integer
For i = 1 To 2
	For j = 1 To 2
		myArray(i,j)=InputBox("Enter number for element " & i & "," & j)
		Next j
	next i
End Sub

The first 4 lines are the same as the last code. Line 5 is where the for loop begins. The purpose of the for loop is to go across each column then skip down to the next row. The input box asks the user for a number as shown below