何时应终止 Excel VBA 变量或将其设置为 Nothing? [英] When should an Excel VBA variable be killed or set to Nothing?

查看:37
本文介绍了何时应终止 Excel VBA 变量或将其设置为 Nothing?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在过去的两年里,我一直在自学 Excel VBA,我认为有时在代码段的末尾处理变量是合适的.例如,我已经看到它在改编自 Ron de Bruin 的将 Excel 转换为 HTML 的代码:

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

我已经对此进行了一些搜索,除了如何做之外几乎没有发现,并且在一个论坛帖子中 不需要局部变量的声明它们在 End Sub 处不复存在.我猜测,根据上面的代码,在 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.

所以我的问题归结为:

  • 网络上是否有什么地方可以解释变量清理的时间和原因,但我还没有找到?

如果不能,这里有人可以解释一下...

And if not can someone here please explain...

  • 什么时候需要对 Excel VBA 进行变量清理,什么时候不需要?
  • 更具体地说......是否有特定的变量用途(公共变量?函数定义的变量?)在内存中加载时间更长比潜艇做的要好,因此如果我不清洁可能会造成麻烦追我吗?

推荐答案

VB6/VBA 使用确定性方法来销毁对象.每个对象存储对自身的引用数.当数量达到零时,对象被销毁.

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.

对象变量在超出范围时保证被清除(设置为 Nothing),这会减少它们各自对象中的引用计数器.无需手动操作.

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. 当您希望在其变量超出范围之前销毁对象(例如,您的过程将需要很长时间才能执行,并且该对象持有资源,因此您希望将对象销毁为尽快释放资源).

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

当您在两个或多个对象之间有循环引用时.

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

如果objectA 存储了对objectB 的引用,而objectB 存储了对objectA 的引用,则这两个对象除非您通过显式设置 objectA.ReferenceToB = NothingobjectB.ReferenceToA = Nothing 来制动链,否则永远不会被破坏.

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.

如果你有一个类级别的变量,当类实例被破坏时它会被清理/销毁.如果需要,您可以提前销毁它(请参阅项目 1.).

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

如果你有一个模块级别的变量,它会在你的程序退出时被清除/销毁(或者,在 VBA 的情况下,当 VBA 项目被重置时).如果需要,您可以提前销毁它(请参阅项目 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