How to programmatically determine the current setting for the options database in VBA

Question

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

However, I see no easy way to find out what the current setting is. I was hoping there might be an Option() function with which I can pass a parameter, for example:

 Debug.Print Option("Base") 

and it will tell me, but it seems that it is not.

Answer 1

So far, I agree with @jonsharpe that if you use Lbound(arr) and Ubound(arr) , you don't need to know this at runtime, but I think this is an interesting question.

First, an example of the correct loop through an array.

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

---

Now, to accomplish exactly what you requested, you need to access the VBIDE library. Otherwise, it is called the Microsoft Visual Basic Library for Application Extensibility. It provides access to the IDE and code with it. You can use it to find out if Option Base 1 declared. However, I do not recommend doing this at runtime. This would be more useful as some kind of static code analysis during development.

First you need to add a link to the library and provide access to the code to yourself . After you have done this, 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 the add-in is an option, @ Mat'sMug and I open source project Rubberduck has a Code Check that will show you all instances of this in the entire active project.

See this for more information on this particular check .

Answer 2

 Function GetBaseOption() As Long Dim arr arr = Array("a") GetBaseOption = LBound(arr) End Sub 

Answer 3

Just use:

 LBound(Array()) 

... to return to the current Option Base setting.

---

Or as a function:

 Function optionBase() As Byte optionBase = LBound(Array()) End Function