VBA中的哨兵对象 [英] Sentry objects in VBA

问题描述

在网络上和我自己的项目中，我普遍看到具有以下模式的代码:

I see codes with the following pattern ubiquitously on the web and in my own projects:

Sub Func()
     Application.EnableEvents = False
     ' some code
     Application.EnableEvents = True
End Sub

由于VBA对象的生存期似乎是确定的，所以我认为我可以像在C++中那样用所谓的哨兵对象替换此模式，这样就可以解决异常退出的问题(err.raise)可以自动解决.

Since the lifetime of VBA objects seems to be deterministic, I thought I could replace this pattern with the so-called sentry objects as we do in C++, so that the problem of unusual exit (err.raise) could be solved automatically.

但是如何?我不太了解，因为我是VBA的新手，甚至还没有完全意识到何时通过引用传递对象.理想情况下，我希望代码看起来像这样:

But how? I have little idea because I'm new to VBA and not even fully aware of when a object is passed by reference. Ideally, I would like to have the code look something like this:

Sub Func()
     dim Sentry
     Set Sentry = CreateSentry(Application.EnableEvents,False)

     ' From now on we should not need to care if the variable was actually 
     ' True or False beforehand, what kind of error handling is used in this function, etc.
End Sub

推荐答案

Application.EnableEvents不是变量，而是属性.您不能在VB(A)中通过引用传递属性，编译器将创建当前属性值的临时副本，并且您的哨兵将被封闭"在该副本上.

Application.EnableEvents is not a variable, it is a property. You cannot pass a property by reference like this in VB(A), the compiler will create a temporary copy of the current property value, and your sentry will be "closed" over the copy.

要以这种方式管理对象属性，可以执行以下操作:
创建一个类，将其命名为SentryForPropertiesVariant并使用类似的代码:

To manage object properties in this way you could do this:
Create a class, name it e.g. SentryForPropertiesVariant and use similar code:

Option Explicit

Private m_Obj As Object
Private m_PropertyName As String
Private m_OldValue As Variant


Public Sub Init(ByVal obj As Object, ByVal PropertyName As String, ByVal NewValue As Variant)
  Set m_Obj = obj
  m_PropertyName = PropertyName

  m_OldValue = CallByName(obj, m_PropertyName, VbGet)
  CallByName m_Obj, m_PropertyName, VbLet, NewValue
End Sub

Private Sub Class_Terminate()
  If Not m_Obj Is Nothing Then
    CallByName m_Obj, m_PropertyName, VbLet, m_OldValue
  End If
End Sub

然后使用它:

Dim s As SentryForPropertiesVariant
Set s = New SentryForPropertiesVariant

s.Init Application, "EnableEvents", False

---

您还可以在模块中具有帮助功能:

---

You can also have a helper function in a module:

Public Function CreateSentry(ByVal obj As Object, ByVal PropertyName As String, ByVal NewValue As Variant) As SentryForPropertiesVariant
  Set CreateSentry = New SentryForPropertiesVariant

  CreateSentry.Init obj, PropertyName, NewValue
End Function

此时使用变得更简单:

Dim s As SentryForPropertiesVariant
Set s = CreateSentry(Application, "EnableEvents", False)

，在这种情况下，您可能希望将Public Sub Init替换为Friend Sub Init.

and in which case you probably want to replace Public Sub Init with Friend Sub Init.

如果您打算将哨兵类存储在共享的加载项(.xla)中，则无论如何都必须具有此类帮助程序功能，因为无法通过驻留在其他工作簿中的代码来创建加载项中定义的类，因此解决方案是在与该类相同的工作簿中定义一个函数，该函数将创建实例并将其返回给外部调用者.

If you plan to store your sentry class in a shared add-in (.xla), you will have to have such helper function anyway, because classes defined in add-ins cannot be created from code residing in other workbooks, so the solution is to also define a function in the same workbook as the class that would create the instance and return it to the external caller.

最后，使用With(类似于C#的using)来控制此类哨兵的生存时间很方便:

Finally, it is convenient to control the lifetime of such sentries with With (resembles C#'s using):

With CreateSentry(Application, "EnableEvents", False)
  'Here EnableEvents is False
End With

'Here it's True

但是，这样做时，请记住仅With类似 using.如果您使用GoTo过早跳出它，则End With语句

However when doing so, you should keep in mind the With only resembles using. If you jump out of it prematurely with GoTo, the End With statement will not be executed, which means the temporary variable holding the sentry instance will live to the end of the procedure, and the property will not revert to its original value until then.

所以不要跳出那些障碍.如果绝对需要，请在End With之前创建一个标签，然后跳转到该标签.

So don't jump out of those blocks. If you absolutely have to, create a label right before End With and jump to that.

这篇关于VBA中的哨兵对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！