Home MS Excel TutorialExcel Array Function in Excel VBA

Array Function in Excel VBA

by Easy Excel Tutorials

Array Function


The Array function in Excel VBA can be used to quickly and easily initialize an array. Place a command button on your worksheet and add the following code lines:

1. First, create a variable named departments of type Variant.

Dim departments As Variant

2. Use the Array Function to assign an array to the variable departments. Add the following code line:

departments = Array(“Sales”, “Production”, “Logistics”)

3. To show the element with index 1, add the following code line:

MsgBox departments(1)


Array Function Result

By default, the element’s index of the array starts from 0.

4. Add Option Base 1 to the General Declarations section if you want the index to start from 1.

Add Option Base 1

Result when you click the command button again.

Array Function Result with Option Base 1

For a practical example of the Array function, see our example program Month Names.

You may also like