Option Explicit

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
When I insert a new module in the VB Editor, I get Option Explicit as the very first line, before I have input anything. Does anyone know why?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

SamS

Well-known Member
Joined
Feb 17, 2002
Messages
542
Aaron got Tools>Options>Editor and untick the Require Variable Declaration box.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
Well wait a minute, let’s look at what the benefits are of the Option Explicit statement before just advising that it be deselected as a default setting.

Option Explicit at the top of a module forces every variable to be declared. There are several reasons why this is a good idea to maintain:

- Spelling errors are identified.
- Your variables can be properly declared by you, not Excel, to have the appropriate memory and system resources assigned to accommodate them. Excel will assign Variant as the variable default type for undeclared variables, not the most efficient use of resources.
- Confusion is avoided if a variable is the same name as a property or method.

Consider the following code:

Sub ShowData()
FindData = InputBox(“Please enter your search term:”)
If FineData = “” Then Exit Sub
Range(“A1”).Value = FindData
End Sub

Without Option Explicit to guard against spelling errors, cell A1 will never have a value returned into it because of the spelling inconsistency between FindData (line 2) and FineData (line 3). The macro will always exit because FineData will always be thought of as empty.

This kind of mistake might be noticeable with a macro this size, but if it is part of a larger procedure these mistakes are hard to identify. Why take the chance?

I’d recommend keeping Option Explicit as the default statement; it forces better code writing practice, and helps avoid coding errors.
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
Yes, Tom is right.
This should be one of the 1st commandments of VB Programming...always use this for the reasons that Tom has highlighted.
 

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
Well it looks as if someone before me turned the Option on, and I will take the advice of Tom and leave it on. Sorry Sam. Thank you all for your help.
 

Forum statistics

Threads
1,143,709
Messages
5,720,389
Members
422,282
Latest member
psunith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top