VBA ARRAY Function

In any programming language, Arrays are used to collect the group of similar objects together. The same concept applies to VBA. In VBA, Arrays are mostly used to store more than one value in the variable.

VBA contains nine different array functions. This tutorial will cover detailed information regarding VBA Array Function, including its definition, syntax, parameters, return type, and different examples explaining how to write a macro code using the VBA ARRAY function.

What is VBA Array Function?

"The VBA Array function returns a variant containing an array of similar datatype. This function is used to quickly and easily initialize an array in your macro module. The VBA Array function is commonly used to store more than one value in the variable."

The VBA Array Function creates a Variant VBA Array from a list of comma-delimited elements. In case no arguments are provided, an array of zero-length is created.

Syntax

Parameters

Arglist(required): This parameter represents a comma-delimited list of elements that are specified to the values of the array contained within the Variant. If no arguments are passed, an array of zero length is created.

Return

The VBA Array function returns a variant containing an array.

Things to Remember

Using the Array Function you can store two types of Arrays i.e., two-dimensional array & multi-dimensional array.
The positioning of an Array starts from 0 instead of 1. Arr(0) represents the the first row and the first column.

Examples:

#VBA ARRAY Example 1: Store String Values using VBA Array Function

Follow the step-by-step guidance to store String values using the VBA Array Function:

Step 1: Go to your Excel workbook. Open the Visual Basic window either by clicking on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.

Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.

Step 3: A module will be inserted. We will write our macro in this module window only. Start the program by introducing the name of your program followed by the declaration of the variable.

Refer to the below given macro code:

Sub VBA_Array_Example1()
    'Declaring the variable to store our array
    Dim arr As Variant
End Sub

Step 4: Next, we will call the Array function, and, in its parameter, we will pass three string elements. Later own we will print the arr(0), aar(1) and arr(2) using MsgBox.

We have used the UBound and LBound function to calculate the Array length in this code.

Refer to the below given macro code:

Sub VBA_Array_Example1()
    'Declaring the variable to store our array
    Dim arr As Variant
    'Using the Array function to store elements of variant data type
    arr = Array("VBA", "Programming", "Lanaguage")
    'Print all elements of the array
    MsgBox arr(0) & " " & arr(1) & " " & arr(2) 'Result: John
    'Printing the array length of our array
    MsgBox "Array Length: " & UBound(arr) - LBound(arr) + 1 'Result: 3
End Sub

Output

Run the macro either by clicking on the Run option from the VBA ribbon toolbar or pressing the F5 key. You will notice Excel will immediately display the MsgBox.

Click on OK and VBA will open another MsgBox displaying the array size.

#VBA Array Example 2 - Array Function inside Another Array Function

Follow the step-by-step guidance to store String values using the VBA Array Function:

Step 1: Go to your Excel workbook. Open the Visual Basic window either by clicking on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.

Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.

Step 3: A module will be inserted. We will write our macro in this module window only. Start the program by introducing the name of your program followed by the declaration of the variable.

Refer to the below given macro code:

Sub VBA__Array_Example2()
    'Declaring the variable to store our array
    Dim arr As Variant
End Sub

Step 4: Next, we will call the Array function, and, in its parameter, we will pass three string elements. Later inside the same array function, we will call another array function and pass its parameters.

Refer to the below given macro code:

Sub VBA__Array_Example2()
    'Declaring the variable to store our array
    Dim arr As Variant
    'Calling an Array function inside another array function
    arr = Array(Array(20, 40, 60), Array(10, 20, 30))
    'display the output
    MsgBox arr(0)(1) 'Output: 40
    MsgBox arr(1)(0) 'Output: 10
End Sub

Output

Run the macro either by clicking on the Run option from the VBA ribbon toolbar or pressing the F5 key. You will notice Excel will immediately display the MsgBox.

Click on OK and VBA will open another MsgBox displaying another output

#VBA Array Example 3 - Create a customised Month Function using inbuilt Array Function.

In the above two examples, we have seen how to work Arrays Function, their syntax, how to add elements, and how to get them printed. Now we will see how to create a customized VBA Function using the Array function.

Follow the step-by-step guidance to store String values using the VBA Array Function:

Step 1: Go to your Excel workbook. Open the Visual Basic window either by clicking on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.

Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.

Step 3: A module will be inserted. We will write our macro in this module window only. Start the program with the Function keyword followed by the function name.

Refer to the below given macro code:

Function Months_List()
'Calling the array function 
 Months_List = Array("January", "Febuary", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
End Function

Step 4: Once done, save your macro and close. Go to the your Excel worksheet and start typing the name of the function. To your surprise, the new Function will appear with the name of Months_List in your worksheet.

Refer to the below image:

Step 5: Open the function and hit the enter button. The function will return the first-month name, i.e., January. Applying the function for the second time in a new cell will still return only January as an output.

To fetch all the 12 months together, you have to select 12 columns in one row.

Since it's an customised function so, we need to close the formulas as an array formula only.

So hold Ctrl + Shift + Enter. The same formula will be copied to all the selected cells. As a result, the function will fill the list of months in the selected cells.