Excel VBA Pro Tip: Learn a simple secret that 7 out of 8 Excel experts use all the time

[IMAGE] Option Explicit Excel VBA Pro Tip

Summary: Did you know there’s one simple practice that most Excel VBA experts do all the time?

It takes seconds to set up and can make your Excel VBA Macros more professional.

Read on to find out how “Option Explicit” works and why I recommend you start using it today.

Difficulty: Basic


Contents

Click on a link to jump to that section…

#1 – You said what ?!?! – funny misspellings
#2 – VBA Spell Checker – Option Explicit for spell checking
#3 – Why use Option Explicit? – 8 expert views
#4 – What if you’re still not convinced? – here’s my advice…
#5 – How to turn on Option Explicit – step by step instructions
#6 – Recap – key points
#7 – References – further reading
#8 – Over to you – what next?

#1 – You said what ?!?! – funny misspellings

» Back to contents

Have you ever seen words that are spelled wrong?

Just SO wrong that you have to laugh?

Like these misspellings…

  • Message on an exit popup window “Are you sure you want to exist?” – it was meant to read “Are you sure you want to exit?”… and if I were you I would think twice before pressing that NO button.
  • Sign posted on a barbed wire fence protecting a private car park said “Illegally parked cars will be fine” – I’m sure that sign caused a lot of arguments… but you said it would be “fine” to park here… nothing about me being fined.
  • Label on a pharmaceutical capsule dispenser had “Take one capsule by mouth nightly 3 hours before ded” – er… I think they meant “before bed” but if they didn’t… then I wouldn’t take one no matter what my doctor said.

In each case if someone had checked the spelling there would be no problem (and no laughs either).

These days spell checkers are built into so many pieces of software, there’s no excuse to misspell words.

So what’s this got to do with writing Excel VBA macros and the little-known “Option Explicit” I promised to explain?

#2  – VBA Spell Checker – Option Explicit for spell checking

» Back to contents

In Excel there’s something like a spell checker for when you write VBA code. It’s called “Option Explicit”

Option Explicit is surprising useful to prevent misspelled variables and constants from messing up your results.

By default, it is turned off so you can create variables and constants in your subroutines without first declaring them.

[Note: In case you’re not clear what I mean by a “variable” or “constant” just think of them as memory containers for your data. You give the container a name by declaring it, and later you can assign data to the container and recall data from the container by using its name. Containers with contents that can be changed are called “variables” whereas containers with contents that stay fixed are called “constants”.]

Example without Option Explicit

This is VBA code that creates variables without first declaring them:

Sub Calculate_Results()

  'Assign values to variables without first declaring them
  Asset_Value = 100000
  Current_Rate = 1.3
  
  New_Asset_Value = Asset_Value * Curent_Rate   'Spot the misspelling?
  
  MsgBox "Your new asset value is : " & New_Asset_Value

End Sub

In the first two lines of executable code, I assign the number 100,000 to the variable “Asset_Value” and the number 1.3 to “Current_Rate”.

So far so good…

The problem comes in the third line which says “New_Asset_Value = Asset_Value * Curent_Rate”

A quick look and I expect the answer to be 100,000 x 1.3 = 130,000

But when I run this code I get the result “0”.

[IMAGE] New asset value error

Er… I don’t like having asset values of 0. Do you?

What went wrong?

It was a simple misspelling of the variable “Current_Rate”. The VBA code I wrote used “Curent_Rate” with a single “r” instead of the correct double “r”.

So Excel dutifully created a new variable “Curent_Rate” with a value of 0 and used that to calculate the New_Asset_Value.

OK… what if we use Option Explicit?

By now you’ll want to know what happens if we use Option Explicit. Am I right?

Here’s the same code as above but with Option Explicit turned on:

Option Explicit
Sub Calculate_Results()

'Declaring variable names before using them

Dim Asset_Value As Double
Dim Current_Rate As Double
Dim New_Asset_Value As Double

  'Now we can assign values to our variables
  Asset_Value = 100000
  Current_Rate = 1.3
 
  New_Asset_Value = Asset_Value * Current_Rate   '<- Compile Error
  
  MsgBox "Your new asset value is : " & New_Asset_Value

End Sub

Notice the first line of the module states “Option Explicit”. This is before any subroutine code.

The first section of the Sub Calculate_Results() is three lines to declare the three variables I plan to use in my code.

I’m adding three specific words to my subroutine “vocabulary list” that allows me to use those words later.

What happens when I run this code?

