VBA ARRAY Function. Arrays are useful to group objects that are the same in any programming language. VBA is based on the same idea. Most of the time, Arrays are used in VBA to store more than one value in a single variable. A total of nine distinct array functions are available in VBA.
The VBA Array function returns a variant with an array of the same data type. The function quickly and easily initialises an array in your macro module. The VBA Array function is often useful to store many values in the variable.
If no arguments are given, a zero-length array is created. You can store either a two-dimensional or a multi-dimensional array by using the Array Function in your programming language. An array’s position starts at 0 instead of 1. The first row and column are represented by Array(0).
Syntax
Array(arglist)
Parameters
Arglist(required): The parameter is a list of elements isolated by commas that are assigned to the values of the array inside the Variant. If you don’t give any arguments, a zero array is made.
See More: VBA Arrays
Example
Use the Array Function in VBA to store string values
Follow the steps below to use the VBA Array Function to store String values:
Step 1: Open up your Excel file. You can open the Visual Basic window by clicking on Developer Window > Visual Basic Editor or pressing Alt + F11.
Step 2: You will see the VB Editor window. Next, you need to do a module. Click “Insert” on the ribbon, then click “Module”.
Step 3: A module will be added. Our macro will be written in this module. Launch the program by giving the programme’s name, followed by “declaring” the variable.
Sub VBA_Array_Function() Dim VarArray() As Variant End Sub
Step 4: Now involve the Array function, passing five strings as input. We will use MsgBox and watch window to display VarArray(0), VarArray(1), VarArray(2), VarArray(3), and VarArray(4) later on.
The Array length was determined with the help of the UBound and LBound functions.
| Sub VBA_Array_Function()
Dim VarArray() As Variant VarArray() = Array(“XL”, “Automation”, “VBA”, “Programming”, “Tutorial”) MsgBox “LBound: ” & LBound(VarArray) & vbNewLine & “UBound: ” & UBound(VarArray) End Sub |
Output
Click the Run button on the VBA toolbar, or hit the F5 key, to activate the macro. Also, you can hit the F9 key, to show watch window.
Click on VarArray() to ‘Add Watch’ winow. You will see that Excel shows the watch window expression right away.
VBA will open MsgBox showing the array’s size.
