Working with ARRAY VARIABLES (VBA)

This post will teach you how to work with Excel arrays in visual basic for applications (VBA).

Why do you need arrays? Excel is much quicker at executing tasks with arrays than working with values on a worksheet. Arrays exist in your computer's RAM memory while workbooks exist on your hard drive.

Working with data from your computer's RAM memory is much faster.

1. Introduction

What is an array?

An array in VBA is a variable that can store multiple values at once instead of just one. Think of it like a row or column of data in a spreadsheet but stored in memory. An array may have multiple dimensions, for example, a 2-dimensional array has both rows and columns. The greatest advantage of using arrays is the speed you can manipulate values in an array.

What is transposing an array?

To transpose an array means to flip its rows and columns. In a 2-dimensional array:

• Rows become columns
• Columns become rows

1 2 3
4 5 6

becomes

1 4
2 5
3 6

What is a user defined function?

A custom function (also known as a User-Defined Function, UDF) is a function that a user creates in Excel to perform calculations that built-in functions cannot handle. In other words, it extends Excel capabilities beyond the built-in functions.

What is a macro?

Macros are sequences of instructions or actions that can be recorded and replayed in Excel to automate repetitive tasks.

• Automate tasks such as formatting, calculations, data manipulation, or interacting with other workbooks.
• You can record macros using the built-in Excel Macro Recorder, which generates VBA (Visual Basic for Applications) code for the actions you perform in Excel.
• Alternatively, you can write macros manually in the VBA editor for more control and flexibility.
• Macros can be run from the Developer tab, assigned to a button, or triggered through custom VBA code.

What is VBA?

VBA (Visual Basic for Applications) is a programming language developed by Microsoft that is used for automating tasks in Microsoft Office applications such as Excel, Word, and Access. It allows users to create macros, automate repetitive tasks, and enhance Office functionality with custom scripts.

What is the "Developer" tab?

The Developer tab is an advanced feature in Microsoft Excel that provides tools for automation, coding, and customization. It includes:

• VBA (Visual Basic for Applications): Write, record and edit macros.
• ActiveX Controls & Form Controls: Create buttons, check boxes, and interactive elements.
• XML Tools: Work with XML maps and data.
• Add-ins Management: Enable and configure Excel Add-ins.

How to enable the "Developer tab"?

Here is how to enable the "Developer" tab if it is missing on the ribbon:

1. Press with left mouse button on "File" located above the ribbon, a new pane appears.
2. Press with mouse on the "Options" button to access Excel settings.
3. Press with mouse on "Customize Ribbon" and then on the right side press with left mouse button on the checkbox next to the tab "Developer" to enable it.
4. Press with left mouse button on "OK" button to apply changes.

The "Developer" tab is now visible on the ribbon. Here are instructions for Excel 2007, Excel 2010 and Excel 2013

2. Declare an array variable

A variable can store one value, an array variable can store multiple values.
The following line declares pets as a String array variable. The parentheses shows that this is an array variable.

Sub Macro1()
Dim pets() As String
End Sub

You can also specify how many values you want to store in the array. The macro below declares variable pets as a string array, it can store 6 values. 0 to 5.

Sub Macro1()
Dim pets(5) As String
End Sub

If you want to start with 1 instead of 0 you can do that too. This variable can store 5 values, 1 to 5.

Sub Macro1()
Dim pets(1 to 5) As String
End Sub

Back to top

3. Redimensioning an array variable

You can also change the amount of values an array variable can store any time in the macro, using the ReDim statement.

Sub Macro1()
Dim pets(1 to 2) As String

pets(1) = "dog"
Msgbox Ubound(pets)

ReDim pets(1 to 3)

Msgbox pets(1) & " " & Ubound(pets)

End Macro

Note that this clears all previous saved array values.

Back to top

4. n-dimensional arrays

The example arrays shown above have one dimension but it is possible to have up to 60000 dimensions. You are probably going to use one or two dimensions most of the time.

4.1 One-dimensional arrays

You can assign values to an array in your macro or user defined function. The following macro shows you how.

Sub Macro1()
Dim pets(1 to 2) As String
pets(1) = "dog"
pets(2) = "cat"
End Sub