[IMAGE] Compile Error

I get a compile error: “Variable not defined” which is Excel warning me that I made a spelling mistake with one of the variables.

This is not a bad thing. In fact it’s a very good thing because I’ve been alerted of the spelling mistake.

When I declared the variables, I didn’t declare one spelled “Curent_Rate”. With Option Explicit at the top of the code module, Excel doesn’t allow me to use a variable that hasn’t been declared.

After fixing the variable to “Current_Rate” I get the correct result:

[IMAGE] New Asset Value

I much prefer asset values above zero 😊

And I hope you can see why I recommend using Option Explicit.

Without Option Explicit we got an answer but it was wrong. With Option Explicit we were alerted to the mistake.

But it’s not just me who likes Option Explicit. Take a look at what other Excel experts say.

#3  – Why use Option Explicit? – 8 experts views

» Back to contents

For any practice there’s usually a trade-off and it’s the same with Option Explicit.

One upside of Option Explicit is that it forces the correct spelling for variable names so you don’t get unexpected or undetected errors from misspelled variable names.

One downside is the need to write more lines of VBA code to declare each variable before using it. This takes more time and effort.

So do you use it or do without?

To help answer this question I surveyed some Excel experts for opinions and found:

“7 out of 8 Excel experts recommend using Option Explicit”

[Note: in case of doubt… my survey didn’t use the scientific method 😊 and is based on anecdotal evidence I found by Google searches.]

 

Expert Opinion 1 = FOR

[IMAGE] S1 Logo

Spreadsheet1.com (Petros Chatzipantazis) – a professional VBA developer who learned his trade by serving big institutions in the demanding London Finance market… he has written about VBA Development best practices in a case study available here.

Quote from Petros’ article on best practices:

Option Explicit is missing from the above module. Without Option Explicit any typographical error in variable names results in a new variant-type variable being created by the compiler. This behavior may not cause an immediate runtime error, but it will almost certainly cause the Excel application to return incorrect results.”

Victor’s summary – if you don’t have Option Explicit turned on, Excel won’t catch any misspelled variables that would likely cause you to have incorrect results.

 

Expert Opinion 2 = FOR

[IMAGE] 5-excel-macro-mastery-logo

Excel Macro Mastery (Paul Kelly) – a professional VBA developer from Ireland who has turned into online Excel trainer… he has a detailed tutorial article on declaring VBA variables here.

Quote from Paul’s tutorial article:

“VBA does not require you to use the Dim Statement. However, not using the Dim statement is a poor practice and can lead to lots of problems. You can use a variable without first using the Dim statement. In this case the variable will automatically be a variant type.

This can lead to problems such as: 1. All variables are variants (see the Variant section for issues with this), and 2. Some variable errors will go undetected. Because of these problems it is good practice to make using Dim mandatory in our code. We do this by using the Option Explicit statement.

Victor’s summary – using Option Explicit encourages you to specify variable type (instead of just using the catch-all Variant type) and can help you detect misspelling errors.

 

Expert Opinion 3 = AGAINST

[IMAGE] 6-mr-excel-logo

Mr Excel (Bill Jelen) – a truly prolific Excel book author (with over 40 published titles)… he was featured in an interview on Excel.TV.

Quote from Excel.TV interview (25:00) where a pragmatic Mr Jelen defends his viewpoint:

“(Jordan Goldmeier) When I talk to you, you were hating on it, you were saying “I don’t have to do it”… so if you could kindly tell our audience (and remember this is a family show) why you don’t like Option Explicit

(MrExcel) The most important thing you can do in your code is not Option Explicit. The most important thing you can do in your code is make code that works, right? And you don’t need Option Explicit to make your code work. We’re not programmers, we’re accountants, we’re Excel people and we’re just flipping over into VBA because we have some horrible data cleansing problem that we need to fix that we don’t want to spend 42 hours typing those dates in. If we’re real programmers and we’re writing code, yes, go declare your variables and all that. But this is just – hey I got this horrible thing at work… I need to knock out 20 lines of code to make this work. And frankly I don’t know what variables I’m going to need so I’m not going to declare them upfront.”

Victor’s summary – if you’re knocking out a couple of lines of VBA to solve a quick problem, what’s the point of Option Explicit as long as the code works?

 

Expert Opinion 4 = FOR

[IMAGE] 7-option-explicit-logo

Option Explicit VBA (Jordan Goldmeier) – an Excel MVP and book author who consults on data analysis and visualizations… he was the one asking Mr Jelen (above) why he didn’t like to use Option Explicit.

