如何在VBA中为类模块声明静态变量? [英] How do I declare Static variable for Class Module in VBA?

问题描述

我想跟踪Excel-VBA中特定类的所有实例，例如VB.Net中的静态成员.这是我的班级模块:

ClassModule:clsClass

Private pName as String
'Static pCount Commented as it doesnt work

Property Set Name(arg as String)
    pName=arg
End Property

Private Sub Class_Initialize()
    'pCount = pCount + 1  Commented as it doesnt work 
End Sub

Public Function GetCount()
    GetCount = pCount
End Function

和我的通用模块 模组:Module1

Sub ABC()

Dim instance1 As New clsClass
Dim instance2 As New clsClass
Dim instance3 As New clsClass
Dim instance4 As New clsClass
'Debug.Print instance4.GetCount() This Should Return 4, but doesnt
End Sub

我在做什么错了?我如何声明一个在所有实例之间共享的变量?

解决方案

最小代码

基于 VBA中的类(静态)方法中所述的逻辑.. >

静态属性StaticCount在Constructor方法中递增. Get和Let Property过程(If Singleton Is Nothing Then ... Else ... End If)中提到了支持静态属性的最重要代码.

此处的缺点是它使用End清除全局/静态变量，以便在每次调用ABC时都打印2，但是也会清除您可能不希望的VBProject的所有全局变量. .如果不使用End，则每次调用ABC时，它将打印2，4，6，+ 2.请参阅下一章中的解决方法.

模块Module1:

Sub ABC()
    Set instance1 = New_clsClass()
    Set instance2 = New_clsClass()
    Debug.Print "Result: " & instance1.StaticCount 'This returns 2
    End ' Reset Global/Static memory to clear "Static Singleton" (and whole VBProject memory)
End Sub

Function New_clsClass() As clsClass
    Set Object = New clsClass
    Static Singleton As clsClass
    If Singleton Is Nothing Then
        Set Singleton = New clsClass
    End If
    Set Object.Singleton = Singleton
    Call Object.Constructor
    Set New_clsClass = Object
End Function

Class Module clsClass:

Private StaticCount_ As Integer
Private Singleton_ As clsClass

Private Static Property Get Singleton() As Object
    Set Singleton = Singleton_
End Property

Private Property Set Singleton(Object As Object)
    Set Singleton_ = Object
End Property

Public Property Get StaticCount() As Integer
    If Singleton Is Nothing Then
        StaticCount = StaticCount_
    Else
        StaticCount = Singleton.StaticCount
    End If
End Property

Private Property Let StaticCount(value As Integer)
    If Singleton Is Nothing Then
        StaticCount_ = value
    Else
        Singleton.StaticCount = value
    End If
End Property

Public Sub Constructor()
    StaticCount = StaticCount + 1
End Sub

使用最小的代码+控制您重置哪些全局/静态变量的方法，而不是使用End

此解决方案基于保留单例的全局变量，因此您可以在运行ABC时重置单例.在这里，除了clsClass74和clsClass75类中的所有单例都将重置，这些类将始终"保持其静态属性活动.

模块Module1:

Global goSingletons As New Collection

Sub ABC()
    Call ResetGlobalMemory
    Set instance1 = New_clsClass()
    Set instance2 = New_clsClass()
    Debug.Print "Result: " & instance1.StaticCount 'This returns 2
End Sub

Sub ResetGlobalMemory()
    ' Reset all singletons except the one of clsClass7
    For i = goSingletons.count To 1 Step -1
        Select Case TypeName(goSingletons(i))
            Case "clsClass74", "clsClass75"
            Case Else
                Call goSingletons.Remove(i)
        End Select
    Next
End Sub

Function New_clsClass() As clsClass
    Set Object = New clsClass
    Set Object.Singleton = GetSingleton("clsClass")
    Call Object.Constructor
    Set New_clsClass = Object
End Function

Function GetSingleton(ClassName As String)
    On Error Resume Next
    Set Singleton = goSingletons(ClassName)
    If Err.Number <> 0 Then
        On Error GoTo 0
        Select Case ClassName
            Case "clsClass": Set Singleton = New clsClass
            Case "clsClass2": Set Singleton = New clsClass2
            Case Else: Err.Raise 9999, , "Singleton not managed by class " & ClassName
        End Select
        Call goSingletons.Add(Singleton, ClassName)
    End If
    Set GetSingleton = Singleton
End Function

Class Module clsClass:

Private StaticCount_ As Integer
Private Singleton_ As clsClass

Private Static Property Get Singleton() As Object
    Set Singleton = Singleton_
End Property

Private Property Set Singleton(Object As Object)
    Set Singleton_ = Object
End Property

Public Property Get StaticCount() As Integer
    If Singleton Is Nothing Then
        StaticCount = StaticCount_
    Else
        StaticCount = Singleton.StaticCount
    End If
End Property

Private Property Let StaticCount(value As Integer)
    If Singleton Is Nothing Then
        StaticCount_ = value
    Else
        Singleton.StaticCount = value
    End If
End Property

Public Sub constructor()
    StaticCount = StaticCount + 1
End Sub

最小的代码+构造函数的参数

(在此建议，以防万一您不确定如何混合上面的代码在VBA中构造函数的参数)

模块Module1:

Sub ABC()
    Set instance1 = New_clsClass(41)
    Set instance2 = New_clsClass(42)
    Debug.Print "Result: " & instance1.StaticCount 'This returns 2
    End ' Reset Global/Static memory to clear "Static Singleton" (and whole VBProject memory)
End Sub

Function New_clsClass(arg1 As Integer) As clsClass
    Set Object = New clsClass
    Static Singleton As clsClass
    If Singleton Is Nothing Then
        Set Singleton = New clsClass
    End If
    Set Object.Singleton = Singleton
    Call Object.Constructor(arg1)
    Set New_clsClass = Object
End Function

Class Module clsClass:

Private arg1_ As Integer
Private StaticCount_ As Integer
Private Singleton_ As clsClass

Private Static Property Get Singleton() As Object
    Set Singleton = Singleton_
End Property

Private Property Set Singleton(Object As Object)
    Set Singleton_ = Object
End Property

Public Property Get StaticCount() As Integer
    If Singleton Is Nothing Then
        StaticCount = StaticCount_
    Else
        StaticCount = Singleton.StaticCount
    End If
End Property

Private Property Let StaticCount(value As Integer)
    If Singleton Is Nothing Then
        StaticCount_ = value
    Else
        Singleton.StaticCount = value
    End If
End Property

Public Function Constructor(arg1 As Integer)
    arg1_ = arg1
    StaticCount = StaticCount + 1
End Function

I want to keep track of all instances of a Particular Class in Excel-VBA, like Static Member in VB.Net. So here is my Class Module:

ClassModule: clsClass

Private pName as String
'Static pCount Commented as it doesnt work

Property Set Name(arg as String)
    pName=arg
End Property

Private Sub Class_Initialize()
    'pCount = pCount + 1  Commented as it doesnt work 
End Sub

Public Function GetCount()
    GetCount = pCount
End Function

and my Generic Module Module: Module1

Sub ABC()

Dim instance1 As New clsClass
Dim instance2 As New clsClass
Dim instance3 As New clsClass
Dim instance4 As New clsClass
'Debug.Print instance4.GetCount() This Should Return 4, but doesnt
End Sub

What am I doing Wrong ? How do i declare a variable that is shared across all of instances ?

解决方案

Smallest code

Based on logic described in Class (Static) Methods in VBA.

The static property StaticCount is incremented in the Constructor method. The most important code to support the static attributes is mentioned in the Get and Let Property procedures (If Singleton Is Nothing Then ... Else ... End If).

The drawback here is that it uses End to clear the global/static variables so that it prints 2 every time you call ABC, but it also clears all global variables of the VBProject that you may not wish. If End is not used, it would print 2, 4, 6, +2 each time you call ABC. See a workaround in the next chapter.

Module Module1:

Sub ABC()
    Set instance1 = New_clsClass()
    Set instance2 = New_clsClass()
    Debug.Print "Result: " & instance1.StaticCount 'This returns 2
    End ' Reset Global/Static memory to clear "Static Singleton" (and whole VBProject memory)
End Sub

Function New_clsClass() As clsClass
    Set Object = New clsClass
    Static Singleton As clsClass
    If Singleton Is Nothing Then
        Set Singleton = New clsClass
    End If
    Set Object.Singleton = Singleton
    Call Object.Constructor
    Set New_clsClass = Object
End Function

Class Module clsClass:

Private StaticCount_ As Integer
Private Singleton_ As clsClass

Private Static Property Get Singleton() As Object
    Set Singleton = Singleton_
End Property

Private Property Set Singleton(Object As Object)
    Set Singleton_ = Object