In this macro 2 values can be stored pets(1 to 2). The index number in the pets variable tells excel where to save the value in the array. pets(index)

You can also grab values from a worksheet and save them to an array.

Sub Macro1()
Dim pets(1 to 2) As String

pets(1) = Worksheets("Sheet1").Range("A1").value
pets(2) = Worksheets("Sheet1").Range("A2").value
End Sub

If you have many values this macro might do the work but this method is relatively slow.

Sub Macro1()
Dim pets(1 to 100) As String
For i = 1 to 100
pets(i) = Worksheets("Sheet1").Range("A" & i).value
Next i
End Sub

There is a quicker way to transfer values from a worksheet to an array that I will tell you about later in this post.

Back to top

4.2 Two-dimensional arrays

This macro shows you how to populate a 2-dimensional array. The first argument is the row number and the second is the column number, pets(row, column)

Sub Macro1()
Dim pets(1 to 2, 1 to 2)

pets(1,1) = "dog"
pets(1,2) = 5
pets(2,1) = "cat"
pets(2,2) = 9
End Sub

This picture is a visual presentation of the array variable. The italic numbers show the position of each value in the array.

Back to top

4.3 Three-dimensional arrays

It is also possible to have arrays with three dimensions. The best analogy I can think of is if the first argument is the row number, the second argument is column number, the third argument is then like worksheets.

Sub Macro1()
Dim pets(1 to 2, 1 to 2, 1 to 2)

pets(1,1,1) = "dog"
pets(1,2,1) = 5
pets(2,1,1) = "cat"
pets(2,2,1) = 9
pets(1,1,2) = "snake"
pets(1,2,2) = 3
pets(2,1,2) = "fish"
pets(2,2,2) = 4
End Sub

Back to top

5. Array size - Finding the starting and ending index number of an array

Each element or value has a index number indicating its position in the array. Arrays always have a lower and upper limit, the Lbound statement returns the lower limit and the Ubound statement returns the upper limit. These functions are very useful. If the lower limit is 1 and the upper limit is 5, the array contains 5 values.

Back to top

5.1 One-dimensional array

The following macro uses the LBound and Ubound statements to determine the size of the array variable.

Sub Macro1()
Dim pets(1 To 10)
Msgbox "LBound: " & LBound(pets) & " Ubound: " & Ubound(pets)
End sub

Back to top

5.2 Two-dimensional array

This macro shows you how to figure out the size of a 2 dimensional array variable.

Sub Macro1()
Dim pets(1 To 5, 1 To 3)
MsgBox "Lower limit 1-dim: " & LBound(pets, 1) & " Upper limit 1-dim:" & UBound(pets, 1) & " Lower limit 2-dim: " & LBound(pets, 2) & " Upper limit 2-dim: " & UBound(pets, 2)
End Sub

Back to top

5.3 Three-dimensional array

Sub Macro1()
Dim pets(1 To 5, 1 To 3, 1 To 2)
MsgBox "Lower limit 1-dim: " & LBound(pets, 1) & " Upper limit 1-dim:" & UBound(pets, 1) & " Lower limit 2-dim: " & LBound(pets, 2) & " Upper limit 2-dim: " & UBound(pets, 2) & " Lower limit 3-dim: " & LBound(pets, 3) & " Upper limit 3-dim: " & UBound(pets, 3)
End Sub

Back to top

6. Manipulate an array

You can change dimensions of an array and still keep all values. But you need to declare the array variable with empty parentheses or you will get this compile error: "Array already dimensioned"

Sub Macro1()
Dim pets() As String
ReDim pets(1 To 5)
pets(2) = "dog"
ReDim Preserve pets(1 To UBound(pets) + 1)
MsgBox "Lbound(pets): " & LBound(pets) & " Ubound(pets): " & UBound(pets) & "pets(2): " & pets(2)
End Sub

You can only ReDim the last dimension, the following array has 2 dimensions, the boundaries are 1 to 5 and 1 to 2.

