Results 1 to 5 of 5

Option Explicit how to turn it off

This is a discussion on Option Explicit how to turn it off within the Excel Questions forums, part of the Question Forums category; When I record a new macro my first line always comes up with Option Explicit. I know it is something ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    443

    Default Option Explicit how to turn it off

    When I record a new macro my first line always comes up with Option Explicit.

    I know it is something I turned on a long time ago but I cant remember where it is or how to turn it off.

  2. #2
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default Re: Option Explicit how to turn it off

    REQUIRE VARIABLE DELCARATION in the OPTIONS menu in VB.
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default Re: Option Explicit how to turn it off

    Before you go without the Option Explicit statement, take a moment to carefully consider that decision.

    The term "Option Explicit" means that the VBA code author must define the names of all the variables being used or referred to in the code. Not doing so is called "implicit variable declaration", where you can name a variable without explicitly defining it.

    Why should you explicitly declare variables?

    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, the most inefficient 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?

    Keeping Option Explicit as the default statement is highly recommended; it forces better code writing practice, and helps avoid coding errors.

  4. #4
    New Member
    Join Date
    Oct 2013
    Posts
    2

    Talking Re: Option Explicit how to turn it off

    Thank you Tom for the comprehensive explanation of Option Explicit! That is probably the most comprehensive answer I've ever seen, and the question wasn't even asked in this thread! That really helped me, especially with the benefits and the example to illustrate.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,915

    Default Re: Option Explicit how to turn it off

    Quote Originally Posted by Tom Urtis View Post
    Why should you explicitly declare variables?

    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.

    Consider the following code:

    Sub ShowData()
    FindData = InputBox("Please enter your search term:")
    If FineData = "" Then Exit Sub
    Range("A1").Value = FindData
    End Sub
    Just to follow up on Tom's explanation, take a look a the following variable names...

    Text10
    Text1O
    Textl0
    TextlO

    You are probably asking.... why is he showing us that given the differences are so obvious? Yes, in the font used for this forum, the difference is quite clear, but do this experiment... copy/paste those four lines into a code window (the Immediate Window will do). In the default font used by the VB editor, the differences in appearance are much harder to see, but you can because they are displayed next to each other and isolated from other code. Now try to image any one of them in a long code statement with other code around it. Do you really think you could spot the error when examining the code? If you think you can, then you are only fooling yourself. Using Option Explicit solves the problem for you, if you Dim Text10, then VB will flag your erroneous use of any of the other three. Trust me... that is a HUGE benefit, especially if this code is for use in a production environment where you would want your code to always be producing the correct answer.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com