Runtime error 9. Subscript is out of range

Question

VBA does not let me apply to the particular element in the dynamical array.

Dim a() As Variant
a = Range("A2:A11").Value
Range("B2:B11").Value = a 'Just to make sure that the list is not empty and is working correctly.

MsgBox a(1) 'OR a(7)=0 OR IF a(4)=0 then MsgBox "!"

I expect to use the first element of the array a, but get an error message at the fourth line, trying to execute MsgBox a(1) or any expression, which involves arr_name(num_index)). I tried the identical code with the static array, which works without any problems:

Dim b(10) As Variant
b(1) = 1234
MsgBox b(1)

The debugger also says that "subscript is out of range". At the same time, The array is for sure not void since the 3rd line works correctly and the array from a is copied to the array of neighboring cells and displayed there just a moment before the macros stops and I'm getting the error message. What is wrong with my code?

If you look in the locals window while stepping through your code you'll see that a is 2D - any array from a sheet is automatically so. So you need MsgBox a(1,1). Or you can transpose it to turn it into 1D. — SJR
Thank you, Mikku and SJR! I did not expect the array to be 2-dimensional. Thank you for the tip with Locals window. It may be really of use - I used to add the watches for every variable manually. — Alex

Mikku Mikku · Accepted Answer · 2019-08-08T08:27:15

This Code:

Dim a() As Variant
a = Range("A2:A11").Value
Range("B2:B11").Value = a

Will give you a 2-D array. To get the Elements of a 2-D array you need to do

 MsgBox a(1,1)
 MsgBox a(2,1)

and so on.

Also, You can check the Locals Window for all the Objects.

Runtime error 9. Subscript is out of range

2 Answers