3

I saw array() = range().value in an example and I'm trying to understand how it works. 

Sub test()
Dim arr() As Variant

arr() = Range("E5:E7").Value
For i = 0 To UBound(arr)
    Debug.Print arr(i)
Next i

End Sub

First, above code is giving me subscript out of range error. How come ? Second, what part of the documentation would let me know how array() = range().value would play out without testing it ? My hypothesis is that it will go through the cells from the top left to the bottom right and add them to the array. How can I confirm that though ?

Improve this question

3 Answers 3

Reset to default
2

I see two issues with your code. The first is that you start i at 0, but arrays in Excel begin at index 1. Instead of For i = 0 you can use For i = LBound(arr) like you use UBound(arr) or just start it at 1.

Second, and more importantly, an array of cells has both columns and rows. When you read a range into a variant array, you get a two-dimensional result (rows and columns) and not a one-dimensional result as you seem to be expecting.

Try this:

Sub test()
        Dim arr() As Variant
        Dim i As Long, j As Long

        arr() = Range("E5:E7").Value
        For i = 1 To UBound(arr, 1)
                For j = 1 To UBound(arr, 2)
                        Debug.Print arr(i, j)
                Next j
        Next i
End Sub

If you want to get just the values of the cells into a one dimensional array, you can do that by using the Transpose function, like this:

arr() = Application.WorksheetFunction.Transpose(Range("E5:E7").Value)

If you do this, the array is now one-dimensional and you can iterate through it like you were trying to.

arr() = Application.WorksheetFunction.Transpose(Range("E5:E7").Value)

For i = 1 To UBound(arr)
    Debug.Print arr(i)
Next i
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

This is a good read for you: http://www.cpearson.com/excel/ArraysAndRanges.aspx

The reason you're getting "out of range" is because it returns a 2 dimensional array.

Your line of code For i = 0 To UBound(arr) should be For i = 1 To UBound(arr,1)

Also, the array starts at 1, so don't use the 0 For i = 1 to UBound(arr, 1)

Your corrected code would be:

Sub Test()

Dim arr() as Variant
arr = Range("E5:E7")
For i = 1 To UBound(arr, 1)
    MsgBox (arr(i, 1))
Next i

End Sub
Improve this answer

3 Comments

Selected this one as an answer due to the informative link on the topic. Thank you every one.
Why do I get a Type Mismatch if I change the range to Range("E5")?
If the range is a single cell, it returns a single value instead of a range. Only ranges containing multiple cells return an array. You can determine this using If Range("E5").Cells.CountLarge = 1 and program accordingly.
0

It's basically loading the cell values of E5 - E7 into an array. But it is going to be two dimensional. So you will need Debug.Print arr(i, 1)

Sub test()
Dim arr() As Variant

arr() = Range("E5:E7").Value
For i = 1 To UBound(arr)
    Debug.Print arr(i, 1)
Next i

End Sub
Improve this answer

2 Comments

I'm getting a Type Mismatch error with the code. Edit: that's because I changed the range to Range("E5").
You will need to start at 1 For i = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.