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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create an Array with VBA in Excel


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 much easier process than creating separate variables that contain similar types of information.


How to Create an Array in Excel VBA: 4 Ideal Methods

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

How to Create an Array in Excel VBA


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.

Create One Dimensional Array with VBA in Excel

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

Create One Dimensional Array with VBA in Excel

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

Create One Dimensional Array with VBA in Excel

  • 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

Create One Dimensional Array with VBA in Excel

  • 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.

Note: Make sure you enable the Design Mode from the Developer tab while running the code.

Read More: How to Declare Array in Excel VBA


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.

Apply Excel VBA to Generate Two Dimensional Array

  • 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

Apply Excel VBA to Generate Two Dimensional Array

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

Note: If you are working with only numeric values then you can apply this code in the Visual Basic window. Here, n = numeric value for each cell and active cells are 2 X 2.

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

Additional Tip: In case your active cells are 2 X 3 and the values are numeric, apply this code. You can add more cells to this method.

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

Read More: Excel VBA Array of Arrays


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.

Create Static Array in VBA

  • Then, select Module under the Insert section.

Create Static Array in VBA

  • 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

Create Static Array in VBA

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

  • Next, click on Run on the Macros window.

Create Static Array in VBA

  • 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

Create Static Array in VBA

  • Therefore, here is the final output.

Read More: How to Define VBA Global Array in Excel


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

Make a Dynamic Array in Excel VBA

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:


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.

Download Workbook

Get the sample file to try it yourself.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo