CBlog(handziuk)

Bradley Handziuk's blog

Always use Option Explicit

12. May 2014 Bradley Programming (0)
When writing any VBA for Excel, Access or Word or whatever you automate you have the option to use the default settings in the VBE editor. Well one of the default that Microsoft provides is to not enforce variable declaration. This code can run just fine 
Function MyNewFunction()
    a = 5 * 7
    MyNewFunction = a
End Function
While this is not a very useful function you'll notice that a was used without every being declared. This often leads to a lot of problems for people. the usual things are
  1. They don't know when their variable went out of scope
  2. They don't know the data type of their variable (and consequently) there is no intellisense support for variables used like this
  3. They use it in appropriately like trying to add a string to a date

the trick is to always have option explicit declared at the top of all your modules. This means that if you do not declare a variable your code will not compile.

You should have the VBE automatically put option explicit at the top of any new modules/class you make by going to  Tools>Options>Editor> Require Variable Declaration = true



Now you can only write the much less sloppy
Function MyNewFunction()
    Dim a as Integer
    a = 5 * 7
    MyNewFunction = a
End Function
Now start hitting Compile often! These two together will save you a lot of headaches.

Add comment

Loading