Option Explicit - Declarations

[ElvisSteel]

Hi,
Several people on this board have recommended that I alway use Option Explicit in my VB code (via the Tools, Options, Require Variable Declaration option)

Firstly, what is the thinking behind this (or what are the implications of not doing this?)

Secondly, When I have a lot of code, I tend to use multiple Modules to group macros together under a common theme. Often I use the same variables in multiple modules, and is there a way of "globally" declaring a variable (perhaps in a single module that contains only these declarations)

These probably seem like dumb questions, but I am quite happy to convert my code to work this way, but I want to ensure that I do this in the most efficient way.

Thanks

Steve

 

[dave3009]

Hi Steve

The option explicit will ensure you declare everything to save time in the decision making of VBA (or something similar)

If you want to use global variables then have a module and declare them as so

Public cell As Range

This will save you having to declare everything in each macro.

 

[JackDanIce]

Hi,

I'm not too sure about this either so getting myself into this thread to hear the answers. At a guess (and for what it's worth):

The option explicit is to prevent duplicate variables being accidentally used in the same module and I think its recommended as good programming technique to make you think about the type of variable you are using (e.g. Dim x as Long vs Dim x as Integer, why one or the other etc)

The second part, yes you can declare global variables. You declare them as Public, before the Sub line e.g.

Public iName As String
Sub myMacro()
etc

And you should be able to pass these from module to module. But I don't know much more than this is intrigued to hear what other people have to say.

Jack

 

[ElvisSteel]

Thanks Dave

 

[lenze]

Not dumb questions at all. Option Explicit simply protects you from making typos in your code. Say you declare a variable "myValue" but late in your code you make a typo and use "myVlaue". Without OE, VBA will simply assume it's a new variable and, not updating "myValue", go on. So later in your code, when Excel looks for myValue, it will use the wrong value.
As for using a same variable in different macros, you can declare it as Public

Public myVariable as Range
Public LR as Long
Public Test as Boolean

This goes at the top of a regular module.

lenze

 

[ElvisSteel]

lenze,

Thanks for your prompt response.
I have one further question...

How do type-declaration characters work?

I have looked in Excel Help, but am still a bit confused.

Taking the $ as an example, this is the type-declaration character for a String.
If I have a statement

Public MyString as String

I don't appear to have to refer to MyString as MyString$, so where do I have to use these type-declaration characters

Thanks

Steve

 

[Hermanito]

The type declaration characters are merely a shorthand...

so you can write
Dim MyString$
instead of
Dim MyString As String
and afterwards in code just refer to MyString

or
Dim MyDouble#
instead of
Dim MyDouble as Double

I don't know the other ones by heart

My advice is to never ever use them, it only confuses and since many people have no clue what they're about it doesn't help making your code more readable, on the contrary...

 

[ElvisSteel]

Thanks for the answer (and advice)

One further question on the same subject...

Previously my code was as follows (without Option Explicit)

ResourceTable = Range("ResourceTable")
For r = 1 To 75
  ResourceID = WorksheetFunction.Index(ResourceTable, r, 1)
  ResourceName = WorksheetFunction.Index(ResourceTable, r, 2)
  etc etc
Next r

This worked fine

Now I have Option Explicit and have declared the variables separately and I get the following errors...

ResourceTable = Range("ResourceTable")

causes error: "Object Variable or Block variable not set"

If I comment out ResourceTable = Range("ResourceTable") it gets past this OK, but then gets an error on...

ResourceID = WorksheetFunction.Index(ResourceTable, r, 1)

error is "Invalid Procedure call or argument"

Now I have declared the range, it appears I do not have to initialise the variable, but the WroksheetFunction doesn't work unless I specifically change it to

ResourceID = WorksheetFunction.Index(Range("ResourceTable"), r, 1)

which kind of defeats the object anyway!

Am I doing something basic wrong here?

Thanks

 

[Jon von der Heyden]

I think this article is worth a read...

 

[Hermanito]

Change this
ResourceTable = Range("ResourceTable")
into this
Set ResourceTable = Range("ResourceTable")