Quote from Excel.TV interview (26:40) where Jordan explains his viewpoint:

“But… that said, the spelling thing is, for me, the spelling thing is huge because maybe I’m just bad at typing… you know you can also as you’re typing you can add it [dim] in and then move it to the front”

Quote from Excel.TV interview (28:23) where Jordan says more:

“So let me just say that’s true… but also not using Option Explicit is wrong that’s why I named my blog after it – Boo Yah! No… seriously though, OK I hear ya… here’s my other reason OK, this is a good one.

Because I watch people write this code when they make these on the fly variables, and they’ll first say “T” like for task, so like “T = something” and then they want another one so they write “TT” and then they’ll want another one so they’ll write “ZZ”… and you all know (I’m looking at you in the audience) you know you’ve done that.

So don’t do that… because you say it’s for you, and then you leave and then the other guy… well I guess that’s not your problem if the other guy has to deal with it… but think about it the other way that the other guy left and you got his code and you have no idea what any of it means, so Option Explicit and declaring your variables makes you think about them…”

Victor’s summary: use Option Explicit 1. to prevent misspelled variable names from messing up your code, and 2. to make you think about what you’re naming your variables so that your code is easier to read and maintain.

 

Expert Opinion 5 = FOR

[IMAGE] 8-excelcampus-logo

Excel Campus (Jon Acampora) – another Excel MVP and online trainer highly recommends Option Explicit.

Quote from Excel.TV interview where Jon explains his answer:

”Rick: Question three is from Chris Newman. Option explicit. Two camps. The Jordan Goldmeier camp and the Bill Jelen camp who says it is stupid. Are you Team Bill or Team Jordan?

Jon: I’m Team Jordan. I teach in the (Excel VBA) course a strong recommendation for option explicit. I think Bill (Jelen) secretly uses it too.

 

Expert Opinion 6 = FOR

[IMAGE] 9-spreadsheetguru-logo

TheSpreadsheetGuru (Chris Newman) – another Excel MVP and online trainer also likes Option Explicit. He wrote an article on what Dim means when writing VBA code.

Here’s an excerpt from the article by Chris:

”Do I have to use Dim? In short, no you do not have to declare your variables while coding in VBA.  Any undeclared variables are defaulted to a Variant variable type which basically means that VBA will cycle through all of the variable types and pick one that allows your code to run without error.  I DO NOT RECOMMEND DOING THIS AS A BEST PRACTICE!

Can I Force Myself To Dimension All My Variables? I’m so glad you asked!  If you type in the phrase Option Explicit at the very top of your module (before your first subroutine or function) you will be forced to dimension every single variable you create.  If you try to run your code without declaring a variable, the Visual Basic Editor will highlight the variable and give you the following error message – Variable not defined”

 

Expert Opinion 7 = FOR

[IMAGE] 10-moth-logo

MyOnlineTrainingHub (Philip and Mynda Treacy) – husband and wife team provide online training for Excel and other Microsoft products. Mynda is an Excel MVP.

Philip strongly recommends using Option Explicit in his tips for debugging VBA code:

”Option Explicit – I’m not sure why this isn’t a default really as using variables that aren’t declared can only lead to trouble…  (simple example)… This seems obvious in a simple example like this, but if you have dozens, or hundreds of lines of code, and you use variables without declaring them, it can lead to all sorts of problems.”

 

Expert Opinion 8 = FOR

[IMAGE] Launch Excel Logo

Launch Excel (Victor Chan – that’s me 😊) – obviously I’m recommending Option Explicit because I wrote this article to promote it as best practice.

To conclude, you can choose like Mr. Excel Bill Jelen to do without Option Explicit.

Or you can go with Spreadsheet1, Excel Macro Mastery, Option Explicit VBA, Excel Campus, The Spreadsheet Guru, My Online Training Hub, and Launch Excel by using Option Explicit.

#4  – What if you’re still not convinced?  – here’s my advice…

» Back to contents

I know what you might be thinking… why make such a big deal out of two words? “Option Explicit”

Sure you can catch misspellings, but the downside is you have to declare every variable or constant in your code which adds more lines to your code and takes more time to type.

Before you dismiss Option Explicit as something that’s “nice to have”… read my reasons for using Option Explicit and why I strongly recommend you use it:

