在 Excel-VBA 中创建公共对象的最佳实践？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21380724/
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
Best practice for creating a public object in Excel-VBA?
提问by Evil Washing Machine
What is the best practice for creating an Excel-VBAdata object (dictionary, list, etc.) which is accessible by all members of the application? Should it be declared as a separate module or a class module?
创建Excel-VBA可由应用程序的所有成员访问的数据对象（字典、列表等）的最佳实践是什么？应该将其声明为单独的模块还是类模块？
For example, I want to create a dictionary object which different subroutines will want to check a user input against (if it contains or not). Should this dictionary object be its own module, class module, or part of the module which contains the subroutines who use it?
例如，我想创建一个字典对象，不同的子例程将要检查用户输入（如果它包含与否）。这个字典对象应该是它自己的模块、类模块还是包含使用它的子程序的模块的一部分？
Note: this question is an extension of Checking if a value is a member of a list
注意：这个问题是Checking if a value is a member of a list的扩展
采纳答案by Dmitry Pavliv
You can use following construction (declare your myListobject as Publicin the top of your module):
您可以使用以下构造（在模块顶部声明您的myList对象Public）：
Public myList As Object
Sub Main()
Call InitializeList
'Do something with your Dictionary object
End Sub
Sub InitializeList()
If Not myList Is Nothing Then Exit Sub
Set myList = CreateObject("Scripting.Dictionary")
myList.Add "item1", 1
myList.Add "item2", 2
myList.Add "item3", 3
End Sub
回答by livefree75
VBA can be frustrating to people who are used to nice OOP-friendly languages like Java and C#. We need to accept VBA's limitations and simply do the best we can with what it offers.
VBA 可能会让习惯了 Java 和 C# 等优秀的 OOP 友好语言的人感到沮丧。我们需要接受 VBA 的局限性，并尽我们所能地利用它提供的东西。
What you're describing almost sounds like something you would declare as a Singleton in other languages.
您所描述的几乎听起来像是您会在其他语言中声明为单例的东西。
My solution would be to create a "Main" module (not Class module). In there, create a private dictionary, and create a Public accessor function for it. This will allow your other methods - er - functions/subs to access it blindly.
我的解决方案是创建一个“主”模块（不是类模块）。在那里，创建一个私有字典，并为它创建一个公共访问器函数。这将允许您的其他方法 - 呃 - 函数/子程序盲目访问它。
Private pMyList as Scripting.Dictionary
Public Property Get MyList() as Scripting.Dictionary
If pMyList = Nothing Then
Set pMyList = new Scripting.Dictionary
pMyList("One") = "Red"
pMyList("Two") = "Blue"
pMyList("Three") = "Green"
EndIf
Set MyList = pMyList
End Property
Public Sub Cleanup
Set pMyList = Nothing
' To deallocate arrays, use:
' Erase pArray
End Sub
'--------------------------------
Public Sub SomeRandomSubInAnotherModule()
Dim theList As Scripting.Dictionary
Set theList = MyList ' If not yet initialized, will initialize
' Do whatever you need to do with theList
Set theList = Nothing ' Release the memory
End Sub
BTW, the "Cleanup" subroutine is just good practice. At the end of your macro, you should call the "Cleanup" subroutine to release memory that Excel may have allocated for any objects you've created. For Class Modules, you can put your cleanup code in
顺便说一句，“清理”子程序只是一个很好的做法。在宏结束时，您应该调用“Cleanup”子例程来释放 Excel 可能为您创建的任何对象分配的内存。对于类模块，您可以将清理代码放入
Public Sub Class_Terminate()
and it will be called automatically.
它会被自动调用。
Note - the previous code would require you to add the "Microsoft Scripting Runtime" as a reference. This gives you the helpful type hints when you're working with the dictionary while you code. If you don't want to do that for some reason, use this code:
注意 - 之前的代码需要您添加“Microsoft Scripting Runtime”作为参考。当您在编码时使用字典时，这会为您提供有用的类型提示。如果您出于某种原因不想这样做，请使用以下代码：
Private pMyList as Object
Public Property Get MyList() as Object
If pMyList = Nothing Then
Set pMyList = CreateObject("Scripting.Dictionary")
pMyList("One") = "Red"
pMyList("Two") = "Blue"
pMyList("Three") = "Green"
EndIf
Set MyList = pMyList
End Property
Public Sub Cleanup
Set pMyList = Nothing
End Sub
