The importance of 'Option Explicit'

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

The importance of 'Option Explicit'

Post by HansV »

When Loungers post a file such as a Word document or Excel workbook with VBA code, I often notice that there's no line

Option Explicit

at the top of the code module(s). In this long post I'll try to explain why there should be such a line, and how you can have it added automatically to new modules.

The line 'Option Explicit' specifies that you must explicitly declare all variables that you use within the module.

If you don't have this line at the top a module, you can create variables "on the fly" by simply mentioning them in your code. This may seem handy in the beginning, but it is dangerous, because a small typo means that you create a new variable and you won't be warned about this.

An Example

Look at this simple macro:

Code: Select all

Sub ExampleCode()
  intVariable1 = 5
  intVariable2 = 6
  intSum = intVariable1 + intVariablr2
  MsgBox intSum
End Sub
There is a typo in the code: we used intVariablr2 instead of intVariable2 in the line that assigns a value to intSum.

If we don't have 'Option Explicit' at the top of the module, VBA will create variables intVariable1, intVariable2, intSum and intVariablr2 for us.

intVariable1 is assigned the value 5, and intVariable2 is assigned the value 6, but since we don't assign a value to intVariablr2 in our code, it has the default value 0. Therefore intSum is assigned the value 5 + 0 = 5, instead of 5 + 6 = 11.

In a procedure that processes financial information, that could have serious consequences!

Let's see what happens in our sample macro when we add the line Option Explicit at the top of the module:

Code: Select all

Option Explicit

Sub ExampleCode()
  intVariable1 = 5
  intVariable2 = 6
  intSum = intVariable1 + intVariablr2
  MsgBox intSum
End Sub
If we try to run the code, we will get a 'Variable not defined' error. The word intVariable1 will be highlighted. Of course! We are now obliged to declare the variables we use. So we change the code to

Code: Select all

Option Explicit

Sub ExampleCode()
  Dim intVariable1 As Integer
  Dim intVariable2 As Integer
  Dim intSum As Integer
  intVariable1 = 5
  intVariable2 = 6
  intSum = intVariable1 + intVariablr2
  MsgBox intSum
End Sub
But when we try to run the new version, we still get an error message. This time, the word intVariablr2 is highlighted, so we know that there is something wrong there. A quick inspection shows that we should have used intVariable2, and when we correct the typo, the code will run and provide the correct result 11.

Having 'Option Explicit' doesn't guarantee that you will write correct, meaningful or effective code, but it does prevent a lot of mistakes.

You may find the 'Variable not defined' warnings irritating initially, but in the long run, you'll save time because you won't have to hunt for typos.

How to add 'Option Explicit' automatically

You can tell the Visual Basic Editor to add the line 'Option Explicit' automatically to each new module that you create:

- In the Visual Basic Editor, select Tools | Options...
- In the Editor tab, tick the check box 'Require Variable Declaration'.
- Click OK.


The setting will apply to all Office applications.

'Require Variable Declaration' will only add 'Option Explicit' to newly created modules, not to modules that already existed before you turned on 'Require Variable Declaration'. You can add the option manually to an existing module by typing it at the top of the module, as we did in the above example.

It is advisable to select Debug | Compile <project name> after inserting 'Option Explicit' to an existing module. The Visual Basic Editor will highlight the first undefined Variable (if any), giving you the chance to add the required declaration or to correct the code if necessary. Repeat until you get no more error messages.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: The importance of 'Option Explicit'

Post by Rudi »

Hear hear...

I have learned from the best... (Having been rapped over the knuckles more than once about the very subject!) I still remember it sooooo clearly!

...and I have seen the benefits of it first hand :)

Thanks for this excellent post Hans. I train this concept with fervor in my training sessions!

Cheers
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: The importance of 'Option Explicit'

Post by Jan Karel Pieterse »

Another advantage of declaring variables is that the editor remembers the capitalisation. So if you:

Dim aVariable As SomeType

and you type
avariable = 10
and go to the next line, the VBE changes your line to:
aVariable = 10
automatically. I am so used to this behaviour that I immediately notice lines for which that does not happen.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: The importance of 'Option Explicit'

Post by HansV »

Good point!
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: The importance of 'Option Explicit'

Post by ChrisGreaves »

HansV wrote:You can tell the Visual Basic Editor to add the line 'Option Explicit' automatically
Thanks Hans, excellent post. The default setting in the VBE options seems to me as bad as setting "Hide Extensions" as the default in Explorer - guaranteed to cause problems to the people who most need help.

For the life of me I can't see why this couldn't have been rectified in, oh, perhaps Office-97 SR-1.
Last edited by ChrisGreaves on 26 Sep 2020, 05:37, edited 1 time in total.
An expensive day out: Wallet and Grimace

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: The importance of 'Option Explicit'

Post by ChrisGreaves »

HansV wrote:
30 May 2010, 13:50
In a procedure that processes financial information, that could have serious consequences
maxim: "If a thing is worth doing, it's worth doing properly".
I would argue that "financial" is irrelevant.

Regardless of the target of the program (financial, spaceflight, GPS, engineering stresses etc), the consequences are the same for every type of program. After all, if you are writing a type of program where accuracy doesn't matter, then why are you writing such a program in the first place?
Obvious exceptions are your excellent examples of how not to write code.

I use the financial argument a great deal because it hits a nerve ("... explaining to 10,000 unionized employees that their paychecks are late") which we all can feel, but I believe that the principle applies to all types of programs in all languages on all platforms.

Most of us can recall and ashamed moment where we erred and caused havoc. In some cases to our careers!

Cheers
Chris
An expensive day out: Wallet and Grimace

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: The importance of 'Option Explicit'

Post by LisaGreen »

Excellent thread!!

Lisa

User avatar
BobH
UraniumLounger
Posts: 9215
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: The importance of 'Option Explicit'

Post by BobH »

Excellent lesson in programming, Hans!

While I'm only an occasional dabbler in VBA, I've written code in various other languages and much of what you are pointing out is relevant to most programming.

You've done a great service today.

:cheers: :chocciebar: :thankyou:
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: The importance of 'Option Explicit'

Post by HansV »

Thank you, but this thread is more than 10 years old! Chris decided to revive it...
Best wishes,
Hans

User avatar
BobH
UraniumLounger
Posts: 9215
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: The importance of 'Option Explicit'

Post by BobH »

At my age, timing is not always precisely noted. You did a good days work no matter when it was done.
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: The importance of 'Option Explicit'

Post by HansV »

Best wishes,
Hans

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: The importance of 'Option Explicit'

Post by jstevens »

Ahem...guilty and thanks for the reminder. :cheers:
Regards,
John

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: The importance of 'Option Explicit'

Post by LisaGreen »

Plug away Hans!!! This stuff is so good and reminders that it is there are never wasted IMNSVHO!

Lisa

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: The importance of 'Option Explicit'

Post by siamandm »

very informative and important thing, i will always make sure to have this line from now on

thank you again.

Regards

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: The importance of 'Option Explicit'

Post by HansV »

Great! Thanks for the compliment.
Best wishes,
Hans