Option Base 1 But LBound Is 0

[wsnyder]

Hi all,

Using Excel 365.
I have Option Base 1 at the top of my Modules

However my code is returning Subscript Out of Range error
And when I hover over the array variable for LBound it shows as 0.

thanks,
-w

Error here:

 Debug.Print vCriteria(i), vColumns(i)

Full code:

Option Explicit
Option Base 1

Sub Foo()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim s As String
    Dim i As Long
    Dim vCriteria As Variant
    Dim vColumns As Variant
    
    Set wb = Thisworkbook
    Set ws = wb.Worksheets("Data")
    Set rng = ws.Range("1:1")
    
    s = "cat","dog","mouse"
        
    vCriteria = GetCriteriaArray(s:=s)                              'Private Function
    vColumns = GetColumnArray(v:=vCriteria, _
                              rng:=rng)                             'Private Function
    Debug.Print "===================================="
    Debug.Print "vCriteria & vColumns"
    Debug.Print "------------------------------------"
    For i = LBound(vCriteria) To UBound(vCriteria)
        Debug.Print vCriteria(i), vColumns(i)
    Next i
    Debug.Print "===================================="
 
    'Tidy up
        Erase vColumns
        Erase vCriteria
        Set rng = Nothing
        Set ws = Nothing
        Set wb = Nothing
    
End Sub

Private Function GetCriteriaArray(s As String) As Variant

    GetCriteriaArray = Split(s, ",")

End Function


Private Function GetColumnArray(v As Variant, _
                                rng As Range) As Variant

    Dim i As Long
    Dim x As Long
    Dim crit As String
    Dim tempColumnArray() As Long
    x = 1
    
    Debug.Print "Range Address: ", rng.Address
    
    For i = LBound(v) To UBound(v)
        ReDim Preserve tempColumnArray(x)
        crit = CStr(v(i))
        Debug.Print x, crit
        tempColumnArray(x) = FindColumnHeader(rng:=rng, _
                                              SearchTerm:=crit)
        Debug.Print x, tempColumnArray(x)
        x = x + 1
    Next i
    
    GetColumnArray = CVar(tempColumnArray)

End Function

 

[Fluff]

The Split function will always return a 0 based array regardless of your settings.

 

[Rick Rothstein]

As Fluff mentioned, the Split function always returns a zero-based array no matter what the Option Base setting is. In a similar manner, the lower bound for both dimensions of an array created by assigning a range to a Variant variable will always be one no matter what the Option Base setting is.

 

[wsnyder]

Thanks for the learnin' gents.
-w

 

[Fluff]

Glad we could help & thanks for the feedback.