VBA array is longer than I expect

Question

I have a simple problem. I have the following code:

Sub test()
    Dim vr() As String
    ReDim vr(1)
    vr(0) = 1
    For i = 0 To UBound(vr)
        Debug.Print (vr(i))
    Next i
End Sub

Even though I have set the length of the array to one, the array actually has two positions:

vr(0) = 1
vr(1) = ""

Why does it have two positions even though I have set the length to one?

As it has been said, you need to use Option Base 1 in order that excel starts arrays' at position 1 rather than 0 as default — Moreno
– Moreno, Commented Dec 14, 2017 at 23:10

S Meaden · Accepted Answer · 2017-12-15 09:13:27Z

4

Well I'd try the following.

Redim vr(0 to 0)

Do you have Option Base at the top of your module? Is it Option Base 0 or Option Base 1?

Regardless specifying both lower and upper bounds as per above example will insulate from whatever Option Base says at the top.

edited Dec 15, 2017 at 9:13

answered Dec 14, 2017 at 21:45

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

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

7 Comments

YowE3K Over a year ago

Don't need to ask about the base - the OP is setting vr(0), so they know that there is a position 0. I think they just don't expect there to be a position 1 despite setting that as the upper bound.

YowE3K Over a year ago

I suspect that the OP is thinking that a ReDim specifies the number of positions in an array, instead of the upper and lower bounds. (So they probably think that ReDim vr(2) should do what we would write as ReDim vr(-5 To -4) but, of course, it doesn't.)

S Meaden Over a year ago

@YowE3K: I agree, like C++ int foo[5] gives elements 0 to 4 but not VBA.

YowE3K Over a year ago

Curious - does c++ allow a non-zero base? i.e. can it do the equivalent of VBA's ReDim vr(-5 To -4) or does it always start from 0?

S Meaden Over a year ago

@YowE3K: no, not unless one uses a SAFEARRAY which is what VBA uses under the hood. Like here stackoverflow.com/questions/3730840/#3735438

|

Collectives™ on Stack Overflow

VBA array is longer than I expect

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related