Excel VBA -- Subscript out of range with single-dimension array

Question

This is an awesomely simple problem, but I've been breaking my head against it for an hour. I'm trying to do the following:

define a VBA array from a named range in my spreadsheet
Access an item in that array

Now, if the named range happens to cover a multi-dimensional area (rows and columns), then this works fine:

foobar = Range("two_dimensional_range")
MsgBox foobar(1,1)

However, if the named range consists of a single dimension (just a column) of data, then this produces a "Subscript Out of Range" error:

foobar = Range("one_dimensional_range")
MsgBox foobar(1)

I've verified that foobar is really an array. I can iterate through it with For Each ... Next. If I call Ubound(foobar) then it gives me the correct size. I just can't access a single element of it directly!

What am I doing wrong?

Siddharth Rout · Accepted Answer · 2012-08-30 18:33:49Z

6

When you assign a range (Not a single cell) to a variant array, It will always be a multidimensional array so this MsgBox foobar(1,1) will still work in the 2nd instance.

See this example

Option Explicit

Sub Sample()
    Dim Ar As Variant

    Ar = Range("A1:A2")

    Debug.Print Ar(1, 1)
    Debug.Print Ar(2, 1)
End Sub

Another example

Option Explicit

Sub Sample()
    Dim Ar As Variant

    Ar = Range("A1:B2")

    '~~> Get Values from Col A
    Debug.Print Ar(1, 1)
    Debug.Print Ar(2, 1)

    '~~> Get Values from Col B
    Debug.Print Ar(1, 2)
    Debug.Print Ar(2, 2)
End Sub

answered Aug 30, 2012 at 18:33

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA -- Subscript out of range with single-dimension array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related