Option Explicit Statement

[Guest]

I am building a database as I learn various functions and have used quite a
bit of code from this community and the general Microsoft website. I ran the
analyzer and for almost all of my forms and reports, I had the recommendation
to add an option explicit statement at the beginning of my code. However, I
don't really know where to put this and how to determine the variables. I
grew up with BASIC and Pascal training so I am somewhat familiar with code
but have no experience with VBA. Is there an article someone could point me
to that would further explain the adding of this statement?

 

[Ken Snell [MVP]]

Easiest way to have Option Explicit statement in your modules when they are
first created is to set the option in Visual Basic Editor.

Tools | Options | Editor tab

Check the box next to "Require Variable Declaration"


In already existing modules, go to the very top (where likely you'll see
Option Compare Database as the first line), enter a new line, and type
Option Explicit
on that new line.

 

['69 Camaro]

Hi.

I had the recommendation
to add an option explicit statement at the beginning of my code. However, I
don't really know where to put this

Is there an article someone could point me
to that would further explain the adding of this statement?

For the answers to these questions, please see Tom Wickerath's tip, "VB
Editor Option Settings," on the following Web page:

http://www.Access.QBuilt.com/html/gem_tips.html

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[PC Datasheet]

Look at "Option Explicit Statement" inthe Help file.

 

[John Vinson]

I am building a database as I learn various functions and have used quite a
bit of code from this community and the general Microsoft website. I ran the
analyzer and for almost all of my forms and reports, I had the recommendation
to add an option explicit statement at the beginning of my code. However, I
don't really know where to put this and how to determine the variables. I
grew up with BASIC and Pascal training so I am somewhat familiar with code
but have no experience with VBA. Is there an article someone could point me
to that would further explain the adding of this statement?

Put it before the first Sub or Function line, at the very first line
of the module.

The syntax could not be easier:

Option Explicit

alone on a line.

This should be done routinely; it ensures that all variables used must
appear in a Dim statement, and thereby prevents undimensioned variant
variables being created inadvertantly by a simple typo.

John W. Vinson[MVP]

 

[Albert D. Kallal]

I am building a database as I learn various functions and have used quite a
bit of code from this community and the general Microsoft website. I ran
the
analyzer and for almost all of my forms and reports, I had the
recommendation
to add an option explicit statement at the beginning of my code.

Yes, that is a good recommends. Placing the compiler directive

Option Explicit

At the begging of a standard module, or even a module attached to a form
means that all variables MUST BE defined before they are used. In my humble
option, this is an absolute requirement for software development, as then
the compiler can catch miss-spelled variable names.

. I grew up with BASIC and Pascal training so I am somewhat familiar with
code

Excellent, I also grew up with that stuff. You note that VBA does support
the older legacy style basic (in fact, VBA has it roots in QuickBasic).

The following code does in fact still compile and work in VBA

PUBLIC SUB TEST1

10 FOR I = 1 TO 10
20 J = L + 1
30 NEXT I
40 MSGBOX "VALUE OF J IS " & J

END SUB

Of course,in the above code I accidentally used "L" on line 20. (note that
line numbers in VBA are optional..and we don't use them anymore...*except*
for error handling, and the goto statement!!

If the module for the above code has a option explicit, then the above would
have to be written as:

PUBLIC SUB TEST1

DIM I AS INTEGER
DIM J AS INTEGER

10 FOR I = 1 TO 10
20 J = L + 1
30 NEXT I
40 MSGBOX "VALUE OF J IS " & J

END SUB

If we compile the above..we will see that L is not defined. So, option
explicit makes the VB compiler behave more like stronger type languages
(Pascal for example). The use of this option means that you must declare all
variable names that you use (just like you have to in Pascal). This approach
also means that the old BASIC convention of using a $ for string variables,
and # for number etc. is not used anymore.

but have no experience with VBA. Is there an article someone could point
me
to that would further explain the adding of this statement?

The following article is a bid old..but still 100% applicable today. You
will find this free on-line book for ms-access is most useful. While the
following is for access 97, the code still works today, and has a very nice
introduction to writing code in VBA.

http://www.microsoft.com/accessdev/articles/bapp97/toc.htm


Good luck..and best of the season to you...