Learn about VBA Array Function

In This Article we will learn

1. Intro about Array function
2. VBA code use of simple array
3. VBA code for using mixed data
4. VBA Code for using Worksheet Data
5. VBA Code Using loop in Array

In VBA (Visual Basic for Applications), the Array function is used to create and initialize a one-dimensional array. It provides a quick way to store and manage multiple values in a variable.

Syntax:

Array(arg1, arg2, ..., argN)

Parameters:

• arg1, arg2, …, argN: These are the elements you want to include in the array. They can be of different data types, such as integers, strings, or objects

Characteristics:

1. The array created using the Array function is zero-based (index starts at 0).
2. The data type of the array is Variant by default.

Example1: Simple use in VBA Array Function

VBA Code:

Sub ExampleArray()

Dim myArray As Variant

myArray = Array("Apple", "Banana", "Cherry")




' Accessing elements

MsgBox myArray(0) ' Outputs: Apple

MsgBox myArray(1) ' Outputs: Banana

MsgBox myArray(2) ' Outputs: Cherry




' Looping through the array

Dim i As Integer

For i = LBound(myArray) To UBound(myArray)

Debug.Print myArray(i) ' Prints each element in the Immediate window

Next i

End Sub

Mixed Data Type in VBA Array Function

Example2:

VBA Code:

Sub Example2()

Dim mixedArray As Variant

mixedArray = Array("John", 25, True) ' An array with mixed data types

MsgBox "Name: " & mixedArray(0) & vbCrLf & _

"Age: " & mixedArray(1) & vbCrLf & _

"Status: " & mixedArray(2) ' Combines elements in a message box

End Sub

Using with Worksheet Data in VBA Array Function

Example3:

VBA Code:

Sub eample3()

Dim myarray As Variant

Dim i As Integer

myarray = Array(ActiveSheet.Range("a1").Value, _

ActiveSheet.Range("a2").Value, _

ActiveSheet.Range("a3").Value)

For i = LBound(myarray) To UBound(myarray)

MsgBox "Value in Array :" & myarray(i)

Next i

End Sub

Key Functions with Arrays:

• LBound(array): Returns the lower bound of the array.
• UBound(array): Returns the upper bound of the array.

Storing Fixed Lists of Data in VBA Array Function :

Example4:

Sub example4()

Dim item As Variant

Dim colors As Variant

colors = Array("Red", "Blue", "Green")

For Each item In colors

MsgBox item

Next item

End Sub

Looping Through Fixed Values in VBA Array Function :

Example 5

VBA Code

Sub example5()

Dim values As Variant

Dim item As Variant

values = Array(5, 10, 15, 20)

For Each item In values

MsgBox item

Next item

End Sub

Excel File for Download

Limitations

1. Cannot Change Dimensions After Creation:
   Use ReDim for dynamic arrays instead.
2. Performance in Large Data Sets:
   For large datasets, consider other methods like arrays populated from worksheet ranges or dictionaries.

Other related Article to Read

1.VBA Option Explicit : The Option Explicit statement in VBA is used to enforce variable declaration in your code. When enabled, you must explicitly declare all variables using the Dim, Private, Public, or Static keywords before using them

2. VBA Split Function : The Split function in VBA is used to break a string into an array of smaller strings, based on a specified delimiter (e.g., a comma, space, or any other character).

3. VBA LEFT Function : The Left function in VBA is used to extract a specified number of characters from the start (left side) of a string. It is useful when you need to work with a substring or truncate data from the beginning of a string.

4. VBA For Loop : A For Loop in VBA (Visual Basic for Applications) is used to repeat a block of code a specified number of times. There are two main types of For loops in VBA: