top of page
  • John

Make a VBA Module "Option Explicit"

If, like me, you commonly inherit VBA code that has been written by other developers without including "Option Explicit" in one or more Modules, it can be very frustrating.


Not having "Option Explicit" has a number of downsides:


  • It can silently introduce bugs into VBA code … hard-to-spot typos in variable names result in brand new variables being created on-the-fly (and, of course, the two variables won't hold the same value)

  • … which makes maintaining and updating other peoples code much more challenging (did that other developer MEAN to use that similarly named variable or is it a typo?)

  • The case (i.e. lower, upper, or a mix of) for the variable name that you type at any point in your code will be inherited by all uses of it ... whereas if you had explicitly defined the variable then the case that you used in the declaration statement will always be inherited

  • Without an explicit declaration, you cannot use certain functions of VBE_Extras … for example, the Rename function relies on the explicit declaration of a variable in order to rename it.


Put simply, not using "Option Explicit" is a rookie mistake … it may appear that you are making it quicker and simpler to write VBA code ... but any experienced VBA developer knows that the first line of code in any Module should always be "Option Explicit" (and that developer also knows that the VBE can add that line automatically for new Modules by checking "Require Variable Declaration" in the Editor tab of the VBE's Options dialog).


But what do you do if you inherit a Module - possibly with hundreds or thousands of lines of code - without "Option Explicit"? For a long time this frustrated me. I would even turn down work that required me to update or maintain code that did not have "Option Explicit" present in all Modules.


So I decided to do something better. I added the 'Make "Option Explicit"' function to VBE_Extras. It does exactly what it sounds like it does … it adds "Option Explicit" at the start of the Module and then adds explicit declarations for each and every variable that doesn't already have an explicit declaration.


Here's a very brief example in which VBE_Extras has automatically transformed this code (which does not have 'Option Explicit and declares some variables but not all of them) ...

Code without Option Explicit

... into this code (which does have 'Option Explicit' and all variables are explicitly declared) ...

Code with Option Explicit

One thing to know about the 'Make "Option Explicit"' function is that it works only if the 'Office Display Language' is set to English (in the UI of the host application: File > Options > Language > Office Display Language).


The 'Make "Option Explicit"' function works in all host applications supported by VBE_Extras which is currently Excel, Word, Access, PowerPoint and Outlook.


Why not download VBE_Extras (which comes with a 60-day free trial) and give it a try?


Click "Option Explicit" for the MS Docs.

0 comments

Comments


bottom of page