End Property

Public Property Get StaticCount() As Integer
    If Singleton Is Nothing Then
        StaticCount = StaticCount_
    Else
        StaticCount = Singleton.StaticCount
    End If
End Property

Private Property Let StaticCount(value As Integer)
    If Singleton Is Nothing Then
        StaticCount_ = value
    Else
        Singleton.StaticCount = value
    End If
End Property

Public Sub Constructor()
    StaticCount = StaticCount + 1
End Sub

Smallest code + way to control what Global/Static variables you reset, instead of using End

This solution is based on a global variable which retains the singletons, so you may reset the singletons when you run ABC. Here, all singletons are reset except the ones of the classes clsClass74 and clsClass75 which are to keep their static properties alive "all the time".

Module Module1:

Global goSingletons As New Collection

Sub ABC()
    Call ResetGlobalMemory
    Set instance1 = New_clsClass()
    Set instance2 = New_clsClass()
    Debug.Print "Result: " & instance1.StaticCount 'This returns 2
End Sub

Sub ResetGlobalMemory()
    ' Reset all singletons except the one of clsClass7
    For i = goSingletons.count To 1 Step -1
        Select Case TypeName(goSingletons(i))
            Case "clsClass74", "clsClass75"
            Case Else
                Call goSingletons.Remove(i)
        End Select
    Next
End Sub

Function New_clsClass() As clsClass
    Set Object = New clsClass
    Set Object.Singleton = GetSingleton("clsClass")
    Call Object.Constructor
    Set New_clsClass = Object
End Function

Function GetSingleton(ClassName As String)
    On Error Resume Next
    Set Singleton = goSingletons(ClassName)
    If Err.Number <> 0 Then
        On Error GoTo 0
        Select Case ClassName
            Case "clsClass": Set Singleton = New clsClass
            Case "clsClass2": Set Singleton = New clsClass2
            Case Else: Err.Raise 9999, , "Singleton not managed by class " & ClassName
        End Select
        Call goSingletons.Add(Singleton, ClassName)
    End If
    Set GetSingleton = Singleton
End Function

Class Module clsClass:

Private StaticCount_ As Integer
Private Singleton_ As clsClass

Private Static Property Get Singleton() As Object
    Set Singleton = Singleton_
End Property

Private Property Set Singleton(Object As Object)
    Set Singleton_ = Object
End Property

Public Property Get StaticCount() As Integer
    If Singleton Is Nothing Then
        StaticCount = StaticCount_
    Else
        StaticCount = Singleton.StaticCount
    End If
End Property

Private Property Let StaticCount(value As Integer)
    If Singleton Is Nothing Then
        StaticCount_ = value
    Else
        Singleton.StaticCount = value
    End If
End Property

Public Sub constructor()
    StaticCount = StaticCount + 1
End Sub

Smallest code + argument to constructor

(proposed here just in case you are not sure how to mix the solution of passing arguments to constructor in VBA with the code above)

Module Module1:

Sub ABC()
    Set instance1 = New_clsClass(41)
    Set instance2 = New_clsClass(42)
    Debug.Print "Result: " & instance1.StaticCount 'This returns 2
    End ' Reset Global/Static memory to clear "Static Singleton" (and whole VBProject memory)
End Sub

Function New_clsClass(arg1 As Integer) As clsClass
    Set Object = New clsClass
    Static Singleton As clsClass
    If Singleton Is Nothing Then
        Set Singleton = New clsClass
    End If
    Set Object.Singleton = Singleton
    Call Object.Constructor(arg1)
    Set New_clsClass = Object
End Function

Class Module clsClass:

Private arg1_ As Integer
Private StaticCount_ As Integer
Private Singleton_ As clsClass

Private Static Property Get Singleton() As Object
    Set Singleton = Singleton_
End Property

Private Property Set Singleton(Object As Object)
    Set Singleton_ = Object
End Property

Public Property Get StaticCount() As Integer
    If Singleton Is Nothing Then
        StaticCount = StaticCount_
    Else
        StaticCount = Singleton.StaticCount
    End If
End Property

Private Property Let StaticCount(value As Integer)
    If Singleton Is Nothing Then
        StaticCount_ = value
    Else
        Singleton.StaticCount = value
    End If
End Property

Public Function Constructor(arg1 As Integer)
    arg1_ = arg1
    StaticCount = StaticCount + 1
End Function

这篇关于如何在VBA中为类模块声明静态变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！