Good Practice

[Ardus Petus]

In this and other Excel NGs, I see quite often VBA lines like:

sub Foo()
dim myRng as Range
dim myRS as ADODB.Recordset
....
set myRng = Nothing
set myRS = Nothing
End Sub

Is this setting objects to Nothing really useful for local variables that
will be automatically destroyed at the end of the Sub.

TIA

--
AP

[K Dales]

In other words, is it necessary? No. Is it good practice? Guess that
depends on how a person defines good practice.

I learned programming a long time ago, when you had to pay attention to
every little detail because the system did not do much for you. Probably
leads to overkill in my coding these days. But I still see some value in
"completeness" in coding and if I create an object variable I want to destroy
it. I don't want to rely on the system to do that. Funny things can keep
the system from doing what it is supposed to do, sometimes. And we wonder
about things like memory leaks, etc... All of these stem from sloppy coding
(not necessarily yours; often the people who coded the system). So for me
"good practice" is to do everything explicitly whenever possible. But others
may have different ideas. After all, some don't even Dim their variables.
--
- K Dales


"Ardus Petus" wrote:

> In this and other Excel NGs, I see quite often VBA lines like:
>
> sub Foo()
> dim myRng as Range
> dim myRS as ADODB.Recordset
> ....
> set myRng = Nothing
> set myRS = Nothing
> End Sub
>
> Is this setting objects to Nothing really useful for local variables that
> will be automatically destroyed at the end of the Sub.
>
> TIA
>
> --
> AP
>
>
>

[Ed]

As a Good Practice, yes. For ranges, maybe not necessary, until you get
into more complicated coding that runs across several Subs - then things can
get exciting! But if you're in the habit of destroying all objects, then
you'll probably do it for file and application objects, too - leave _those_
hanging around, and life gets downright annoying!

Ed

"Ardus Petus" <[email protected]> wrote in message
news:%[email protected]...
> In this and other Excel NGs, I see quite often VBA lines like:
>
> sub Foo()
> dim myRng as Range
> dim myRS as ADODB.Recordset
> ...
> set myRng = Nothing
> set myRS = Nothing
> End Sub
>
> Is this setting objects to Nothing really useful for local variables that
> will be automatically destroyed at the end of the Sub.
>
> TIA
>
> --
> AP
>
>

[Chip Pearson]

If the object is declared within the sub, there is no reason to
set it to Nothing at the end of the sub. VBA will destroy the
object when the sub ends.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Ardus Petus" <[email protected]> wrote in message
news:%[email protected]...
> In this and other Excel NGs, I see quite often VBA lines like:
>
> sub Foo()
> dim myRng as Range
> dim myRS as ADODB.Recordset
> ...
> set myRng = Nothing
> set myRS = Nothing
> End Sub
>
> Is this setting objects to Nothing really useful for local
> variables that
> will be automatically destroyed at the end of the Sub.
>
> TIA
>
> --
> AP
>
>

[Ardus Petus]

This is precisely what I thought.

Thanks,
--
AP

"Chip Pearson" <[email protected]> a écrit dans le message de
news:[email protected]...
> If the object is declared within the sub, there is no reason to
> set it to Nothing at the end of the sub. VBA will destroy the
> object when the sub ends.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
> "Ardus Petus" <[email protected]> wrote in message
> news:%[email protected]...
> > In this and other Excel NGs, I see quite often VBA lines like:
> >
> > sub Foo()
> > dim myRng as Range
> > dim myRS as ADODB.Recordset
> > ...
> > set myRng = Nothing
> > set myRS = Nothing
> > End Sub
> >
> > Is this setting objects to Nothing really useful for local
> > variables that
> > will be automatically destroyed at the end of the Sub.
> >
> > TIA
> >
> > --
> > AP
> >
> >
>
>