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. 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

2. 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

3. 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.

3.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

3.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

3.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

4. 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

4.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

4.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

4.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

5. 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

6. 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

7. Transfer array values to a worksheet

&

7.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

7.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

8. 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

9. 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

9.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

10. 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

10.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

10.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

11. 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

12. 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

13. 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

14. 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)