Option explicit

[tactps]

I have seen the line "Option Explicit" in many macros, and have looked in the VBA help files, but do not understand what this does.

Can anyone explain to me when and why I should use it (it's advantages and reasons for it)?

 

[Nimrod]

Hello tactps:
It forces you to declare all your variables . Try to use one that hasn't been and you will recieve an error. Though if you listen to the "good" programmers they will insist on it's use... oh well sigh .... that's what I get for being in the Nimrod camp :wink:

Personally I don't like it much and don't use it. However another option I am quite fond of is Opton Compare Text .

 

[tactps]

Thanks Nim,

If I read that correctly, that means that it will make my macros more efficient as I am likely to limit the variables to Long, Byte, String etc.

That being the case, it is never required, but generally a good idea.

Is that right?

 

[SydneyGeek]

Option Explicit is all about declaring variables, which is always good programming practice. You set it in the VBE environment by going to Tools | Options | Edit, and checking the option for Require Variable Declaration; after you do this, all new modules will have Option Explicit at the top. You can also insert it into existing modules.

As for what it does...
Say you have the variable, StartDate, which you declare with
Dim StartDate As Date
Later on in your code, you insert a typo -- StarDate
Without Option Explicit, VBA will happily create a new variable called StarDate, use it in your code (and give it a value of 0), thus stuffing up your logic. In large routines / modules, this is a huge pain to debug.
With Option Explicit, the code won't run. It goes into Debug mode, highlights the rogue variable and lets you correct the typo. Much easier on the hair follicles.

Denis

 

[Nimrod]

make my macros more efficient

No it won't make them more efficient. It just forces you to declare all your variables. By forcing you to do this there is no chance of mis-typing a variable. So it's a way of making sure you don't do any typo's.

If you don't declare any variables they all become variant by default ... this is not suppose do be as effecient but that's purely theory. I've written code that looped thru 60000 records 100 times and seen less then of .01 second difference !

IN OTHER WORDS .. it just prevents you from making typo's

 

[tactps]

Ah... now I understand and it sounds like good practice.

Nim, Geek - thanks for getting me one step closer to being half-decent!

Have a good day.

 

[Tazguy37]

make my macros more efficient

No it won't make them more efficient. It just forces you to declare all your variables. By forcing you to do this there is no chance of mis-typing a variable. So it's a way of making sure you don't do any typo's.

If you don't declare any variables they all become variant by default ... this is not suppose do be as effecient but that's purely theory. I've written code that looped thru 60000 records 100 times and seen less then of .01 second difference !

IN OTHER WORDS .. it just prevents you from making typo's

If you declare all your variables as Variant, you'll use 16 bytes per declaration. If (for example) all those varables could have been declared as Double instead, you'd save 8 bytes of memory per variable.

I agree that, in terms of time to execute code, there's very little difference (at least in code I tested) between the two. But in terms of efficiency, I'd have to disagree and say that memory usage has to factor in there somewhere. Wouldn't you agree? Semantics, I know, but still....

 

[tactps]

Agreed, from a technical perspective, but I doubt it would make significant time savings in most macros.

Thanks you all again.