Hi,

I always thought that Variables Live till the end of the Sub / Function
and get deferenced at the end of the procedure.

But I saw this tip on a website, that within a sub, dereferences a
locally created variable to Nothing.

I'm now in doubt if I should be doing that in my procedures, since I
have some heavy variables too

And any suggestion for maintaining global constants in VBA, I have
created these strings that wont die till the Application closes

Thanks,
Megha

The life time of variables is the scope of the procedure (sub/function) that
they were declared in or the module or project depending on the declaration
for global variables.

For object variables like worksheet, workbook, range etc (not the standard
variables like integer, long, string etc.) it is best to dereference the
variable after you have finished using it as in:

dim ws as worksheet

set ws = activesheet
ws.range("A1") = "A"
set ws = nothing

Excel has some problems with leaving the references active.

HTHs.

"vmegha" wrote:

Hi,

I always thought that Variables Live till the end of the Sub / Function
and get deferenced at the end of the procedure.

But I saw this tip on a website, that within a sub, dereferences a
locally created variable to Nothing.

I'm now in doubt if I should be doing that in my procedures, since I
have some heavy variables too

And any suggestion for maintaining global constants in VBA, I have
created these strings that wont die till the Application closes

Thanks,
Megha

This is a debatable topic as I have mentioned before. Matt Curland, an MS
developer has some interesting things to say about it at
http://tinyurl.com/cq95p. However, there are cases, automation comes to
mind, where not clearing down the objects is suspected of being the reason
why Excel sometimes fails to quit.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Martin Fishlock" wrote in
message ...
The life time of variables is the scope of the procedure (sub/function)
that
they were declared in or the module or project depending on the
declaration
for global variables.

For object variables like worksheet, workbook, range etc (not the standard
variables like integer, long, string etc.) it is best to dereference the
variable after you have finished using it as in:

dim ws as worksheet

set ws = activesheet
ws.range("A1") = "A"
set ws = nothing

Excel has some problems with leaving the references active.

HTHs.

"vmegha" wrote:

Hi,

I always thought that Variables Live till the end of the Sub / Function
and get deferenced at the end of the procedure.

But I saw this tip on a website, that within a sub, dereferences a
locally created variable to Nothing.

I'm now in doubt if I should be doing that in my procedures, since I
have some heavy variables too

And any suggestion for maintaining global constants in VBA, I have
created these strings that wont die till the Application closes

Thanks,
Megha

Variables do live till the end of a sub/function, that is true. But if
you assign an object with the set statement it is a bit a different
situation. Every object has a reference counter and bascially counts
how many variables reference this object. When you use the set
statement, it will increase the Ref counter and when you use Nothing,
it will decrease the Ref counter. When the Ref counter is Zero, the
object will be cleaned up. So, yes, set every variable to Nothing, if
you assigned an object to it via the "Set" command.

Global string constants, I think that is ok, have a few of them myself
:-)

Cheers

Remy Blaettler
http://www.collaboral.com

Thanks all, I guess it's just safer to dereference the objects... I've
created.

Well it certainly doesn't hurt.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vmegha" wrote in message
oups.com...
Thanks all, I guess it's just safer to dereference the objects... I've
created.

Well it certainly doesn't hurt.

Contradicting your own post?

Setting them to Nothing
yourself is a waste of code: VB has to check if they are Nothing anyway and
always generates the code the code to release them, so you're just
duplicating existing code.

Also, unneeded teardown code
obscures necessary teardown code buy burying it in a bunch of noise, which
makes it harder to maintain the real teardown code.

As to Remy's comment, has he ever written a strictly Excel internal vba
procedure that won't let Excel close because an object reference is
retained. I grant that his comment is correct for C++, but in VBA, VBA
handles that for the coder. If referencing automation, then the problem
with automation is not with Set statements, but with the creation of
implicit references that can not be released.

--
Regards,
Tom Ogilvy




"Bob Phillips" wrote in message
...


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vmegha" wrote in message
oups.com...
Thanks all, I guess it's just safer to dereference the objects... I've
created.

Life and learn...
But yeah, too much C++ background. Glad C# is here :-)