何时应该将Excel VBA变量杀死或设置为Nothing？ [英] When should an Excel VBA variable be killed or set to Nothing?

问题描述

在过去的两年中，我一直在教Excel VBA，而且我认为在代码段末尾处理变量有时是合适的。例如，我已经看到它完成了这一点，从 Ron de Bruin将Excel转换为HTML的代码：

 函数SaveContentToHTML（Rng为范围）
 
 Dim FileForHTMLStorage As Object 
 Dim TextStreamOfHTML As Object 
 Dim TemporaryFileLocation As String 
 Dim TemporaryWorkbook As Workbook 
 
 ... 
 
 TemporaryWorkbook.Close savechanges：= False 
杀死TemporaryFileLocation 
 Set TextStreamOfHTML = Nothing 
 Set FileForHTMLStorage = Nothing 
 Set TemporaryWorkbook = Nothing 
 
结束函数
  

我已经做了一些搜索，发现很少超出了如何做，在一个论坛帖子没有局部变量需要被清除的声明，因为它们在 End Sub 中不再存在。我猜，根据上面的代码，可能在 End Function 或其他情况下我没有遇到过。

所以我的问题归结为：

• 网络上是否有某个地方解释了何时和为什么为了可变清理，我只是没有找到它？

如果不是有人可以在这里解释...

• 是否需要Excel VBA的变量清理？


• 更具体地说...在内存中保留加载更多
  的特定变量使用（公共变量？
  函数定义的变量）如果我自己不清洁
  ，可能会导致麻烦。

解决方案

VB6 / VBA使用确定性方法去剔除对象。每个对象存储对自身的引用数量。当数字达到零时，对象被销毁。

保证对象变量被清理（设置为 Nothing ）当它们超出范围时，这会减少它们各自对象中的引用计数器。不需要手动操作。

只有两种情况需要显式清理：

1. 当您希望在变量超出范围之前销毁对象（例如，您的过程需要很长时间才能执行，并且对象持有资源，因此您要销毁对象尽快释放资源）




2. 当两个或更多对象之间有一个循环引用。

   
   
   

   如果 objectA 存储对 objectB 和 objectB 存储对 objectA 的引用，两个对象永远不会被破坏，除非你明确地设置 objectA.ReferenceToB = Nothing 或 objectB.ReferenceToA = Nothing 。






你显示的代码段是错误的。不需要手动清理。做手动清理甚至是有害的，因为它给你一个错误的更正确的代码。

如果您在类级别有一个变量，它将被清理/销毁类实例被破坏。如果你想要的话，你可以先破坏它（见项目 1。）

如果你有一个变量模块级别，当您的程序退出时（或者在VBA的情况下，当VBA项目重置时），它将被清除/销毁。如果你想要的话，你可以先破坏它（参见 1。）

变量的访问级别与私人）不影响其寿命。

I've been teaching myself Excel VBA over the last two years, and I have the idea that it is sometimes appropriate to dispose of variables at the end of a code segment. For example, I've seen it done in this bit adapted from Ron de Bruin's code for transferring Excel to HTML:

Function SaveContentToHTML (Rng as Range)

        Dim FileForHTMLStorage As Object
        Dim TextStreamOfHTML As Object
        Dim TemporaryFileLocation As String
        Dim TemporaryWorkbook As Workbook

...

        TemporaryWorkbook.Close savechanges:=False
        Kill TemporaryFileLocation
        Set TextStreamOfHTML = Nothing
        Set FileForHTMLStorage = Nothing
        Set TemporaryWorkbook = Nothing

End Function

I've done some searching on this and found very little beyond how to do it, and in one forum post a statement that no local variables need to be cleared, since they cease to exist at End Sub. I'm guessing, based on the code above, that may not be true at End Function, or in other circumstances I haven't encountered.

So my question boils down to this:

• Is there somewhere on the web that explains the when and why for variable cleanup, and I just have not found it?

And if not can someone here please explain...

• When is variable cleanup for Excel VBA necessary and when it is not?
• And more specifically... Are there specific variable uses (public variables? Function-defined variables?) that remain loaded in memory for longer than subs do, and therefor could cause trouble if I don't clean up after myself?

解决方案

VB6/VBA uses deterministic approach to destoying objects. Each object stores number of references to itself. When the number reaches zero, the object is destroyed.

Object variables are guaranteed to be cleaned (set to Nothing) when they go out of scope, this decrements the reference counters in their respective objects. No manual action required.

There are only two cases when you want an explicit cleanup:

1. When you want an object to be destroyed before its variable goes out of scope (e.g., your procedure is going to take long time to execute, and the object holds a resource, so you want to destroy the object as soon as possible to release the resource).

2. When you have a circular reference between two or more objects.

   If objectA stores a references to objectB, and objectB stores a reference to objectA, the two objects will never get destroyed unless you brake the chain by explicitly setting objectA.ReferenceToB = Nothing or objectB.ReferenceToA = Nothing.

The code snippet you show is wrong. No manual cleanup is required. It is even harmful to do a manual cleanup, as it gives you a false sense of more correct code.

If you have a variable at a class level, it will be cleaned/destroyed when the class instance is destructed. You can destroy it earlier if you want (see item 1.).

If you have a variable at a module level, it will be cleaned/destroyed when your program exits (or, in case of VBA, when the VBA project is reset). You can destroy it earlier if you want (see item 1.).

Access level of a variable (public vs. private) does not affect its life time.

这篇关于何时应该将Excel VBA变量杀死或设置为Nothing？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！