Create Arrays in VBA
As we know array can hold multiple items. In VBA we can create One dimensional arrays, Multidimensional arrays, and Dynamic arrays. In this article we discuss about how to create arrays in Microsoft Office Excel by using VBA.
One Dimensional Arrays:
Let’s create single dimensional array as shown below on button click. Open Microsoft Office Excel 2013 and add one command button and write below code on its click event.
Private Sub CommandButton1_Click()
Dim myArray(2)
myArray(0) = 10
myArray(1) = 20
myArray(2) = 30
End Sub
In the above code we are creating the one dimensional array myArray with length of 3, it is because arrays index starts from 0. If you want to make arrays starts from 1, use Option Base as shown below.
Option Base 1
Dim myArray(2)
myArray(0) = 10
myArray(1) = 20
myArray(2) = 30
One dimensional arrays has only one row with multiple columns.
Multidimensional Arrays:
If you want to create multiple rows with arrays, we have to use Multidimensional arrays. Create Multidimensional arrays with four rows and three columns as shown below.
Private Sub CommandButton1_Click()
Dim myArray(3, 2)
myArray(0, 0) = 1001
myArray(0, 1) = "John"
myArray(0, 2) = "US"
myArray(1, 0) = 1002
myArray(1, 1) = "Mark"
myArray(1, 2) = "US"
myArray(2, 0) = 1003
myArray(2, 1) = "Bob"
myArray(2, 2) = "US"
myArray(3, 0) = 1004
myArray(3, 1) = "Smith"
myArray(3, 2) = "US"
Dim i As Integer
With Worksheets("Sheet1")
For i = 1 To UBound(myArray)
.Cells(1, i).Value = myArray(0, i - 1)
.Cells(2, i).Value = myArray(1, i - 1)
.Cells(3, i).Value = myArray(2, i - 1)
.Cells(4, i).Value = myArray(3, i - 1)
Next i
End With
End Sub
As shown above we created the Multidimensional Array and assigning its values to excel worksheet sheet1. Once you click on the button the output displays as below.
We can create the array with worksheet value with the help of Variant type. Let’s take above example and create array as shown below.
Private Sub CommandButton1_Click()
Dim myArray As Variant
myArray = Worksheets("Sheet1").Range("A1:C4")
MsgBox (UBound(myArray))
End Sub
The above code multidimensional arrays based Sheet1 A1 to C4 cells with the four rows and 3 columns.
Dynamic Arrays:
Sometimes we don’t know which length of array you have to create. In this type of situations we can create the dynamic arrays for which length varies.
Private Sub CommandButton1_Click()
Dim myArray() As String
Dim iNumSheets As Integer, iCount As Integer
iNumSheets = ActiveWorkbook.Worksheets.Count
ReDim myArray(0 To iNumSheets - 1)
For iCount = 1 To iNumSheets
myArray(iCount - 1) = ActiveWorkbook.Sheets(iCount).Name
Next iCount
MsgBox ("Number Of Worksheets : " & (UBound(myArray) + 1))
End Sub
The above code creating the dynamic array myArray based on number of worksheets in the active excel workbook by using ReDim option. By using ReDim option we can resize the size of the array based on our requirement.
