VBA中替代公共变量的方法

Question

我有许多公共变量被多个模块引用。如果你调试或停止程序，这些变量就会被清空。我一直把这些变量写入电子表格，以防它们被清空，但这很混乱。我更希望能够将它们都存储在代码中。有没有什么替代公共变量且不会被清空的方法？

- user2242044

---

你可以使用CustomDocumentProperties集合或Names集合。 - David Zemens

有一个子程序可以设置它们所有的内容，这样每次只需要运行它即可吗？ - baarkerlounger

另外，FYI，仅通过进入中断模式无法清除变量，只有在结束执行时才能清除。 - David Zemens

可能是全局变量失去其值的重复问题。 - RubberDuck

Answer 1

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

以下示例仅存储字符串数据，但您还可以使用日期、数字和Yes/No（如果你愿意可以将其替换为布尔值）。对于字符串数据，字符数限制为255个。

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

   End Sub

你可以从后台|信息|属性|高级属性|自定义中查看CPD： 如果你在运行时结束，可以从CDP恢复值，可以通过以下方式查询属性值： myVar = ActiveWorkbook.CustomDocumentProperties("myProperty").Value 你可以使用这样的函数来设置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

- David Zemens

---

+1 - 我首选的方法。值得注意的是，在名称之外，还有一个隐藏的命名空间，可以在需要将名称隐藏不让电子表格用户看到时使用。(更多信息请参见http://www.cpearson.com/excel/hidden.htm) - Ioannis

@loannis 哦，那也很棒！！ - David Zemens

Answer 2

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

编辑：另请参阅John Walkenbach的书以获取更多信息。

编辑：请参见Ioannis的下面评论，其中包含一个重要的注意事项。

---

典型警告：这里有龙, 在Windows注册表上胡乱更改会有危险，等等。

---

尽管上述警告令人担忧，但请注意，几乎每个在Windows计算机上运行的程序都会对注册表进行某些操作，这本身并不危险。只需确保您的代码仅更改/删除由您的Excel应用程序创建的注册表键即可。

---

以下是使用Windows脚本编写的示例程序（非本人编写，来源于快速搜索）：

从注册表中读取：

'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

检查注册表键是否存在：

'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

保存注册表键：

'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

从注册表中删除密钥：

'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

- Rick

---

在这种情况下，当用户使用相同的代码打开第二个文件时，需要注意变量可能会混乱。有方法来处理这个问题，只需要稍加小心即可。 - Ioannis

是的，这是真的。规避这种情况的一种方法是为每个过程实例拥有单独的注册表键。我相信Walkenbach编写的过程包括此功能。 - Rick

几周前，我曾经点赞过这个巧妙的方法（我认为对于大多数 Excel 应用程序来说有些过度杀伤力，但因人而异），但现在我确实需要在 PowerPoint 中使用它，进行插件管理/版本控制 :) - David Zemens

Answer 3

如果您的代码中仍然有指向公共变量的引用，那么在调试（断点）模式下，这些变量不会从内存中被清除。实际上，在某些情况下，如果您将鼠标移动到变量上方，它会告诉您断点处的值。

如果您想要变量持久化，我建议使用与当前使用相同的方法（将它们写在Excel工作表上）或者使用数据库。

如果您确实将这些变量写入工作表，请不要直接修改变量，而是使用适合您模型的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

- BrainO2

Answer 4

TempVars集合是公共变量的良好替代品。它不会在会话之间保存，但是它会在整个会话期间保持不变。

但是要使用TempVars，您需要添加对Microsoft Access 14.0 Object Library的引用。从2007年起才有TempVars可用。

- RubberDuck

Answer 5

这里有另一种解决方案，可能比我的第一个答案（使用注册表）更可取，具体取决于用例。您可以将值存储在"非常隐藏"工作表中。这样做有以下优点：

1. 防止变量被意外删除（它们对用户完全不可见）
2. 允许多个副本的工作簿拥有自己版本的变量，而不是所有实例都从注册表访问相同的值
3. 防止工作簿的多个实例同时尝试编辑注册表键
4. 允许工作簿在多台机器上使用后保留值，通过电子邮件等方式传递

- Rick