VBA Excel - Releasing variables from memory - getting error - can't see why

[Sparkle99]

Hi,
I'm trying to release some variables at the end of a loop

The line...
Set CurrentRow = Nothing
causes...
Compile Error - Object Required

Excel 2010. I have tried CurrentRow defined as Long, String and others.
I have tried the DIM statement outside of the loop or inside - makes not difference
This is happening for ALL of my 'Set xxx = Nothing' statements - not specific to this variable.

Any suggestions ?

 

[Andrew Poulsom]

Set and Nothing are used for Object variables (like Worksheet and Range) not Longs or Strings.

 

[GopalaKrishnaJ]

Use Like This if string

 CurrentRow = VbNullString

If Long or interger or double type

 CurrentRow = Empty

 

[Kyle123]

Hmmm, this may get me a battering but I'm not even remotely convinced there is an actual need to release memory in this way as a generic rule.

The garbage collector will release the memory as soon as the variable goes out of scope. Sure, there are situations where it is necessary to do this explicitly for example to prevent circular references between classes but in the main I rarely ever release memory explicitly.

Still it is considered best practice and there's certainly no harm in doing it, maybe I'm just lazy....

I'd be very interested in any article which shows that it is absolutely required (other than the dodgy DAO access thing).

 

[Andrew Poulsom]

There's a discussion on the subject here:

Daily Dose of Excel » Blog Archive » Nothing Keyword

 

[Kyle123]

Thanks Andrew, I'd seen that before and the linked thread (on DDoE) makes for interesting reading. It seems really to come down to personal preference unless there's a genuine need for it - of which of course there can be which I eluded to in my earlier post.

Still, no harm in including it anyway