Option Explicit - Declarations

[ElvisSteel]

Hi,
Several people on this board have recommended that I alway use Option Explicit in my VB code (via the Tools, Options, Require Variable Declaration option)

Firstly, what is the thinking behind this (or what are the implications of not doing this?)

Secondly, When I have a lot of code, I tend to use multiple Modules to group macros together under a common theme. Often I use the same variables in multiple modules, and is there a way of "globally" declaring a variable (perhaps in a single module that contains only these declarations)

These probably seem like dumb questions, but I am quite happy to convert my code to work this way, but I want to ensure that I do this in the most efficient way.

Thanks

Steve

 

[ElvisSteel]

Jon, thanks for the reference - very useful
Hermanito, thanks for the coding advice.

However I'm still confused as to how best to declare variables that are ranges when using Option Explicit.

I'm using

Public ResourceTable as Range

Is this, plus the Set statement as per Hermanitos response the best way to do this?

What is your advice generally on declaring variables that are used in multiple modules, declare them each time or once as Public?

Thanks again for your help and advice, it is invaluable

 

[dave3009]

Declaring variables using option explicit isn't any different from declaring without. you always have to set a range if you plan to do something with it during the routine.

If you're going to use a particular variable in multiple modules then it's probably a good time saving tip to declare them just once, it's also neater and less confusing.

 

[Colin Legg]

Just to summarise:

Always, without exception, use Option Explicit in VBA code. You should have an Option Explicit statement at the top of every code module.

In the VBE you can go to Tools > Options > Editor tab and tick the "Require Variable Declaration" checkbox. This will mean that, going forward, Option Explicit statements will be automatically added to new code modules for you.


Option Explicit forces you to explicitly declare your variables. Without it, implicit variable declaration is possible. This is where VBA creates variables behind the scenes for you. For example, in this code:

Sub foo()
 
    number1 = 1
    number2 = 2
 
    MsgBox number1 + nubmer2
 
End Sub

The message box gives us "1".

Three Variant type variables are implicitly created:

• number1
• number2
• nubmer2 (obviously a typo)

The benefits of having Option Explicit and correctly declaring your variables with the most appropriate data types quickly become clear:

• It improves your knowledge as a VBA programmer because you gain a better understanding of how code works
• Typos in your code are identified at compile time. Typos can be very hard to spot when debugging, so this will save you hours of headaches.
• It will also make it easier to avoid or identify other bugs in your code in a variety of situations
• Variant types, as a general rule of thumb, will make your code run more slowly. If you are explicitly declaring your variables as more appropriate data types such as a Range, a String, a Long Integer, rather than having all your variables implicitly declared as variants, will mean that your code will run more quickly. There are, of course, situations where Variant types are entirely appropriate.

There are no valid disadvantages to using Option Explicit.

What is your advice generally on declaring variables that are used in multiple modules, declare them each time or once as Public?

Are we talking about a range reference which is genuinely shared by all the procedures in those modules?

I suspect you have a situation that's like this:

Option Explicit
Sub foo1()
    
    Dim rngCell As Range
    
    For Each rngCell In Range("B1:B3")
        Debug.Print VBA.CStr(rngCell.Value)
    Next rngCell
    
End Sub
 
Sub foo2()
    Dim rngCell As Range
    
    For Each rngCell In Range("A1:A3")
        Debug.Print VBA.CStr(rngCell.Value)
    Next rngCell
    
End Sub

Both the procedures have a Range type variable called rngCell so you are asking if you should use this instead?

Option Explicit
 
Dim rngCell As Range
 
Sub foo1()
    
    For Each rngCell In Range("B1:B3")
        Debug.Print VBA.CStr(rngCell.Value)
    Next rngCell
    
End Sub
 
Sub foo2()
    
    For Each rngCell In Range("A1:A3")
        Debug.Print VBA.CStr(rngCell.Value)
    Next rngCell
    
End Sub

In this sort of scenario, my answer is no: variable scope should be minimised, and these two Range variables really have nothing to do with each other (the only common point is they are both the same type), so you should stick with the first version.

Hope that helps...