Array Function in Excel VBA

 

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)

Result:

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.

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Data Analysis in Excel and Excel VBA.

3/5 Completed! Learn much more about arrays ➝
Go to Next Chapter: Function and Sub