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
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
If you put the same numbers into the input box as in the original array you will see the following in the locals window.
Local arrays can be useful when combined with other ideas in Excel VBA. This will be covered in a future post