Remove object from memory

[lexcel]

Maybe stupid question, but I couldn't find an answer in the help file
or the forum:
How do I remove an object from memory?
I am looking for the opposite of New :
Set MyObject = New MyClass
Now how do I free the memory occupied by this object ?

 

[Trevor Shuttleworth]

Set MyObject = Nothing

Regards

Trevor

 

[Norman Jones]

Hi Lexcel,

Try:

Set myOjrct = Nothing

 

[lexcel]

Thanks for the reply, but does this really remove the object from
memory as well?
What if I did the following:

Dim Fred as MyObject, George as New MyObject

Set Fred = George
Set George = Nothing

I could still access the same object through Fred, it is not erased
from memory.
For many built-in functions there is a Delete method, that is more like
what I am looking for.
Maybe may question should be:
How do I create a Delete method which frees the memory occupied by my
object?

With regards,

Lex

 

[Trevor Shuttleworth]

Lex

All you've done is created a reference. Then you've put some data there.
You've used it and then you've set it to nothing ... cleared it out.

Try:

Sub test()
Dim MyObject As Worksheet
Set MyObject = Worksheets(1)
MsgBox MyObject.Name
Set MyObject = Nothing
MsgBox MyObject.Name
End Sub

and see what happens.

Regards

Trevor

 

[Norman Jones]

Hi Lexcel,

See Chip Pearson's comments in the section entitled: 'Don't Use The New
Keyword In A Dim Statement'

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

 

[JMB]

I remember reading somewhere that an object won't be removed from memory
until ALL references to the object are set to nothing - which is what I think
Trevor is getting at.

 

[lexcel]

Thanks Norman, I was aware of this article, but as this is example code
which will never be compiled I thought efficiency was of minor
importance.
But maybe the example would have been more clear if I put the New
outside the Dim.
Roaming the internet I find the most peculiar and interesting
information, but still no answer... Only more questions. I'll put them
on the forum and see what happens.

 

[lexcel]

I got it.
I am a bit ashamed as well.
But I didn't believe that VBA was so complex (to make our lives
simpler).
I added a Class_Terminate routine to MyClass and indeed, it was not
called as long as at least 1 pointer to the object survived.
So VBA inserts code that keeps track of the number of pointers to an
object and when that becomes 0 deletes the object. That implies that it
is up to VBA to remove the object from memory or not and the programmer
has no control over it.
But it makes the programmers life more simple, no worry about cleaning
up.

Anyway it is clear now what happens and everybody was right.

Thanks a lot.