Convert range to an array

Question

I know a lot of threads regarding this topic already exist, but I still can't find a solution that works in this scenario. The following two subs keep giving me a "subscript out of range" error.

Sub test1()
    Dim Arr() As Variant
    Arr= Range("A1:A10")

    MsgBox Arr(0)

End Sub

Sub test1()
    Dim Arr As Variant
    Arr= Range("A1:A10").Value

    MsgBox Arr(0)

End Sub

Answer 1

EDITED for clarity, in light of the comments below.

Assigning a range's Value to a Variant variable will either result in the variable containing a one-based 2D array of Variants indexed by row and column (in this order), or containing the actual value of the range if it's a 1-cell range.

In your particular case, this would work:

Sub test1()
    Dim Arr As Variant
    Dim row As Long
    Dim col As Long

    row = 1
    col = 1

    Arr = Range("A1:A10").Value

    MsgBox Arr(row, col)
End Sub

In a more general approach, if your downstream code expects to deal with an array, but you range has the possibility to cover a single cell, you can force an array even in such a situation, along those lines:

Sub test2()
    Dim rng As Range
    Dim Arr As Variant
    Dim row As Integer
    Dim col As Integer

    row = 1
    col = 1

    Set rng = Range("A1:A1") '<== 1 cell only!

    Arr = rng.Value

    'Ensure we're dealing with an array even in this case.
    If Not IsArray(Arr) Then
        ReDim Arr(1 To 1, 1 To 1) As Variant
        Arr(1, 1) = rng.Value
    End If

    MsgBox Arr(row, col)
End Sub