VBA 引用计数 - 对象销毁

VBA Reference counting - Object destruction

提问by Trace

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 reallyquestioned before).

最近我遇到了一个让我更生气的问题；它让我很忙，我无法在网上找到一个透明的解释。
它与 Excel 对象的销毁有关（我一直使用它，以前从未真正质疑过）。

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

导致我的问题的背景：
对于常规对象，您可以使用关键字 SET 和 NEW 实例化一个对象。例如：

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:

每当我们以这种方式实例化时，对象就会在堆内存中创建，并且引用计数器增加 1。
如果我不添加更多引用，以下语句会将引用计数归零：

Set classInstance = Nothing 

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

当引用计数变为 0 时，对象被销毁并从内存中清除，并且“classInstance”指向 .

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

我读过的内容：
当我们使用“CREATEOBJECT”函数时，它返回对 COM 对象的引用。

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.

对象的引用计数将变为 0，并且 oApp 将不再指向该对象。

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:

我的问题：
1）为什么这种类型的对象需要在实际从内存中删除对象之前调用方法 .Quit ？
添加对需要 .close 方法的工作簿对象（workbooks.add 或 workbook.open）的引用时也是如此。为什么在将引用计数归零时不能自动销毁这些对象？
例如，当我们说：

set oRange = nothing 

2) And is there a need to say:

2）还有没有必要说：

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.

由于 Application 对象在申请 .Quit 时已经从内存中清除了，所以没有对象可以再释放了。
我能想到的唯一原因，为什么 oApp 在退出后会被设置为 Nothing，是因为它可能指向一个未使用的内存位置（在堆上），并且如果重新分配这个内存可能会导致混乱（虽然在 VBA 中我发现这很难想象）。我在问自己这个结论是否正确，我希望得到知道答案的人的确认。
请告诉我，如果我看错了。

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?

3）他们在 VBA 中所说的“对对象的引用”（例如上面代码中的 oApp），我将它们视为 C 中的指针变量。使用此语句是否安全，还是再次使用，我是否错误地看到了这一点？

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.

通常不难应用。退出并设置为空，但收到有关该主题的一些准确信息会很好。这样我就 100% 地知道我为什么要这样做。

采纳答案by Siddharth Rout

Good Question :)

好问题 ：）

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

Excel 控制其对象的创建。同样，它也控制着它们的破坏。

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

设置oApp = Nothing只会破坏对象引用。它不会删除应用程序。要销毁 Excel 对象，您必须使用它的.Quit方法。

Whenever you do, Set x = Nothing, the reference(pointer) named xto 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.

每当您这样做时，都会删除Set x = Nothing指向x其相关对象的引用（指针）。这并不意味着对象本身将从内存中删除。对象是否会从内存中移除取决于各种因素。

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.

1. 是否有更多的引用指向同一个对象。如果存在，则不会删除该对象。引用计数必须为零。
2. 该对象的析构函数的内部实现。

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

该.Quit方法被定义为优雅地删除 excel 分配的所有内存对象，并关闭自身。

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

它类似于Close在 VB6 中调用窗体。以 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

问题2呢？谢谢 – Kim Gysen 14 分钟前

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.

它可能与此处显示的不完全一样，并且编译器优化可能会使事情的行为有所不同……但这是起作用的基本概念。

回答by Nigel Heffernan

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

您问题的第 2 部分非常有趣，值得扩展答案。

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.

• 对象和对象变量；
• 解除对象时的陷阱；
• ...以及在 Excel 2013 中对 Application 对象进行引用计数的一个重要变化。

但是，如果你想要一个简短的答案，那就是： "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:

有些对象是在你的 Excel 会话的“自己的”内存空间中创建的，它们的内存分配由你的会话控制；某些对象具有在对象变量被解除后存在的持久组件；有些没有：

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:

在这两种情况下，内存被分配，对象变量（以及它们指向方法和属性的指针 vTable）被实例化，它们是你的命令，直到你关闭它们：

Set oDict = Nothing
Set oWShell = Nothing

And, on dismissal, no trace of them remains.

而且，在被解雇时，他们的踪迹都没有留下。

But thisobject is persistent:

但是这个对象是持久的：

Dim oWbk as Excel.Workbook

Set oWbk = Application.Workbooks.Add

...您已经创建了一个新的工作簿对象，如果您使用 关闭对象变量Set oWbk = NothingSet oWbk = Nothing，您将看到新的工作簿对象仍然以可见的形式存在于用户界面中。

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'.

您实际创建的是一个 Workbook对象- 一个带有活动工作表的工作簿窗口和与之配套的完整用户界面 - 以及一个 Workbook对象变量- 程序员的 COM 接口，Workbook 对象的方法和属性表 - 您可以使用命名实体“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.

取消 oWbk 对象变量会删除该框架，但 Workbook 本身仍然存在：您已经创建了一个 Workbook 对象，并且保留它是您的。

The object is more than its object variableand 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 