Sub Macro1()
Dim pets() As String
ReDim pets(1 To 5, 1 To 2)
ReDim Preserve pets(1 to Ubound(pets,1) , 1 To UBound(pets,2) + 1)
MsgBox "Lbound(pets,1): " & LBound(pets,1) & " Ubound(pets,1): " & UBound(pets,1) & vbNewLine & "Lbound(pets,2): " & LBound(pets,2) & " Ubound(pets,2): " & UBound(pets,2)
End Sub

The ReDim Preserve statement changes the last dimension the 2-dimensional array in the macro above. The boundaries are now 1 to 5 and 1 to 3, this picture shows you that.

Excel is really slow when it comes to manipulating arrays, it is better to try to minimize the number of times you use ReDim Preserve by guessing how many elements you need.

Back to top

7. Clear an array

You can use the ReDim statement to clear all saved array values.

Sub Macro1()
Dim pets(1 to 2) As String

pets(1) = "dog"
Msgbox Ubound(pets)

ReDim pets(1 to 3)

Msgbox pets(1) & " " & Ubound(pets)

End Macro

You can also use the Erase function

Sub Macro1()

Dim pets(1 to 2) As String

pets(1) = "cat"

Erase pets

End Sub

Back to top

8. Transfer array values to a worksheet

&

8.1 Macro

This macro returns an array to worksheet Sheet1

Sub Macro1()
Dim pets(1 to 2) As String

pets(1) = "dog"
pets(2) = "cat"

Worksheets("Sheet1").Range("A1:B1") = pets

End Macro

As you can see it returns theses values horizontally. It is possible to return values vertically also, se Transpose later in this post.

Back to top

8.2 User defined function

The following user defined function (udf) returns array values but you need to enter it as an array formula.

Function ArrTest
Dim pets(1 to 2) As String

pets(1) = "dog"
pets(2) = "cat"

ArrTest = pets

End Macro

Here are the steps on how to enter an array formula:

1. Select cell A1:B1
2. Type =ArrTest()
3. Press and hold CTRL + SHIFT
4. Press Enter

If you enter the udf correctly the formula bar shows {=ArrTest()}

If you fail to enter this udf as an array formula it might show the first value in the array in all cells, the formula bar shows =ArrTest()

Back to top

9. Transpose an array

A 1-dimensional array is always shown horizontally on worksheet, to change that you can use the Transpose method and rearrange the values vertically.

Sub Macro1()
Dim pets(1 to 2) As String

pets(1) = "dog"
pets(2) = "cat"

Worksheets("Sheet1").Range("A1:A2") = Application.Transpose(pets)

End Sub

If your array is larger than 65536 the transpose statement will return an error, this macro will not work.

Sub Macro1()
Dim pets(1 To 65537) As String
Dim i As Single
For i = 1 To 65537
pets(i) = i
Next i
Worksheets("Sheet1").Range("A1:A65537") = Application.Transpose(pets)
End Sub

The Transpose statement returns Run-time error '13': Type mismatch.

However, there is a workaround to overcome this array limit. The following macro returns an array vertically to cell range A1:A65537 and Transpose is not even used. The downside is that you can't change the size of the first dimension using ReDim Preserve.

Sub Macro1()
Dim pets(1 To 65537, 1 To 1) As Single

For i = 1 To 65537
pets(i, 1) = i
Next i

Worksheets("Sheet1").Range("A1:A65537") = pets

End Sub

Be aware that if you use Transpose with an array that is larger than 65536 in excel 2013 or 2016 no error is shown.

Back to top

10. Fetch data from a worksheet

This macro retrieves 65537 values from cell range A1:A65537 and it does it super fast.

Sub Macro1()
Dim pets() As Variant

pets = Worksheets("Sheet1").Range("A1:A65537").Value

MsgBox "LBound(pets,1):" & LBound(pets, 1) & " Ubound(pets,1): " & UBound(pets, 1) & vbNewLine & "LBound(pets,2): " & LBound(pets, 2) & " UBound(pets,2): " & UBound(pets, 2)

End Sub

It returns a 2-dimensional array even if you only grab one column of data.

Back to top

10.1 Copy data from an excel defined table

You can also copy values from an excel defined table to an array.

Sub Macro1()
Dim pets() As Variant

