What does Option Explicit do in VBA?

David Meego - Click for blog homepageMy colleague asked me for assistance with a Visual Basic for Applications (VBA) case yesterday.

The VBA code was being used to customise a report and use ActiveX Data Objects (ADO) to read a value from the SQL Server data and display it on the report.  The code was similar to the examples in the Using ADO with VBA with Report Writer post, except that it did not work and displayed a VBA runtime error when the report was run.

Looking at the code, I noticed that there were no Dim statements for the variable declaration needed for the ADO connection, command and result set variables.  I was about to add the statements from the example, but I decided to demonstrate to my colleague why requiring variable declaration is important.

So from the Visual Basic Editor, we went to Tools >> Options and made sure Require Variable Declaration was selected. See the screenshot below: 

VBA Options

What the Require Variable Declaration option actually does is to add the command Option Explicit to any new VBA scripts created. As the script in our support case had already been created, we manually added the command Option Explicit to the top of our script.

This setting is off by default and so needs to be turned on manually. The thinking was that it was easier for people to learn Visual Basic if they did not need to declare variables explicitly and to let the system declare them implicitly for you. This might be the case for a very short program with one or two variables, but add soon as the code gets more complex using implicit variable declaration becomes an issue.

The problem is when you make a typo when writing your code. With implicit variable declaration, the system will just create a new variable with the mistyped name and you will spend hours trying to work out why your code is not working. You will read the line again and again and your brain will see what you meant to type and not what you actually did type.  With explicit variable declaration, the system with throw a compiler error and you will be able to fix the typo straight away.

So back to our support case, once we added Option Explicit the compiler told us that the cn, rst, cmd and sqlstring variables had not been declared.  So we copied in the standard Dim statements to the top of the script.  The compiler then told us that it did not understand what ADODB was, so we added a reference to the Microsoft ActiveX Data Objects 2.X library using Tools >> References. Then the compiler highlighted one of those typos I mentioned with an AddressCode field being written as AddresCode. Finally, the code compiled and when tested, ran without errors.

All those issues with variables not being defined to the correct data types, not having the ADO reference added and the typo were found quickly by the compiler once we required variable declaration.  Without variable declaration, we only had an obscure error message when we tried to execute the code.

This is why the first action I always take on a system when working with VBA is to ensure that the Require Variable Declaration option is checked.

David