New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Option Explicit - And why to use it

Free Excel Tips and Tutorials

Option Explicit - And why to use it

Postby Sisyphus » Fri Feb 03, 2012 4:56 am

The 'Explicit' option in Visual Basic requires that variables be declared in a Dim, Private, Public, Static or Const statement before they can be used in the code. This sounds like unnecessary effort when one considers that VB can work perfectly well without such declarations. It automatically declares each variable as it is being introduced. Why should you waste time and space in declarations whereas you already paid good money to let VB do the job? :?

Consider that you spend about 20% max of your time programming and a minimum 80% trouble-shooting. It follows that substantial time savings are possible only by avoiding trouble to shoot, and that is the light in which to look at the Explicit option. Misspelled variable names just don't happen any more because VB highlights them the very first time you try to run your code. You also gain the capability to control capitalization and employ it as an aid. You declare your variable as 'LastRow', for example, but as you program you always write 'lastrow'. VB will correct the capitalization. If it doesn't you have been served warning that you didn't spell the variable's name as declared.

You also gain control over the type of variable that is being declared. If you don't declare your variables Excel will do it using the type it sees fit which, most of the time, is Variant - Excel's default. Variants take up much more memory than other types and are, in consequence, slower to handle. A variant storing the number 1 takes up 16 bytes of memory. An integer type of variable only needs 2. However, if you don't know which type to declare you can still use Option Explicit for its other benefits and let Excel make the choice of data type by declaring the variable's name without specifying a type.

The Optional Explicit statement must be placed at the top of each code module, before all procedures. You can set your VB to do this automatically:
- In the VB Editor window select Tools/Options.
- On the Editor tab look for 'Code Settings'.
- Check the box for 'Require Variable Declaration'.

See? Now you are getting your money's worth of automation! :D
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 21 guests