How to programmatically determine current setting for Option Base in VBA

Question

How can I programmatically determine the current setting for Option Base in VBA? The Option Base can be set to 0 or 1, and this determines whether array indices start at 0 or 1 (see MSDN).

However, I can't see any easy way to find out what the current setting is. I was hoping there might be an Option() function that I could pass a parameter to, something like:

Debug.Print Option("Base")

and it would tell me, but it doesn't seem to be there.

Answer 1

Function GetBaseOption() As Long

    Dim arr

    arr = Array("a")
    GetBaseOption = LBound(arr)

End Sub

Answer 2

While I agree with @jonsharpe that if you're using Lbound(arr) and Ubound(arr), you don't need to know this at runtime, but I think it's an interesting question.

First, an example of properly looping through an array.

For i = LBound(arr) To Ubound(arr)
    ' do something
Next

---

Now, in order to do exactly what you asked, you'll need to access use the VBIDE library. Otherwise known as the "Microsoft Visual Basic for Applications Extensibility" library. It provides access to the IDE and the code with-in it. You can use it to discover if Option Base 1 has been declared. I don't recommend trying to do it at runtime though. This would be more useful as a kind of static code analysis during development.

First, you'll need to add a reference to the library and grant the code access to itself. Once you've done that, the following code should do what you would like.

Public Sub FindOptionBase1Declarations()
    
    Dim startLine As Long
    startLine = 1
    
    Dim startCol As Long
    startCol = 1
    
    Dim endLine As Long
    endLine = 1 ' 1 represents the last line of the module
    
    Dim endCol As Long
    endCol = 1 ' like endLine, 1 designates the last Col
    
    Dim module As CodeModule
    Dim component As VBComponent
    For Each component In Application.VBE.ActiveVBProject.VBComponents ' substitute with any VBProject you like
        Set module = component.CodeModule
        
        If module.Find("Option Base 1", startLine, startCol, endLine, endCol, WholeWord:=True, MatchCase:=True, PatternSearch:=False) Then
            
            Debug.Print "Option Base 1 turned on in module " & component.Name
            
            ' variables are passed by ref, so they tell us the exact location of the statement
            Debug.Print "StartLine: " & startLine
            Debug.Print "EndLine: " & endLine
            Debug.Print "StartCol: " & startCol
            Debug.Print "EndCol: " & endCol
            
            ' this also means we have to reset them before we look in the next module
            startLine = 1
            startCol = 1
            endLine = 1
            endCol = 1
        End If
        
    Next 
    
End Sub

See the CodeModule.Find documentation for more information.

---

If using an add-in is an option, @Mat'sMug and I's open source project Rubberduck has a Code Inspection that will show you all instances of this throughout the active project.

See this for more information on that particular inspection.