get-digital-help.com

The ARRAY function creates a Variant variable containing array values.

Sub Macro1()

MyArray = Array("Cat", "Dog", "Rabbit")

For i = LBound(MyArray) To UBound(MyArray)
txt = txt & MyArray(i) & vbNewLine
Next i

MsgBox txt

End Sub

The macro above populates variable MyArray with values Cat, Dog and Rabbit.

A FOR ... NEXT statement loops through the array variable using the Lbound and Ubound function to determine the lower and upper limit of the array.

An ampersand then concatenates the values and lastly, the msgbox shows the concatenated string.

Excel Function VBA Syntax

Array(arglist)

Arguments

Comments

The following macro demonstrates another way to create an array variable. [arglist] means evaluate.

Sub Macro2()

MyArray = [{"Cat", 5, "Rabbit"}]

For i = LBound(MyArray) To UBound(MyArray)
    txt = txt & MyArray(i) & vbNewLine
Next i

MsgBox txt

End Sub

This macro creates array variable MyArray with values in two dimensions.

Sub Macro3()

MyArray = Array(Array("Cat", "Dog"), Array("Rabbit", "Squirrel"))

For r = 0 To 1
    For c = 0 To 1
        txt = txt & MyArray(r)(c) & " "
    Next c
    txt = txt & vbNewLine
Next r

MsgBox txt

End Sub

This macro populates array variable MyArray with values in two dimensions using the evaluate characters.

Sub Macro4()

MyArray = [{"Cat", "Dog"; "Rabbit", "Squirrel"}]

For r = LBound(MyArray, 1) To UBound(MyArray, 1)
    For c = LBound(MyArray, 2) To UBound(MyArray, 2)
        txt = txt & MyArray(r, c) & vbNewLine
    Next c
Next r

MsgBox txt

End Sub

---

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.