是否可以使用VBA自定义类的有状态默认实例？

我正在开发一些与财务相关的Excel加载项，供我的商业团队中的一小组使用。

我想创建一个持久对象，该对象存储与应用程序相关的特定元数据值的状态，以便任何类或标准模块中的代码都可以访问它们，因此我只需要声明一次或在应用程序启动时设置其默认值。示例包括：

• 应用名称
• 发行/版本号
• 用于存储/检索设置的顶级注册表项
• “首次运行”状态
• 正在启用日志记录

在过去，我会为这些事情声明全局常量，但是我试图使用一种最新的模式（如果有的话）来解决这个问题。

我的想法是创建一个PredeclaredID属性设置为true的类，并在工作簿加载和/或类初始化事件中填充默认值。

我知道在类的默认实例中维护状态不是一种好的做法，但是我想不出另一种创建持久对象的方法。我对工厂模式有些了解，很乐意使用一种工厂模式，但是持久性却让我失望。

任何见解或替代方法表示赞赏。

更新2020年6月10日

我在这种方法上取得了一些成功。这是我一直在研究的原型，结合了freeflow的有用建议。

类属性和属性过程

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "AppSettings"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Attribute VB_Description = "Persistent object that exposes app-specific settings to other procedures and objects"
'@Folder "MyAddin.02 AddIn"
'@ModuleDescription ("Persistent object that exposes app-specific settings to other procedures and objects")
'@PredeclaredId
Option Explicit

Private Type TAppSettings
    Name As String
    LoggingEnabled As Boolean
    Author As String
    AuthorEmail As String
    ReleaseName As String
    Version As String
    TopLevelRegKey As String
    AppFirstRun As Boolean
    LogPath As String
End Type

Private this As TAppSettings

Public Property Get Name() As String
    Name = this.Name
End Property

Public Property Get LoggingEnabled() As Boolean
    LoggingEnabled = this.LoggingEnabled
End Property

Public Property Let LoggingEnabled(ByVal value As Boolean)
    this.LoggingEnabled = value
End Property

Public Property Get Author() As String
    Author = this.Author
End Property

Public Property Get AuthorEmail() As String
    AuthorEmail = this.AuthorEmail
End Property

Public Property Get ReleaseName() As String
    ReleaseName = this.ReleaseName
End Property

Public Property Get Version() As String
    Version = this.Version
End Property

Public Property Get TopLevelRegKey() As String
    TopLevelRegKey = this.TopLevelRegKey
End Property

Public Property Get AppFirstRun() As Boolean
    AppFirstRun = this.AppFirstRun
End Property

Public Property Let AppFirstRun(ByVal value As Boolean)
    this.AppFirstRun = value
End Property

bootstrapper：此过程将应用程序的默认值传递给Populate方法，并从类Initialize事件中调用。这些属性在此处硬编码为常量，但可以从工作簿自定义属性或外部设置文件中获取。

'@Description "Sets properties of default AppSettings object. Called by class initialise event"
Private Sub BootStrapper()

    Const APP_NAME As String = "MyTestApp"
    Const APP_AUTHOR As String = "John Smith"
    Const APP_AUTHOR_EMAIL As String = "[email protected]"
    Const APP_RELEASE_NAME As String = "R1"
    Const APP_VERSION As String = "V1.0"
    Const APP_FIRSTRUN_DEFAULT As Boolean = True
    Const APP_LOGGING_DEFAULT As Boolean = False

    Dim success As Boolean

    On Error Resume Next
    success = Me.Populate( _
              Author:=APP_AUTHOR, _
              AuthorEmail:=APP_AUTHOR_EMAIL, _
              Name:=APP_NAME, _
              ReleaseName:=APP_RELEASE_NAME, _
              Version:=APP_VERSION, _
              AppFirstRunDefault:=APP_FIRSTRUN_DEFAULT, _
              AppLoggingDefault:=APP_LOGGING_DEFAULT)
    On Error GoTo 0

    If Not success Then
    Err.Raise _
        vbObjectError + 1024, _
        "AppSettings.Bootstrapper", _
        "Failed to bootstrap the static AppSettings class"
    End If

End Sub

Populate方法：设置类属性，创建顶级注册表项，并在注册表中读取/存储读取/写入属性的设置。

'@Description "Populates AppSettings object properties"
Friend Function Populate( _
       ByVal Author As String, _
       ByVal AuthorEmail As String, _
       ByVal Name As String, _
       ByVal ReleaseName As String, _
       ByVal Version As String, _
       ByVal AppFirstRunDefault As Boolean, _
       ByVal AppLoggingDefault As Boolean) As Boolean

    Populate = False
    On Error Resume Next                         'Handle any errors by function return value

    ' Populate properties for those values passed in to the function
    With this
        .Author = Author
        .AuthorEmail = AuthorEmail
        .Name = Name
        .ReleaseName = ReleaseName
        .Version = Version
        .TopLevelRegKey = .ReleaseName & "\" & .Version
    End With

    ' Populate the AppFirstRun and LoggingEnabled settings from the registry
    ' If registry settings don't exist then this is the true first run so set defaults
    Dim registryValue As String
    With this
        registryValue = GetSetting(.Name, .TopLevelRegKey, "AppFirstRun", vbNullString)
        If registryValue = vbNullString Then     'Set default if no registry value
            .AppFirstRun = AppFirstRunDefault
            SaveSetting .Name, .TopLevelRegKey, "AppFirstRun", CStr(AppFirstRunDefault)
        Else
            .AppFirstRun = CBool(registryValue)
        End If
        registryValue = GetSetting(.Name, .TopLevelRegKey, "LoggingEnabled", vbNullString)
        If registryValue = vbNullString Then     'Set default if no registry value
            .LoggingEnabled = AppLoggingDefault
            SaveSetting .Name, .TopLevelRegKey, "LoggingEnabled", CStr(AppLoggingDefault)
        Else
            .LoggingEnabled = CBool(registryValue)
        End If
    End With

    If Err.Number = 0 Then Populate = True
    On Error GoTo 0

End Function

初始化过程：该Class_Initialize代码检查调用方是否在尝试新建该类的另一个实例，如果没有，则调用引导程序。如果对象超出范围（例如通过Set AppSettings = Nothing），则在重新生成默认实例时会自动重新填充属性。

Private Sub Class_Initialize()
' AppSettings is a stateful default instance singleton class
' Prevent additional instance creation with "New"

    If Not Me Is AppSettings Then
        Err.Raise _
            vbObjectError + 1024, _
            "Invalid Object Instance", _
            TypeName(Me) & ": New is not permitted on Static Classes"
    End If

    'Call BootStrapper procedure to auto-initialise properties
    BootStrapper

End Sub