The ARRAY function creates a Variant variable containing array values.

1. Array Function VBA Syntax

Array(arglist)

Back to top

2. Array Function Arguments

Back to top

3. Array Function example

'Name macro
Sub Macro1()

'Populate array variable
MyArray = Array("Cat", "Dog", "Rabbit")

'For Next statement using lower and upper boundaries of the array variable
For i = LBound(MyArray) To UBound(MyArray)

'Save array value to variable txt and a new line
txt = txt & MyArray(i) & vbNewLine

'Continue with next value
Next i

'Show message box containing contents of variable txt
MsgBox txt

'Exit macro
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.

Back to top

4. Alternative way to populate an array variable

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

'Name macro
Sub Macro2()

'Populate array variable using brackets and curly brackets
MyArray = [{"Cat", 5, "Rabbit"}]

'For Next statement using lower and upper boundaries of the array variable
For i = LBound(MyArray) To UBound(MyArray)

    'Save array value to variable txt and a new line
    txt = txt & MyArray(i) & vbNewLine

'Continue with next value
Next i

'Show message box containing contents of variable txt
MsgBox txt

'Exit macro
End Sub

Back to top

5. Populate a two-dimensional array variable

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

'Name macro
Sub Macro3()

'Populate array variable using two Array functions
MyArray = Array(Array("Cat", "Dog"), Array("Rabbit", "Squirrel"))

'For Next statement for rows
For r = 0 To 1

    'For Next statement for columns
    For c = 0 To 1

        'Save array value to variable txt and a new line
        txt = txt & MyArray(r)(c) & " "
    'Continue with next value
    Next c

    'Add a new line to variable txt
    txt = txt & vbNewLine

'Continue with next value
Next r

'Show message box containing contents of variable txt
MsgBox txt

'Exit macro
End Sub

Back to top

6. Alternative way to populate a two-dimensional array variable

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

'Name macro
Sub Macro4()

'Populate array variable using brackets and curly brackets
MyArray = [{"Cat", "Dog"; "Rabbit", "Squirrel"}]

'For Next statement for rows using lower and upper boundaries
For r = LBound(MyArray, 1) To UBound(MyArray, 1)

    'For Next statement for columns using lower and upper boundaries
    For c = LBound(MyArray, 2) To UBound(MyArray, 2)

        'Save array value to variable txt and a new line
        txt = txt & MyArray(r, c) & vbNewLine

    'Continue with next value
    Next c

'Continue with next value
Next r

'Show message box containing contents of variable txt
MsgBox txt

'Exit macro
End Sub

Back to top

ARRAY function examples

Click a cell to make a column hidden or visible – VBA
What's on this page Press with left mouse button on a specific cell to hide/show entire column Where to put […]

Combine cell ranges ignore blank cells
The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

Copy Excel Table filter criteria programmatically
I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]

Copy worksheets in active workbook to new workbooks
This article demonstrates several VBA macros, they will save you time if you have lots of worksheets. The first macro […]

Create a Print button – macro
This article describes how to create a button and place it on an Excel worksheet, then assign a macro to […]

Hide specific worksheets programmatically
This article demonstrates techniques to hide and unhide worksheets programmatically. The image above shows the Excel window and the worksheet […]

How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]

How to use the ARRAY function [VBA]
The ARRAY function creates a Variant variable containing array values. Table of Contents Array Function VBA Syntax Array Function Arguments […]

How to use the FOR NEXT statement
Table of Contents FOR NEXT statement FOR NEXT with a counter variable FOR each NEXT example FOR NEXT with counter […]

How to use the LBOUND and UBOUND functions
The Lbound and Ubound functions calculate the size of of an array. The Lbound returns the lower limit of an array and […]

How to use the TEXTTOCOLUMNS method
The TextToColumns method puts a cell that contains a string into substrings and distributes them horizontally using a delimiting character […]

List all hyperlinks in worksheet programmatically
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]

Move a shape [VBA]
This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape […]

Multi-level To-Do list template
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]

Sort values in a cell based on a delimiting character
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Excel VBA Reference

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

Excel categories

Comments (2)