VBA中公共变量的替代方案

问题描述

我有一些公共变量在多个模块中引用。我知道如果你调试或点击停止变量被清除。我已经将这些变量写入电子表格，所以我有这些变量被清除，但是这是混乱的。我宁愿把它全部存储起来。有一个公共变量的替代方案从未被清除？

解决方案

一个简单的解决方案是将变量存储在注册表中，只要读/写就可以了。这具有在多个Excel会话（甚至在计算机重新启动或崩溃之后）保留值的附加好处 - 假设您的注册表幸存下来！

编辑：另请参阅，了解更多信息。

编辑：看到以下评论由Ioannis重要考虑。

---

锅炉警告： ，Twils与Windows注册表在你的危险等等。

---

尽管如此，上述警告意识到，Windows计算机上的几乎每个程序都与注册表一起使用某些东西，这样做并不是固有的危险。只需确保您的代码仅更改/删除由Excel应用程序创建的注册表项 。

---

使用Windows Scripting的示例程序（我没有写这些;从）：

从注册表读取：

 '读取注册表项的值i_RegKey 
'如果无法找到该键，返回值为
函数RegKeyRead（i_RegKey As String）As String 
 Dim myWS As Object 
 
 On Error Resume Next 
'访问Windows脚本
设置myWS = CreateObject（WScript。 Shell $）
'从注册表读取密钥
 RegKeyRead = myWS.RegRead（i_RegKey）
结束函数
  

检查注册表项是否存在：

 '如果发现注册表项i_RegKey，则返回True 
'和False如果不是
函数RegKeyExists（i_RegKey As String）As Boolean 
 Dim myWS As Object 
 
错误GoTo ErrorHandler 
'访问Windows脚本ting 
设置myWS = CreateObject（WScript.Shell）
'尝试读取注册表项
 myWS.RegRead i_RegKey 
'键被发现
 RegKeyExists = True 
退出函数
 
 ErrorHandler：
'键未找到
 RegKeyExists = False 
结束函数
  / pre> 
 
 
保存注册表项：
 
 
 
 '设置注册表键i_RegKey到
'值i_Value，类型为i_Type 
'如果省略i_Type，该值将被保存为字符串
'如果没有找到i_RegKey，将创建一个新的注册表项
 Sub RegKeySave（i_RegKey As String，_ 
 i_Value As String，_ 
可选i_Type As String =REG_SZ）
 Dim myWS As Object 
 
 '访问Windows脚本
设置myWS = CreateObject（WScript.Shell）
'写注册表项
 myWS.RegWrite i_RegKey，i_Value，i_Type 
 
 End Sub 
  
 
 
 
从注册表中删除密钥
 
 
 
 
从注册表中删除i_RegKey 
'如果删除成功，则返回True，
'和False如果没有（无法找到密钥）
函数RegKeyDelete（i_RegKey As String）As Boolean 
 Dim myWS As Object 
 
错误GoTo ErrorHandler 
'访问Windows脚本
设置myWS = CreateObject（WScript.Shell）
'删除注册表项
 myWS.RegDelete i_RegKey 
'删除成功
 RegKeyDelete = True 
退出函数
 
 ErrorHandler：
'删除不成功
 RegKeyDelete = False 
结束函数
  / pre> 
I have a number of public variables that are referenced across several modules. I know if you debug or hit stop the variable gets cleared out. I have been writing these variables to a spreadsheet so I have them in case they get cleared out, but this is messy. I'd rather have it all stored it code. Is there any alternative to a public variable that never gets cleared?
 解决方案 
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!). 
EDIT: Also see John Walkenbach's book for more information on this.
EDIT: See below comment by Ioannis for an important consideration. 
Boilerplate warning: Here be dragons, Twiddle with the Windows registry at your peril, etc etc.
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. 
Example procedures using Windows Scripting (I didn't write these; from a quick search):
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
                        
这篇关于VBA中公共变量的替代方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！