CODEWITHSUNDEEP
vbaexcel
Vityata
Vityata

Reputation: 43585

Why Are Excel Array Constants Base 1?

If you take this code and run it you would see that the initialization of the Array starts with 1 and not with 0.

Option Explicit

Public Sub TestMe()

    Dim x() As Variant
    x = [{"A", "B"}]
    PrintArray x

    'this gives an error:
    'Debug.Print x.Count
'-----------------------------------------------------------------

    Dim k As Variant
    k = Array("C", "D")
    PrintArray k

End Sub

Public Sub PrintArray(ByRef myArray As Variant)

    Dim lngCounter As Long

    For lngCounter = LBound(myArray) To UBound(myArray)
        Debug.Print lngCounter; " -> "; myArray(lngCounter)
    Next lngCounter
    Debug.Print TypeName(myArray)

End Sub

Any idea why? This is the immediate window:

 1  -> A
 2  -> B
Variant()
 0  -> C
 1  -> D
Variant()

It somehow looks like Collection, but when I do debug.print x.count it gives an error.

If it was a range, then it should be giving a range in the TypeName like this:

Public Sub TestMe()    
    Dim p As Variant
    Set p = Range("A1")
    Debug.Print TypeName(p)    
End Sub

Upvotes: 3

Views: 364

Answers (1)

Phil
Phil

Reputation: 397

When you initialize the array with values, it will always use standard VBA Lbound of 1. When you initialize the array using Array(), it will use whatever you specify in Option Base (default 0). If you put Option Base 1 at the start of your module, and run your test you will see it running consistently. MSDN on Array()

Upvotes: 3

Related Questions