...也就是说，您会期望这两个命令都调用 Set oWbk= NothingSet oWbk= Nothing- 尤其是oWbk.CloseoWbk.Close命令 - 但是如果您在没有明确解除 oWbk 的情况下尝试其中任何一个，您会发现它oWbkoWbk仍然作为一个空壳存在，并且所有调用和请求有关它的信息 (try>  Debug.Print> TypeName(oWbk)Debug.Print> TypeName(oWbk) ) 将返回'Automation Error'“自动化错误”消息。

Some of the commments in the previous answer mention the UserFormobject 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.

上一个答案中的一些评论提到了该UserForm对象 - 与 Dictionary 和 Shell 对象不同 - 是具有可见用户界面的对象。但此用户界面不是 Excel 用户界面中的持久性新对象，如工作簿或工作表。

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:

幸运的是，您创建的对象归您的 Excel 会话所有，您可以再次实例化一个对象变量，以获得相同的方法和属性框架，并再次控制该对象：

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

...当然，假设您有某种方法可以确保您确定了正确的工作簿对象：但这根本不是您的问题。

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

这个答案是：不是在 Excel 会话的“自己的”内存中创建的对象。

Set oApp = CreateObject("Excel.Application")

这个语句将创建一个 Excel 对象，它和新的 Workbook 一样，有一个用户界面（虽然你需要设置.Visible.Visible属性 True 才能看到它）和内存中的持久存在：再一次，对象不仅仅是它的对象变量，消除变量不会破坏对象。

Unlike the new Workbook, it isn't quiteyours 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.

不同的是新的工作簿，它不是很你的指挥：它是在它自己的权利，分配其自己的内存的Excel会话- oApp在当前会话的内存“足迹”只是指针和名称：接口（VTABLE 、iDispatch 和所有那些带有指向实现在 VBA 中操作 Excel 会话的神秘行为的结构的指针的命名方法存在于这个新 Excel 会话分配的内存块中。

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

以下是 Office 2010 和旧版 Excel 中发生的情况：

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

关闭对象变量Set oApp = Nothing会使该会话保持运行状态，我强烈建议您使该会话可见，以便您可以手动关闭它！

Closing that Excel session manually, without explicitly dismissing the oApp object variable, will definitelyleave 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.

手动关闭该 Excel 会话，而不明确解除 oApp 对象变量，肯定会使 oApp 处于“空壳”状态，并且一个冷酷无头的幽灵在哀号 “自动化对象已与其客户端断开连接！” 在代码库的黑暗角落。

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

但是，在 Office 2013 和更高版本中，Set oApp = Nothing会完全执行您期望的引用计数，并且会话会关闭。尝试一下：

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

Set oApp = NothingSet oApp = Nothing如果另一个对象变量有引用， 它就不会关闭——这不是唯一一个增加引用计数器的实体：GUI 中的用户活动（尝试创建一个新工作簿并编辑它）保持会话正常运行，也。

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

为了您自己的娱乐，看看是否oApp.Quit确实关闭了 oApp 并将其设置为Nothing.

Of course, oApp.Quitwill definitely close the session...

当然，oApp.Quit肯定会关闭会话......

...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 anyother input, from the user interface or your VBA - then oApp.Quitwon't close the session.

......或者会吗？如果在该会话中发生了某些事情 - 长时间的计算，或者在 Application 对象响应来自用户界面或 VBA 的任何其他输入之前必须查看并单击的“模态”错误消息- 那么oApp.Quit不会关闭会话。

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

我们不要去那里。在所有条件相同的情况下，oApp.Quit肯定会在 2010 和更早版本的 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.

但是在 Office 2013 中，从最后一个对象变量调用“退出”只会隐藏 UI：对象变量仍然响应您的代码——不需要活动工作簿的方法和属性仍然可以通过 oApp 访问——以及一个单独的实例Excel.exe 的文件在任务管理器的进程选项卡中清晰可见。

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.

同样，通过单击用户界面中的“关闭”按钮退出新会话会关闭会话的窗口，但是，如果您的代码中有引用此应用程序对象的对象变量，它仍然存在于内存中，并且“oApp”仍然可以获取属性和方法。

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 notbe left 'disconnected' by a quit command or UI action.

因此，引用计数器在当前版本的 Excel 中以两种方式工作：对象存在直到引用计数递减为零，并且最后一个剩余的对象变量不会被退出命令或 UI 操作“断开连接”。

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.

不过，您的会话没有“自己”的新的应用对象：如果你被解雇的最后一个对象变量并将其设置为Nothing，并且还有别的保持东西方妇女网络会话有效-用户活动，或者一些内部过程-没有什么像Application.Workbooks() 或 Worksheets() 集合以标识其他 Excel 会话并实例化指向 Excel.Application 对象的特定实例的对象变量。

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

有多种使用 API 调用获取特定会话的方法，但它们并不像您希望的那样可靠。

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

...所以，总而言之，“第 2 部分”中有很多内容。