VBA Reference counting - Object destruction

Question

Lately I've bumped into a question that made me pounder; it kept me busy and I couldn't find a transparent explanation for it on the net.
It is related to the destruction of Excel objects (which I use all the time and never really questioned before).

Background leading to my question:
With regular objects, you can instantiate an object using the keywords SET and NEW. For example:

Set classInstance = New className 

Whenever we instantiate this way, the object is created in the heap memory and the reference counter is increased by 1.
In case I don't add more references, the following statement would bring the reference count back to zero:

Set classInstance = Nothing  

When the reference count goes to 0, the object is destroyed and cleared from memory and the "classInstance" points to .

What I've read:
When we use the "CREATEOBJECT" function, it returns a reference to a COM object.

Set oApp = CreateObject("Excel.Application") 

Even though we could say:

Set oApp = nothing  

The objects' reference count will go to 0, and oApp will not point to the object anymore.

My questions:
1) Why is it that this type of object requires to call the method .Quit before the object is actually being removed from memory?
The same goes when adding a reference to a workbook object (workbooks.add or workbook.open) which requires the .close method. Why can't these objects be automatically destroyed when bringing the reference count to zero?
Which is the case when we say for example:

set oRange = nothing  

2) And is there a need to say:

oApp.Quit set oApp = nothing  

Since the Application object is already cleared from memory when applying .Quit, there is no object to be released anymore.
The only reason I could come up with, why oApp would be set to Nothing after Quit, would be because it could be pointing to an unused memory location (on the heap) and could lead to confusion later if this memory would be re-assigned (although in VBA I find this hard to imagine). I was questioning myself if this conclusion is correct and I would like to receive confirmation for that from someone who knows the answer.
Please, tell me if I see this wrongly.

3) What they call in VBA "a reference to an object" (such as oApp in the code above), I see them as pointer variables in C. Would it be safe to use this statement or again, am I seeing this wrongly?

Generally is not hard to apply .Quit and set to nothing, but it would be nice to receive some accurate information on the topic. So that I know for 100% percent why I am doing it.

Answer 1

Good Question :)

Excel controls the creation of its objects. Likewise it also controls their destruction.

Setting oApp = Nothing just destroys the object reference. It doesn't remove the Application. To destroy an Excel object, you have to use it's .Quit method.

Whenever you do, Set x = Nothing, the reference(pointer) named x to its relevant object is removed. This doesn't mean that the object itself will be removed from the memory. Whether the object will be removed from memory or not, depends on various factors.

1. Whether there are more references pointing towards the same object. If there are, the object will not be removed. The reference count must be zero.
2. The internal implementation of the destructor of that object.

The .Quit method is defined to graciously remove all the memory objects excel has allocated, and close itself.

It is similar to calling Close on a form in VB6. Take for example, a form in vb6.

Dim f As Form Set f = Form1 f.Show  ' '~~> Rest of the code '  Set f = Nothing 

Will this destroy the form? :)

FOLLOWUP

How about question 2? Thanks – Kim Gysen 14 mins ago

It might not be exactly as shown here, and compiler optimizations may make things behave differently... but this is the basic concept that is at work.

Answer 2

Part 2 of your question is quite interesting, and it's well worth an extended answer.

This is going to cover three key points:

• Objects and object variables;
• Pitfalls when dismissing objects;
• ...And an important change in reference-counting the Application object in Excel 2013.

But, if you want a short answer, it's: "Not all objects are equal".

Now read on...

Some objects are created in your Excel session's 'own' memory space, and their memory allocation is controlled by your session; some objects have persistent components that exist after the object variable is dismissed; and some do not:

 Set oDict = CreateObject("Scripting.Dictionary") Set oWShell = CreateObject("Shell.Application") 

In both these cases, memory is allocated, the object variables (and their vTable of pointers to methods and properties) are instantiated, and they are yours to command until you dismiss them:

 Set oDict = Nothing Set oWShell = Nothing 

And, on dismissal, no trace of them remains.

But this object is persistent:

 Dim oWbk as Excel.Workbook
 Set oWbk = Application.Workbooks.Add 

...You've created a new workbook object and, if you dismiss the object variable with Set oWbk = Nothing, you will see that the new workbook object still exists as a visible presence in the user interface.

What you actually created was a Workbook object - a workbook window with an active worksheet and the full user interface that goes with that - and a Workbook object variable - a programmer's COM interface, a table of methods and properties for the Workbook object - that you can manipulate in code using the named entity 'oWbk'.

Dismissing the oWbk object variable removes that framework, but the Workbook itself will still exist: you've created a Workbook object, and it's yours to keep.

