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, Dec 14 '17 at 23:10

S Meaden · Accepted Answer · 2017-12-15T09:13:27.463

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 '17 at 09:13

answered Dec 14 '17 at 21:45

S Meaden

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 Dec 14 '17 at 21:49
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.) – YowE3K Dec 14 '17 at 21:54
@YowE3K: I agree, like C++ `int foo[5]` gives elements 0 to 4 but not VBA. – S Meaden Dec 14 '17 at 21:55
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`? – YowE3K Dec 14 '17 at 21:57
@YowE3K: no, not unless one uses a SAFEARRAY which is what VBA uses under the hood. Like here https://stackoverflow.com/questions/3730840/#3735438 – S Meaden Dec 14 '17 at 21:57
Okay I understand. Redim v(1) is not the same as int v[1] ? How doest it work then? – fossekall Dec 15 '17 at 09:12
@fossekall: I have edited answer to include a link to MSDN for Option Base. All of the information is in the answer. – S Meaden Dec 15 '17 at 09:14

1 Answers1