我有许多公共变量被多个模块引用。如果你调试或停止程序,这些变量就会被清空。我一直把这些变量写入电子表格,以防它们被清空,但这很混乱。我更希望能够将它们都存储在代码中。有没有什么替代公共变量且不会被清空的方法?
这是一个CustomDocumentProperties的示例,我最近开始使用它来存储一些元信息(比处理CustomXMLParts更容易)。
以下示例仅存储字符串数据,但您还可以使用日期、数字和Yes/No(如果你愿意可以将其替换为布尔值)。对于字符串数据,字符数限制为255个。
Sub Test()
'## Assign a CDP
SetCustomProperty "myProperty", "some value I want to store"
End Sub
你可以从后台|信息|属性|高级属性|自定义中查看CPD:
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
典型警告:这里有龙, 在Windows注册表上胡乱更改会有危险,等等。
以下是使用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
如果您的代码中仍然有指向公共变量的引用,那么在调试(断点)模式下,这些变量不会从内存中被清除。实际上,在某些情况下,如果您将鼠标移动到变量上方,它会告诉您断点处的值。
如果您想要变量持久化,我建议使用与当前使用相同的方法(将它们写在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
TempVars
,您需要添加对Microsoft Access 14.0 Object Library的引用。从2007年起才有TempVars
可用。
CustomDocumentProperties
集合或Names
集合。 - David Zemens