How to Create an Array in Excel VBA (4 Ideal Methods)

The array is a very effective command in excel VBA while working with multiple values. It helps to lessen time consumption and clarify information about the dataset. In this article, we will learn how to create an array in excel VBA with 4 ideal methods.

Download Workbook

Get the sample file to try it yourself.

What Is Array in Excel VBA?

In excel, arrays are evaluated as variables that can hold multiple values at the same time. It helps to store long string data based on a single variable which helps to determine values further needed. It is a way more easy process than creating separate variables which contain similar types of information.

4 Ideal Methods to Create an Array in Excel VBA

Let us take a sample dataset for illustration. Here the dataset shows the information of obtained marks of 5 students of a school.

Now, we will create an array out of this dataset with Excel VBA.

1. Create One Dimensional Array with VBA in Excel

Here, we will create a one-dimensional array in excel with VBA. It will give information from the first string referred to as a single dimension. Follow the procedure below:

• First, go to the Developer tab and click on Insert.
• Here, select Command Button from its drop-down section.

• Afterward, you will see a CommandButton1 appear on your worksheet.

• Now, right-click on it and select View Code.

• Following, you will get a new Visual Basic window.
• Insert this code on the page:

Private Sub CommandButton1_Click()
Dim Names(1 To 5) As String

Names(1) = "Anna"
Names(2) = "Tom"
Names(3) = "Miranda"
Names(4) = "Karen"
Names(5) = "George"

MsgBox Names(3)
End Sub

• After that, save this code and close the window.
• Next, click on CommandButton1 and you will see a message box with the output.

Here, as we gave the required output Name (3), it is showing the result of the 3rd name from the dataset. You can change the value according to your preference.

Read More: How to Split a String into an Array in VBA (3 Ways)

2. Apply VBA to Generate Two Dimensional Array

In this section, we will create a two-dimensional array in VBA. It will give output from the second string based on the first one. Let’s see how it works:

• First, follow the process above and insert a Command Button with your dataset.

• Then, right-click on it and select View Code.

• Now, insert this code on the page.

Private Sub CommandButton1_Click()
Dim Names(5 To 9, 1 To 3) As String
Dim t As Integer, g As Integer

For t = 5 To 9
    For g = 1 To 3
        Names(t, g) = Cells(t, g).Value
    Next g
Next t

MsgBox Names(7, 3)
End Sub

• After that, save this code and close the window.
• Now, click on CommandButton1 and you will see a message box with the output.

Sub Two_Dimension()

Dim TwoDim(1 To 2, 1 To 2) As Long
Dim t As Integer
Dim g As Integer

TwoDim(1, 2) = n
TwoDim(2, 1) = n
TwoDim(1, 1) = n
TwoDim(2, 2) = n

For t = 1 To 2
    For g = 1 To 2
        Cells(t, g) = TwoDim(t, g)
    Next  t
Next  g

End Sub

Sub Multi_Dimension()

Dim MultiDim(1 To 3, 1 To 2) As Long
Dim t As Integer
Dim g As Integer

MultiDim(1, 2) = n
MultiDim(2, 1) = n
MultiDim(1, 1) = n
MultiDim(2, 2) = n
MultiDim(3, 1) = n
MultiDim(3, 2) = n

For t = 1 To 3
For g = 1 To 2
Cells(t, g) = MultiDim(t, g)
Next t
Next g

End Sub

Similar Readings

• Excel VBA: Determine Number of Elements in Array (4 Examples)
• How to Name a Table Array in Excel (With Easy Steps)
• Excel VBA to Read CSV File into Array (4 Ideal Examples)
• Excel VBA: Remove Duplicates from an Array (2 Examples)
• VBA to Get Unique Values from Column into Array in Excel (3 Criteria)

3. Create Static Array in VBA

In this section, let us create a static array in excel VBA. In a static array, the length of the string is predetermined. Follow the steps below:

• In the beginning, go to the Developer tab and select Visual Basic.

• Then, select Module under the Insert section.

• Following, insert this code on the blank page.

Sub Static_Array()

Dim AryType(5 To 9) As Variant

AryType(5) = "Anna"
AryType(6) = "Tom"
AryType(7) = "Miranda"
AryType(8) = "Karen"
AryType(9) = "George"

Cells(5, 2).Value = AryType(5)
Cells(6, 2).Value = AryType(6)
Cells(7, 2).Value = AryType(7)
Cells(8, 2).Value = AryType(8)
Cells(9, 2).Value = AryType(9)

End Sub

• After that, click on the Run Sub button or press F5 on your keyboard.

• Next, click on Run on the Macros window.

• Finally, you will get the names in the cell range B5:B9.

• Follow the same process to open a new Module.
• Now, insert this code and Run it.

Sub Static_Array()

Dim AryType(5 To 9) As Integer

AryType(5) = 85
AryType(6) = 78
AryType(7) = 89
AryType(8) = 92
AryType(9) = 67

Cells(5, 3).Value = AryType(5)
Cells(6, 3).Value = AryType(6)
Cells(7, 3).Value = AryType(7)
Cells(8, 3).Value = AryType(8)
Cells(9, 3).Value = AryType(9)

End Sub

• Therefore, here is the final output.

Read More: How to Convert Range to Array in Excel VBA (3 Ways)

4. VBA to Make a Dynamic Array in Excel

In the case of a dynamic array, the length of the string is not determined. Here is the process to create a dynamic array in excel VBA:

• First, follow the process above to open a new Module.
• Now, insert this code on the blank page.

Sub Dynamic_Array()

Dim AryType() As Variant

ReDim AryType(10)

AryType(1) = "Anna"
AryType(2) = "85"
AryType(3) = "Tom"
AryType(4) = "78"
AryType(5) = "Miranda"
AryType(6) = "89"
AryType(7) = "Karen"
AryType(8) = "92"
AryType(9) = "George"
AryType(10) = "67"

Cells(5, 2).Value = AryType(1)
Cells(5, 3).Value = AryType(2)
Cells(6, 2).Value = AryType(3)
Cells(6, 3).Value = AryType(4)
Cells(7, 2).Value = AryType(5)
Cells(7, 3).Value = AryType(6)
Cells(8, 2).Value = AryType(7)
Cells(8, 3).Value = AryType(8)
Cells(9, 2).Value = AryType(9)
Cells(9, 3).Value = AryType(10)

End Sub

Here, we addressed the data as a Variant and did not determine any length of a string. Therefore, we used the ReDim function to assign the length after declaring the variable

• Following, Run this code.
• Finally, you will get all the values at once like this:

Read More: How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)

Things to Remember

• An array counts the values from 0, not from 1.
• Make sure to begin the array from the cell (0,0) which means the first row and first column.
• In the case of Dynamic Array, you must use the ReDim function. Otherwise, it will not show any results.

Conclusion

I hope it was a helpful article for you on how to create an array in Excel VBA with 4 ideal methods. Let us know your feedback in the comment box. Follow ExcelDemy for more excel related blogs.

Related Articles

• Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)
• Excel VBA to Create Data Validation List from Array
• How to Sort Array with Excel VBA (Both Ascending and Descending Order)
• Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
• VBA to Transpose Array in Excel (3 Methods)