I have a code in a userform in vba excel:
Dim TimeArray() As Variant
TimeArray = Worksheets("Data").Range("E1:E30").Value
MsgBox TimeArray(0)
The error is
"Subscript is out of range"
while my range (E1:E3) have data.
-
The way you assigned values to the array means that the LBound value of the array is = 1 not 0.Xabier– Xabier2018-07-20 09:29:38 +00:00Commented Jul 20, 2018 at 9:29
Add a comment
|
1 Answer
Converting range to array is a standard procedure in VBA. In general, when you do it explicitly like this TimeArray = Worksheets("Data").Range("E1:E30").Value, VBA translates it to a 2D-array. Thus, to get the first value you have to ask TimeArray(1, 1), because the cells in Excel start with column 1 and row 1:
Sub TestMe()
Dim TimeArray() As Variant
TimeArray = Worksheets(1).Range("E1:E30")
MsgBox TimeArray(1, 1)
End Sub
With Application.Transpose(), you can force a 1-dimensional array from one column:
Sub TestMeAndTransposeMe()
Dim TimeArray() As Variant
TimeArray = Application.Transpose(Worksheets(1).Range("E1:E30"))
MsgBox TimeArray(1)
Dim i As Long
For i = LBound(TimeArray) To UBound(TimeArray)
Debug.Print i; TimeArray(i)
Next i
End Sub
You can see additional examples of the useage of Application.Transpose() here:
