Dim

[Guest]

What is the primary benfit of a Dim statement -- if the variable does not
cause any name or data type confusion.

I understand that it is to declare a variable. However, I seem to be able to
run macros without any problem no matter if I declare the varaiable or do not.

For instance, I use For Next loops similar to:

Dim intI as integer
For intI = 1 to 25
................
................
Next intI

but these loops work the same weither I declare the variable or not.

Any thoughts?

Thank You,

 

[Tom Ogilvy]

Dim can speed up your code although I think you would need a lot of
variables for it to be noticeable.

The big advantage is if you put

Option Explicit

at the top of your code.

then if you do

Dim intI as integer
Dim iStep as Integer
iStep = 5
For intI = 1 to 25
if intI mod iStp then
................
................
Next intI

where you have a typo in iStp, will raise an error. Otherwise, the error
could be overlooked in complex code and the code could give you wrong
answers.

Option explicit requires that all variables be declared. (Dim' d)

 

[Shatin]

Read this:

http://www.cpearson.com/excel/DeclaringVariables.htm

 

[Jim Rech]

The main reason to Dim variables, in conjunction with Option Explicit which
requires variables to be declared, is to prevent you from accidentally
creating 'new' variables via making typos. If you dim MyRange as Range and
later start using MyRg you'll get a compiler warning.

Other advantages include being able to set the scope of the variable
(global, module, procedural) and to use the most efficient data type -
undeclared variables are always variants which take more memory and are
'slower' than more specific variables like integers.

And don't forget Intellisense. When you declare a MyRange as Range and then
type "MyRange." VB pops up the methods and properties of Range objects which
makes your programming easier and less error prone.

--
Jim
| What is the primary benfit of a Dim statement -- if the variable does not
| cause any name or data type confusion.
|
| I understand that it is to declare a variable. However, I seem to be able
to
| run macros without any problem no matter if I declare the varaiable or do
not.
|
| For instance, I use For Next loops similar to:
|
| Dim intI as integer
| For intI = 1 to 25
| ................
| ................
| Next intI
|
| but these loops work the same weither I declare the variable or not.
|
| Any thoughts?
|
| Thank You,
|
|
|
|

 

[Guest]

Thank you all,

That helps alot!!

 

[Ron Rosenfeld]

What is the primary benfit of a Dim statement -- if the variable does not
cause any name or data type confusion.

I understand that it is to declare a variable. However, I seem to be able to
run macros without any problem no matter if I declare the varaiable or do not.

For instance, I use For Next loops similar to:

Dim intI as integer
For intI = 1 to 25
................
................
Next intI

but these loops work the same weither I declare the variable or not.

Any thoughts?

Thank You,

The reason *you* can use it either way, in your code, is because you do not
have the Option Explicit statement at the beginning.

See http://www.cpearson.com/excel/DeclaringVariables.htm for a discussion as to
the benefits of using the Option Explicit statement (and declaring variables).


--ron

 

[Dave Peterson]

And just to add to Jim's intellisense point.

If you have a variable declared as:

Dim TJHVariableForKeepingTrackOfOverDueBooks as long

you can type

tjh
and hit ctrl-spacebar
if what you typed was unique, the VBE will complete the variable name.

If it isn't unique enough, you'll see a list to choose from.

And another nice thing.

If you mix uppercase with lower case in you Dim statement, you can type in lower
case. Then when you finish that line of code (and hit enter), the VBE will
change the case for your -- but only if you typed it correctly. (Still
lowercase--then you made a typo!)

 

[Guest]

Ok, now that I realize that I should be declaring all variables.

Is there an easy way to determine what type of variable to assign. For
instance, I am trying to declare a variable for a SheetName such as:

ShNM = Activesheet.Name

--Used to reference a worksheet at a later time -- What type should I use
here?

Also,

SelAd = Selection.Address

--Used to reference a cell at a later time --What type should I use here? I
have tried several and Variant is the only one that works. Is this really the
best?

 

[Jim Rech]

Both Activesheet.Name and Selection.Address (assuming a cell/range is
selected, not, say, a button) are strings.

But re "Used to reference a worksheet at a later time" it is simpler to do
this:

Dim WS as Worksheet
Set WS = ActiveSheet
.....and later:
WS.Activate

than to do what you're thinking of:
Dim WSName as String
WSName = ActiveSheet.Name
...and later:
Worksheets(WSName).Activate

--
Jim
| Ok, now that I realize that I should be declaring all variables.
|
| Is there an easy way to determine what type of variable to assign. For
| instance, I am trying to declare a variable for a SheetName such as:
|
| ShNM = Activesheet.Name
|
| --Used to reference a worksheet at a later time -- What type should I use
| here?
|
| Also,
|
| SelAd = Selection.Address
|
| --Used to reference a cell at a later time --What type should I use here?
I
| have tried several and Variant is the only one that works. Is this really
the
| best?
|
|
|
|
| "Ron Rosenfeld" wrote:
|
| > On Mon, 10 Oct 2005 12:15:02 -0700, tjh <[email protected]>
wrote:
| >
| > >What is the primary benfit of a Dim statement -- if the variable does
not
| > >cause any name or data type confusion.
| > >
| > >I understand that it is to declare a variable. However, I seem to be
able to
| > >run macros without any problem no matter if I declare the varaiable or
do not.
| > >
| > >For instance, I use For Next loops similar to:
| > >
| > >Dim intI as integer
| > >For intI = 1 to 25
| > > ................
| > > ................
| > >Next intI
| > >
| > >but these loops work the same weither I declare the variable or not.
| > >
| > >Any thoughts?
| > >
| > >Thank You,
| > >
| > >
| > >
| >
| > The reason *you* can use it either way, in your code, is because you do
not
| > have the Option Explicit statement at the beginning.
| >
| > See http://www.cpearson.com/excel/DeclaringVariables.htm for a
discussion as to
| > the benefits of using the Option Explicit statement (and declaring
variables).
| >
| >
| > --ron
| >

 

[Tom Ogilvy]

Dim ShNM as String

--
Regards,
Tom Ogilvy

Ok, now that I realize that I should be declaring all variables.

Is there an easy way to determine what type of variable to assign. For
instance, I am trying to declare a variable for a SheetName such as:

ShNM = Activesheet.Name

--Used to reference a worksheet at a later time -- What type should I use
here?

Also,

SelAd = Selection.Address

--Used to reference a cell at a later time --What type should I use here? I
have tried several and Variant is the only one that works. Is this really the
best?

 

[Ron Rosenfeld]

Ok, now that I realize that I should be declaring all variables.

Is there an easy way to determine what type of variable to assign. For
instance, I am trying to declare a variable for a SheetName such as:

ShNM = Activesheet.Name

--Used to reference a worksheet at a later time -- What type should I use
here?

Also,

SelAd = Selection.Address

--Used to reference a cell at a later time --What type should I use here? I
have tried several and Variant is the only one that works. Is this really the
best?

There may be more information on Chip's web site regarding efficient use of
variables. Variant will usually work.

However, in your examples above, both Name and Address return strings, so you
could Dim ShNM as String.


--ron