Print Range to an Array in VBA

When speaking about Excel, we have to know the difference between arrays and ranges.

The main difference is that array is a collection of different elements, that can vary. Range has a beginning and an end, and we do not have to assign numbers to elements that are in between.

This means that we can transform our array to a certain range. In the example below, we will show how to do this.

Add Range in Array in VBA

First thing first, we need to define our range, and that will be a series of random numbers, ranging from cell A2 to cell A11:

Now we need to open the VBA, and we will do that by clicking ALT + F11 on our keyboard, and then going to the Insert tab and choosing Module:

In the window that appears on the right side, we will type in our formula:

Sub PrintRangeToArray()
    Dim i() As Variant
    Dim j As Integer
    i = Range("A1:A11")
    For j = 1 To UBound(i)
        Debug.Print i(j, 1)
    Next j
End Sub

Sub PrintRangeToArray()

Dim i() As Variant

Dim j As Integer

i = Range("A1:A11")

For j = 1 To UBound(i)

Debug.Print i(j, 1)

Next j

End Sub

This is what our formula looks like in the module:

This formula declares two variables, “i” as a variant, and “j” as an integer. Then we declare the “i” variable to be equal to the range A1:A11.

For the last part, we use For Next Loop from 1 to the count of “i” (in our case, 11 times), and then print out the values in column number 1, in our Immediate window.

The Immediate window is a nice preview option in VBA that can be accessed in the View tab, or by clicking the shortcut in VBA (CTRL + G).

Now we can either step through our code by pressing F8 while in the module or we can execute the code by pressing F5. We will do the latter, and have the following results in our Immediate window:

Although it does not seem as if we did much, we have successfully managed to input our range into an array.

Add Range to a Multi-Dimensional Array

We can also use an array to store data from ranges that span over multiple columns. To do this, we will use a multi-dimensional array. For this to work, we will add the names in column B:

To encompass both of these columns and add this range to an array, we will use the following formula:

Sub PrintRangeToArray2()
    Dim i() As Variant
    Dim j As Integer
    i = Range("A1:B11")
    For j = 1 To UBound(i, 1)
        Debug.Print i(j, 1) & " " & i(j, 2)
    Next j
End Sub

Sub PrintRangeToArray2()

Dim i() As Variant

Dim j As Integer

i = Range("A1:B11")

For j = 1 To UBound(i, 1)

Debug.Print i(j, 1) & " " & i(j, 2)

Next j

End Sub

Our formulas look like this in the module:

When we execute this code by pressing F5 while in the code and having our Immediate window opened, this is what we will end up with:

Which is the exact result we wanted.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 71