When It Makes Sense to Use Option Explicit

February 17, 2018 - by Bill Jelen

It seems that I have caused some angst over the years because of my controversial stance that Option Explicit is not useful in VBA. I was reminded of this recently when I did a Skype session at Excel Weekend of Brazil. I've written a book on Excel VBA for QUE. That book has been translated to Portuguese for the Brazil market. So, apparently, I've taught the fine people of Brasil about Excel VBA for 15 years. Now that I am 15 years older and wiser, I will concede that there might be good reasons to use Option Explicit.

At Notre Dame, I took business courses with a heavy concentration in programming. Through a weird set of circumstances, I took every programming course available at ND from both the Engineering and Business schools. I learned Fortran, Pascal, Assembler, and COBOL. I loved programming. Those programming courses rescued my GPA and kept me from being thrown out. Back in the day, our professors told us that we had to plan out our program and hand-draw flowcharts before we started coding. I was a rebel. I would write the program first and then draw the flowchart after I was done.

I spent the first 18 months of my career writing COBOL programs. But then I transferred to Finance and spent ten years working in Finance and Accounting. I was doing mostly accounting work, but when I needed to write some code, I knew enough to write some code.

I programmed macros in Lotus 1-2-3 and then started writing VBA Macros after we switched to Excel in 1995. I was still rebellious at that point: declaring my variables ahead of time seemed too much like drawing a flowchart before writing the code. I am never one to plan ahead. Dive in. Start coding. If you need a new variable somewhere along the way, simply create the variable.

I was a rebel, writing the code first and drawing the flowchart later Declaring variables, I argued, is for formal programmers. If you are working in Accounting and are simply knocking out 20 lines of code, there is no reason to declare your variables ahead of time.

My stance on that has gotten me into some friendly arguments with other Excel experts. Jordan Goldmeier. Bob Phillips. Cristiano Galvão.

First off, I always have recommended declaring your Object Variables. In the code below, WSD is declared as a worksheet and assigned using the Set command. If you declare the object variable, you can browse all properties and methods by typing the variable followed by a period.

By not using Option Explicit, you run the risk of spelling errors. In the code below, a variable called FinalRow is created. In the next line, I likely meant to loop from 2 to FinalRow, but I mistyped the variable as Fina1Row.

Without Option Explicit, someone may not realize the typo is there. The FinalRow variable might contain 100. But the code inside the loop will not execute because the Fina1Row variable was never initialized and will contain a zero.

To prevent the frustration from debugging code with potential typing errors, you can open Tools, Options in the VBA menu. Choose the Require Variable Declaration box shown below.

All future modules will start with a line that says Option Explicit. You will have to add new lines at the top of the macro defining the FinalRow and i variables. Excel won't tell you about the misspelling until you actually try to run the macro. They will then alert you that the Variable Is Not Defined.

So, it is your call. If you don't mind planning ahead and declaring all of your variables, you will have the safety net of VBA telling you if you typed a variable incorrectly. For me, I prefer the thrill of debugging the code one line at a time and finding my typing mistakes by myself. But if you don't feel like living dangerously, feel free to enable Option Explicit.

I am not really sure if my actions involved Steps 5, 8, or 9 of the 12 steps. But if anyone spent extra time debugging their code because of a typing error, I am sorry for causing you that pain.

Every Saturday, I will discuss one of my bad habits in Excel and discuss why maybe you should do what I say instead of do what I do.

Title Photo: Luca Bravo