Option Explicit - Declarations

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
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

Well-known Member
Joined
Jun 23, 2006
Messages
6,996
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

Code:
Public cell As Range
This will save you having to declare everything in each macro.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
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.
Code:
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
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
Code:
Public myVariable as Range
Public LR as Long
Public Test as Boolean
This goes at the top of a regular module.

lenze
 

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
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
Code:
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

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
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

Board Regular
Joined
Mar 23, 2009
Messages
122
Thanks for the answer (and advice)

One further question on the same subject...

Previously my code was as follows (without Option Explicit)

Code:
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...
Code:
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...
Code:
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
Code:
ResourceID = WorksheetFunction.Index(Range("ResourceTable"), r, 1)
which kind of defeats the object anyway!

Am I doing something basic wrong here?

Thanks
 

Hermanito

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
Change this
ResourceTable = Range("ResourceTable")
into this
Set ResourceTable = Range("ResourceTable")
 

Forum statistics

Threads
1,082,253
Messages
5,364,052
Members
400,776
Latest member
JimmyLee

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top