pets = Range("Table1").Value

MsgBox "LBound(pets,1):" & LBound(pets, 1) & " Ubound(pets,1): " & UBound(pets, 1) & vbNewLine & "LBound(pets,2): " & LBound(pets, 2) & " UBound(pets,2): " & UBound(pets, 2)

End Sub

Range("Table1").Value returns all values except the headers, use Table1[#All] if you want all values. If you want to copy only column "Name" to an array use Table1[Name].

Back to top

11. Copy an array

To copy an entire array simply use the equal sign.

Sub Macro1()
Dim pets(1 To 2) As Variant
Dim dogs() As Variant

pets(1) = "Labrador Retriever"
pets(2) = "Golden Retriever"

dogs = pets

MsgBox dogs(1) & vbNewLine & dogs(2)
End Sub

Back to top

11.1 Copy a column to a new array

Application.Index statement allows you to copy a section of an array.

dogs = Application.Index(pets, 0, 1) copies column 1 from array pets to a new array named dogs.

Copying a column to a new array creates a 2-dimensional array but if you copy a row the new array is 1-dimensional, see the two next macro examples below.

Sub Macro1()
Dim pets(1 To 2, 1 To 2) As Variant
Dim dogs() As Variant

pets(1, 1) = "Labrador Retriever"
pets(2, 1) = "Golden Retriever"
pets(1, 2) = "Boxer"
pets(2, 2) = "Beagle"
dogs = Application.Index(pets, 0, 1)

MsgBox dogs(1, 1) & vbNewLine & dogs(2, 1)
End Sub

Back to top

11.2 Copy a row to a new array

Copying a row (horizontal values) from an array creates a new array with only one dimension.

Sub Macro1()
Dim pets(1 To 2, 1 To 2) As Variant
Dim dogs() As Variant

pets(1, 1) = "Labrador Retriever"
pets(2, 1) = "Golden Retriever"
pets(1, 2) = "Boxer"
pets(2, 2) = "Beagle"
dogs = Application.Index(pets, 2, 0)

MsgBox dogs(1) & vbNewLine & dogs(2)

End Sub

As you might have suspected there is a problem with the INDEX function. It can't handle arrays larger than 65536 values and Microsoft seems to do nothing about it, the error has been there since excel 2007.

Back to top

12. Send an array to a macro

Macro2 creates and populates a new array and then runs Macro1 using the array as an argument.

Sub Macro1(val() As String)

MsgBox val(1)  vbNewLine  val(2)
End Sub

Sub Macro2()
Dim pets(1 To 2) As String
pets(1) = "Labrador Retriever"
pets(2) = "Golden Retriever"
Call Macro1(pets)
End Sub

Back to top

13. Pass an array to a function

The following two functions demonstrates how to pass arrays between functions. User defined function Func2 copies values from a worksheet to an array, it then passes the array to Func1. Func1 transposes the values and sends them back. Func2 then returns the values to a worksheet.

Function Func1(val() As Variant)
Func1 = Application.Transpose(val)
End Function

Function Func2(rng As Range)
Dim temp() As Variant
Dim temp1() As Variant

temp = rng.Value
Func2 = Func1(temp)
End Function

You can also send an array from a macro to a udf.

Back to top

14. Determine the number of dimensions in an array

User defined function CountDim counts the number of dimensions in an array.

Function CountDim(val() As Variant)
Dim i As Single

On Error Resume Next
For i = 1 To 60000
chk = LBound(val, i)
If Err <> 0 Then Exit For
Next i
On Error GoTo 0
CountDim = i - 1
End Function

Function Test()
Dim arr(1 To 10, 1 To 5, 1 To 3, 1 To 5, 1 To 3)
Test = CountDim(arr)
End Function

Function Test returns 5, there are five dimensions in this array:

Back to top

15. Split a text string

The split function allows you to separate a text string using a delimiter. It returns a 1-dimensional array (0 to n) and that makes it interesting in this context.

Function CountWords(rng As Range)
Text = Split(rng, " ")
CountWords = UBound(Text) + 1
End Function

Back to top

Arrays category

More than 1300 Excel formulas

Excel categories

Comments (11)