The object is more than its object variable and dismissing the variable does not destroy the object: it just dismisses an interface, a framework of methods and properties that you can use to manipulate the object in code.

Closing the Workbook, with or without saving a file, should automatically dismiss the object variable and clear up the memory allocated for that interface of properties, methods and attributes:

 'try this: oWbk.Close SaveChanges:=False ' or maybe this: Application.Workbooks(Application.Workbooks.Count).Close SaveChanges:=False  

...That is to say, you would expect both of those commands to call Set oWbk= Nothing - especially the oWbk.Close command - but if you try either of them without explicitly dismissing oWbk, you will find that oWbk still exists as an empty husk, and all calls and requests for information on it (try> Debug.Print> TypeName(oWbk) ) will return 'Automation Error' messages.

Some of the commments in the previous answer mention the UserForm object which - unlike the Dictionary and the Shell object - is an object with a visible user interface. But this user interface is not a persistent new object in the Excel user interface like a Workbook or a worksheet.

Luckily for you, the object you created is owned by your Excel session, and you can instantiate an object variable again, to get the same framework of methods and properties, and take control of the object again:

 Set oWbk = Application.Workbooks(Application.Workbooks.Count) 

...Assuming, of course, that you have some way of being sure that you identified the right workbook object: but that's not your question at all.

Where this answer is going is: objects that are not created in your Excel session's 'own' memory.

 Set oApp = CreateObject("Excel.Application") 

This statement will create an Excel object which, like the new Workbook, has a User Interface (although you'll need to set the .Visible property True to see it) and and a persistent presence in memory: once again, the object is more than its object variable, and dismising the variable does not destroy the object.

Unlike the new Workbook, it isn't quite yours to command: it's an Excel session in it's own right, it allocates its own memory - oApp's 'footprint' in your current session's memory is just the pointer and the name: the interface (vTable, iDispatch, and all those named methods with pointers to the structures that implement the arcane act of manipulating an Excel session in VBA) exists in the block of memory allocated by this new Excel session.

Here's what happens in Office 2010, and older versions of Excel:

Dismissing the object variable with Set oApp = Nothing leaves that session up and running, and I would strongly suggest that you make the session visible so that you can close it manually!

Closing that Excel session manually, without explicitly dismissing the oApp object variable, will definitely leave oApp in the 'empty husk' state, and a grim and headless spectre wailing 'The Automation object has disconnected from its clients!' in the dark corners of your code base.

But, in Office 2013 and later versions, Set oApp = Nothing performs exactly the reference-counting you would expect and the session closes. Try it:

 Private Sub Test()
 Dim oApp As Excel.Application
 Set oApp = New Excel.Application 'Set oApp = CreateObject("Excel.Application")
 oApp.Visible = True
 Set oApp = Nothing
 End Sub 

It won't close on Set oApp = Nothing if another object variable has a reference - and that's not the only entity that gets to increment the reference counter: user activity in the GUI (try creating a new workbook and editing it) keeps the session up and running, too.

For your own amusement, see if oApp.Quit does actually dismiss oApp and sets it to Nothing.

Of course, oApp.Quit will definitely close the session...

...Or will it? If there is something going on in that session - a long calculation, or an 'modal' error message that you have to see and click before the Application object responds to any other input, from the user interface or your VBA - then oApp.Quit won't close the session.

Lets not go there. All things being equal, oApp.Quit will definitely close the session in 2010 and earlier versions of Excel.

But in Office 2013, calling 'Quit' from the last object variable merely hides the UI: the object variable still responds to your code - the methods and properties that don't require an active workbook are still accessible via oApp - and a separate instance of Excel.exe is clearly visible in the Processes tab of Task manager.

Likewise, quitting the new session by clicking the 'close' button in the user interface closes the session's windows but, if there's an object variable with a reference to this application object in your code, it's still there, in memory, and 'oApp' can still get at the properties and methods.

So the reference counter works both ways in current versions of Excel: the object exists until the reference count decrements to zero, and the last remaining object variable will not be left 'disconnected' by a quit command or UI action.

Nevertheless, your session doesn't 'own' that new application object: if you've dismissed the last object variable and set it to Nothing, and there's something else keeping the neww session alive - user activity, or some internal process - there's nothing like the Application.Workbooks() or Worksheets() collection to identify other Excel sessions and instantiate an object variable pointing to a specific instance of an Excel.Application object.

There are ways of getting a specific session using API calls, but they aren't as reliable as you might wish them to be.

...So, all in all, there's quite a lot in that 'part 2'.