Assign a range to an array

Question

Everything I find says this is a quick and easy way to assign a range of values to an array.

I get an error when I try to access the array.

Subscript out of range.

Option Explicit

Sub assign_arr_from_range()
    Dim myArr() As Variant
    myArr = Range("A1:D1").Value
    MsgBox (myArr(3))
End Sub

Answer 1

• myArr = Range("...").Value method always creates a two-dimensional array, regardless of whether the range is a row, a column, or an area.
• To access (read/update) a specific item, you should use myArr(1, x) instead of myArr(x).
• However, if you prefer using myArr(x), you can implement option 2 as shown below.

Option Explicit
Sub assign_arr_from_range()
    
    Dim myArr(), myArr2() 
    myArr = Range("A1:D1").Value
    MsgBox (myArr(1, 3))
    
    ' Option 2
    myArr2 = Application.Transpose(Application.Transpose(Range("A1:D1").Value))
    MsgBox (myArr2(3))
    
End Sub

Microsoft documentation:

WorksheetFunction.Transpose method (Excel)