How to use Array function in VBA?

Using the Array function in VBA (Visual Basic for Applications) is straightforward and very useful for creating and handling arrays dynamically.

Here’s a basic guide on how to use it:

Creating an Array

To create an array, you simply use the Array function and pass the elements you want to include in the array. For example:

Dim myArray As Variant
myArray = Array("Apple", "Banana", "Cherry")

In this case, myArray is a variant that holds an array of strings.

Accessing Elements

You can access elements of the array using their index. Remember that VBA arrays are zero-based by default, so the first element is at index 0.

Dim firstFruit As String
firstFruit = myArray(0)  ' This will be "Apple"

Modifying Elements

You can modify elements of the array by accessing them by their index and assigning a new value.

myArray(1) = "Blueberry"  ' Changes "Banana" to "Blueberry"

Dynamic Arrays

One of the benefits of using Array function is that it creates a dynamic array. This means you can change the size of the array at runtime.

Multidimensional Arrays

While the Array function creates a one-dimensional array, you can nest arrays to create multidimensional arrays.

Dim multiArray As Variant
multiArray = Array(Array(1, 2, 3), Array("A", "B", "C"))

Iterating Over an Array

You can use a loop to iterate over the elements of the array.

Dim item As Variant
For Each item In myArray
    Debug.Print item
Next item

Array Limits

Note that the Array function is limited by the maximum size of a variant, which depends on your system’s memory.

Type Declaration

While the example uses Variant for simplicity, it’s good practice to declare the specific type of array if you know what type of data it will hold, for example, Dim myArray() As String.

Passing Arrays to Functions

Arrays created with the Array function can be easily passed to and returned from functions.

Using with Excel Ranges

In Excel VBA, the Array function is particularly useful for dealing with ranges and their values.

Remember, the Array function is versatile and can be adapted to many different uses in VBA programming.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *