VBA：在 Excel 中创建会话持久对象（哈希）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9496810/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA: Creating session persistent objects (hash) in Excel
提问by Richard H
Is it possible in a VBA function (UDF) to create an object that has global scope? I.e persists beyond the runtime of the function? I would want to stick it in a hash with a unique key that I can pass to other functions. I know you can do this in c#/c++ dll's.
是否可以在 VBA 函数 (UDF) 中创建具有全局作用域的对象？即持续超出函数的运行时？我想将它粘贴在一个带有唯一键的散列中，我可以将它传递给其他函数。我知道你可以在 c#/c++ dll 中做到这一点。
The motivation is a heavy duty piece of processing that I don't want to repeat across hundreds of function calls: I want to cache the results so I only need to do once. E.g let's imagine I have a UDF which builds the results object in Cell A1:
动机是我不想在数百个函数调用中重复的繁重处理：我想缓存结果，所以我只需要做一次。例如，让我们假设我有一个 UDF，它在单元格 A1 中构建结果对象：
=CreateResultsObject(arg1, arg2, arg3...)
The function does the heavy work and returns a unique ID string (the key for the object stored in the persistent hash). Cell A1 now contains this string value which I can then pass to other functions: they can then access the cached object in the hash with the key.
该函数执行繁重的工作并返回一个唯一的 ID 字符串（存储在持久哈希中的对象的键）。单元格 A1 现在包含这个字符串值，然后我可以将其传递给其他函数：然后它们可以使用键访问哈希中的缓存对象。
Is this possible? If so how?
这可能吗？如果是这样怎么办？
回答by Roman
The variables you declare in a module are persistent.
您在模块中声明的变量是持久的。
This code in a module might go into the direction you want:
模块中的此代码可能会朝着您想要的方向发展：
Option Explicit
Dim col As New Collection
Public Function GetValue(ByVal strName As String) As String
GetValue = col.Item(strName)
End Function
Public Sub SetValue(ByVal strName As String, ByVal strValue As String)
col.Add strValue, strName
End Sub
Note:
笔记：
For duplicate or missing names the code will fail. Instead of a string value any kind of object could be passed by modifying the function signatures accordingly.
对于重复或缺失的名称，代码将失败。可以通过相应地修改函数签名来传递任何类型的对象，而不是字符串值。
Addendum:
附录：
The same code with a bit more intelligence - for existing keys in the collection the value will be replaced instead of failing with an error.
具有更多智能的相同代码 - 对于集合中的现有键，值将被替换，而不是因错误而失败。
Option Explicit
Dim col As New Collection
Public Function GetValue(ByVal strName As String) As String
GetValue = col.Item(strName)
End Function
Public Sub SetValue(ByVal strName As String, ByVal strValue As String)
If HasValue(strName) Then
col.Remove (strName)
End If
col.Add strValue, strName
End Sub
Private Function HasValue(ByVal strName As String) As Boolean
Dim val As Variant
Dim bRes As Boolean
bRes = True
On Error Resume Next
val = col.Item(strName)
If Err.Number <> 0 Then
bRes = False
Err.Clear
End If
On Error GoTo 0
HasValue = bRes
End Function
回答by JMax
What about using a global variable within a module?
在模块中使用全局变量怎么样？
Something like this:
像这样的东西：
Option Explicit
Dim sHash As String
Function CreateResultsObject()
'very long code
sHash = "MyTest"
CreateResultsObject = "ok"
End Function
Function displayresultsobject()
displayresultsobject = sHash
End Function
Note that your Hash will be recalculated only when you call CreateResultsObject()in your worksheet and each time you ask for recalculation.
请注意，只有在您调用CreateResultsObject()工作表和每次要求重新计算时，才会重新计算您的哈希。