Write code that’s easier to read – OK Option Explicit isn’t a magic bullet that suddenly makes your code easier to read. However it does help because it encourages you to think about declaring every variable and constant. Which means you can think of names that are relevant and helpful (such as Asset_Value instead of something less helpful like Z).

Organize your variables in one place – It’s common practice (though not necessary) to keep all your variable declarations at the top of your subroutines. It’s like having a special vocabulary list you can refer to when coding, which helps you track all your variable names. Again, Option Explicit doesn’t force you to put all your declarations at the top, but once you start using Option Explicit you’re probably going to stick with this convention because you’ll see it in so many other people’s VBA code.

Use the right data types – If you don’t declare your variables, you might accidentally store text in a variable that’s supposed to store numbers. Then much later in your code you try multiplying a number by that variable (which has text in it) and wonder why your code errors out with a “type mismatch”. By using Option Explicit, you get a run-time error “type mismatch” on the actual line where you first try to assign a text string to a number-typed variable and it’s easier to catch.

Help prevent spelling mistakes – This is a big one because spelling mistakes can cause unexpected and sometimes undetected calculation errors. Why take the risk? Trust me on this… if you use Option Explicit you’re saving yourself a lot of headaches later.

Bonus Tip for preventing spelling mistakes: One useful thing I like to do is use at least one uppercase letter for variable names (especially longer ones). When I type my code I do it in lowercase letters.  The Visual Basic Editor automatically capitalizes any variable name that’s been declared. So if I misspell a variable, the VBE doesn’t capitalize it and I know I made a spelling mistake. This only works if Option Explicit is turned on first.

#5  – How to turn on Option Explicit – step by step instructions

» Back to contents

So I’ve convinced you to try Option Explicit? Great!

You have two options to use it.

  1. Manual – You type “Option Explicit” at the top of every code module in the directives section, before you write any subroutines or functions.
  2. Automatic – You turn on an option so the VBA Editor always writes “Option Explicit” at the top of every new code module. This way you don’t worry about forgetting to type “Option Explicit”. Detailed steps are below.

For completeness… if you want to remove Option Explicit from any module, just delete “Option Explicit” from the top and you’re free again to conjure up new variables with total abandon!

Detailed Steps: automatically add Option Explicit

To turn on automatic Option Explicit follow these steps.

[IMAGE] Excel VBE Tools Options

Go to the VBA Editor (ALT + F11 from Excel) and click on the Tools menu. Select Options.

[IMAGE] 13-require-variable-declaration

Make sure the box for “Require Variable Declaration” is checked and click OK.

That’s it!

Now the VBA Editor automatically adds “Option Explicit” to every new code module.

[Note: this method doesn’t add “Option Explicit” to existing modules – you must do that yourself if you want to use Option Explicit for existing code.]

#6  – Recap – key points

» Back to contents

Here’s a quick recap of the main points to help your recall:

  • Spelling checker – Misspelled variables and constants can lead to unexpected VBA errors, some annoying and others disastrous. Adding “Option Explicit” to the top of code modules is like turning on spell checker for your variables and constants.
  • Other benefits of using “Option Explicit” – 1. Encourages you to write code that’s easier to read, 2. Encourages you to organize your variables in one place at the top of every subroutine, 3. Helps you to use the right data types in your VBA code.
  • How to use it –1. Manual – Type “Option Explicit” at the top of every code module, or 2. Automatic – Turn on the VBA Editor option that requires variable declaration.

#7  – References – further reading

» Back to contents

Here are articles for more info.

For fun here are some embarrassing spelling errors.

#9 – Over to you – what next?

» Back to contents

You just learned a highly valuable VBA pro tip on Option Explicit. If you start using it right now you’re going to thank me for years to come. You’re welcome!

[IMAGE] Victor Smile No BackgroundI’d like to ask for a little favor in return…

… I want you to share this article with anyone who could benefit, so that you can help me reach a wider audience. Email the link, embed it in forum posts and share it on social media.

Or if you’re not ready to share the article, I’d like you to join my email community. Sign up using the email form below and help build my email reach. It’s totally free to join and you can unsubscribe any time.

Finally, please comment below if you have a question or tip. I’ll do my best to answer questions and spread good tips! 🙂

Cheers,
Victor
(Launch Excel)

Victor Chan
Victor runs Launch Excel to help you master Excel and VBA. He has used Excel a lot since 2002. He's a Chartered Accountant (Fellow of the ICAEW) with a Masters in Manufacturing Engineering from the University of Cambridge... and has a passion for teaching others. Enjoy his tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recent Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.