VBA中公共变量的替代方案-AlternativestoPublicVariablesinVBA

6  

A simple solution would be to store your variables in the registry, and just read/write them as necessary. This has the added benefit of preserving values over multiple Excel sessions (and even after a computer reboot, or a crash - assuming your registry survived it!).

一个简单的解决方案是将变量存储在注册表中，并根据需要读取/写入它们。这具有在多个Excel会话中保留值的额外好处（甚至在计算机重新启动或崩溃之后 - 假设您的注册表幸免于难！）。

EDIT: Also see John Walkenbach's book for more information on this.

编辑：有关此问题的更多信息，请参阅John Walkenbach的书。

EDIT: See below comment by Ioannis for an important consideration.

编辑：请参阅Ioannis的以下评论作为重要考虑因素。

Boilerplate warning: Here be dragons, Twiddle with the Windows registry at your peril, etc etc.

Boilerplate警告：这里有龙，Twiddle与Windows注册表在你的危险等等。

The above warning notwithstanding, realize that almost every program on your Windows computer does something with the registry, and it is not inherently dangerous to do so. Just make sure your code only changes/deletes registry keys which were created by your Excel application.

尽管如此，上述警告意识到Windows计算机上几乎每个程序都对注册表执行某些操作，并且这样做本身并不危险。只需确保您的代码仅更改/删除由Excel应用程序创建的注册表项。

Example procedures using Windows Scripting (I didn't write these; from a quick search):

使用Windows Scripting的示例程序（我没有写这些;来自快速搜索）：

Reading from the Registry:

'reads the value for the registry key i_RegKey
'if the key cannot be found, the return value is ""
Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

Checking if a Registry key exists:

'returns True if the registry key i_RegKey was found
'and False if not
Function RegKeyExists(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'try to read the registry key
  myWS.RegRead i_RegKey
  'key was found
  RegKeyExists = True
  Exit Function

ErrorHandler:
  'key was not found
  RegKeyExists = False
End Function

Saving a Registry key:

'sets the registry key i_RegKey to the
'value i_Value with type i_Type
'if i_Type is omitted, the value will be saved as string
'if i_RegKey wasn't found, a new registry key will be created
Sub RegKeySave(i_RegKey As String, _
               i_Value As String, _
      Optional i_Type As String = "REG_SZ")
Dim myWS As Object

  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'write registry key
  myWS.RegWrite i_RegKey, i_Value, i_Type

End Sub

Deleting a key from the Registry:

'deletes i_RegKey from the registry
'returns True if the deletion was successful,
'and False if not (the key couldn't be found)
Function RegKeyDelete(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'delete registry key
  myWS.RegDelete i_RegKey
  'deletion was successful
  RegKeyDelete = True
  Exit Function

ErrorHandler:
  'deletion wasn't successful
  RegKeyDelete = False
End Function

9  

Here is an example of the CustomDocumentProperties, which I recently started using to store some meta-information (easier than dealing with the CustomXMLParts).

下面是CustomDocumentProperties的一个示例，我最近开始使用它来存储一些元信息（比处理CustomXMLParts更容易）。

The examples below store only string data, but you can also use date, number and Yes/No (which with some finagling you could sub as a Boolean). You are limited to 255 characters for string data.

下面的示例仅存储字符串数据，但您也可以使用日期，数字和是/否（可以使用某些标记作为布尔值）。字符串数据限制为255个字符。

   Sub Test()
   '## Assign a CDP
   SetCustomProperty "myProperty", "some value I want to store"

   End Sub

You can view the CPD's from the Backstage | Info | Properties | Advanced Properties | Custom:

您可以从Backstage中查看CPD信息|属性|高级属性|自定义：

In the event that you End run-time, you can restore the values from the CDP, you can query the property value by:

如果您结束运行时，您可以从CDP恢复值，您可以通过以下方式查询属性值：

myVar = ActiveWorkbook.CustomDocumentProperties("myProperty").Value

myVar = ActiveWorkbook.CustomDocumentProperties（“myProperty”）。值

You can use functions like these to set properties in the CustomDocumentProperties collection:

您可以使用这些函数在CustomDocumentProperties集合中设置属性：

Sub SetCustomProperty(property$, val$)
    Dim cdp As Variant
    Dim hasProperty As Boolean
    If HasCustomProperty(property) Then
        ActiveWorkbook.CustomDocumentProperties(property).Value = val
    Else
        ActiveWorkbook.CustomDocumentProperties.Add property, False, msoPropertyTypeString, val

    End If
End Sub
Private Function HasCustomProperty(property$) As Boolean
Dim cdp As Variant
Dim boo As Boolean
For Each cdp In ActiveWorkbook.CustomDocumentProperties
    If cdp.name = property Then
        boo = True
        Exit For
    End If
Next
HasCustomProperty = boo
End Function

2  

Public variables don´t get erased from memory on debug (break) mode if there are still references that point to them further along in your code. In fact, in some cases if you move your mouse over the variable it will tell you the value at the break.

如果仍有引用在代码中进一步指向它们，则公共变量不会在调试（中断）模式下从内存中删除。事实上，在某些情况下，如果您将鼠标移到变量上，它会告诉您休息时的值。

If you want variables to persist I would use the same method as you are currently using (write them down on an excel worksheet) or a database.

如果你想要保持变量，我将使用你当前使用的相同方法（在excel工作表上写下来）或数据库。

If you do write these variables down, I would recommend you never modify the variable directly, instead use setters and getters that suit your model. For example, if you write them down to a worksheet you might use the following:

如果你确实写下这些变量，我建议你不要直接修改变量，而是使用适合你模型的setter和getter。例如，如果将它们写到工作表，则可以使用以下命令：

public sub setVariable(v as String)
     worksheets("Sheet1").Range("A1").value = v
end sub

public function getVariable() as String
    getVariable = worksheets("Sheet1").range("A1").value
End Function

1  

Here is another solution that might be preferable to my first answer (using the registry) depending on the use case.

这是另一种解决方案，可能比我的第一个答案（使用注册表）更可取决于具体用例。

You could store the values in a "Very Hidden" worksheet. This has the following advantages:

您可以将值存储在“非常隐藏”的工作表中。这具有以下优点：

1. Prevents the variables from accidentally being destroyed (they are completely invisible to the user)
2. 防止变量被意外破坏（它们对用户完全不可见）
3. Allows multiple copies of the workbook to have their own versions of the variables instead of all instances accessing the same values from the registry
4. 允许工作簿的多个副本具有自己的变量版本，而不是从注册表访问相同值的所有实例
5. Prevents multiples instances of the workbook from trying to edit the registry keys at the same time
6. 防止工作簿的多个实例同时尝试编辑注册表项
7. Allows the workbook to maintain the values after being used on multiple machines, emailed, etc etc
8. 允许工作簿在多台计算机上使用，通过电子邮件发送等后维护这些值

1  

The TempVars Collection is a great alternative to Public Variables. It won't persist between sessions, but it will persist for the entirety of A session.

TempVars Collection是Public Variables的绝佳替代品。它不会在会话之间持续存在，但它会持续整个A会话。

You will need to add a reference to Microsoft Access 14.0 Object Library though. TempVars are only available from 2007 onward.

您需要添加对Microsoft Access 14.0对象库的引用。 TempVars仅从2007年开始提供。