是否可以在 Excel 的 VBA 中永久存储数组元素？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7589579/
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
Is it possible to store elements of array permanently in VBA in Excel?
提问by user652061
I wrote macro in Excel that uses dynamic array. Users will add elements to that array.
我在 Excel 中编写了使用动态数组的宏。用户将向该数组添加元素。
Is it possible to store elements permanently, so the elements will be available even after workbook was closed?
是否可以永久存储元素，以便即使在工作簿关闭后元素仍然可用？
The trick is that I do not want to store elements on a worksheet and insert them back to array when workbook is open, but to have elements permanently saved in array.
诀窍是我不想将元素存储在工作表上并在工作簿打开时将它们插入回数组，而是将元素永久保存在数组中。
回答by Raj More
One of your best bets is to store the array values in a worksheet and mark the worksheet as hidden using VBA.
最好的办法之一是将数组值存储在工作表中，并使用 VBA 将工作表标记为隐藏。
Me.Worksheets("ArrayValuesWorksheet").Visible = False
If you use other things like local CSV files, registry, etc then the code will behave differently by simply moving the workbook to different machines and you will lose the ability to have multiple copies of the array.
如果您使用其他内容，如本地 CSV 文件、注册表等，那么只需将工作簿移动到不同的机器上，代码的行为就会有所不同，并且您将无法拥有数组的多个副本。
Edit(Excellent suggestion in @Reafidy's comments below)
编辑（下面@Reafidy 的评论中的极好建议）
回答by RonnieDickson
You can also store an array in a name from the names collection, and this name can be hidden.
您还可以将数组存储在名称集合中的名称中，并且可以隐藏该名称。
Names.Add Name:="StoredArray", RefersTo:=myArray, Visible:=False
回答by aevanko
To answer your question directly: No, there is no way to store an array that has a lifetime beyond the application in memory - you'll have to save it somewhere.
直接回答你的问题：不，没有办法在内存中存储一个生命周期超出应用程序的数组 - 你必须将它保存在某个地方。
There are several options available but your best is most likely saving it to a sheet. 'Very hidden' sheets are only visible through VBA code so to the user it's the same thing as the array always being there and active in memory.
有多种选项可用，但最好的方法是将其保存到工作表中。“非常隐藏”的工作表只能通过 VBA 代码看到，因此对用户来说，它与数组始终存在并在内存中处于活动状态是一样的。
回答by Patrick Lepelletier
"there is no way..." , >>> yes can do !
“没有办法......”，>>> 是的可以做到！
some examples : listbox/ComboListBox (on a sheet) = array ...
一些例子：listbox/ComboListBox（在一张纸上）= array ...
For 2D variant array : range()=array.
对于 2D 变体数组：range()=array。
Or store it in a commandbarMenu (those can be made multidimensional)
或将其存储在 commandbarMenu 中（可以制作多维）
or whateverthat keeps after reloading workbook...
或重新加载工作簿后保留的任何内容...
回答by Bruno Leite
To read/write Array in sheet you can use
要在工作表中读/写数组，您可以使用
Sub WriteArray()
Dim MyArray As Variant
MyArray = Array("x", "y", "z")
Range("A1:C1").Value = MyArray
End Sub
Sub ReadArray()
Dim MyArray As Variant
MyArray = Range("A1:C1").Value
End Sub
After you can use the Visibleproperty of sheet to hide as we have responded.
之后您可以使用Visiblesheet的属性进行隐藏，我们已经做出了回应。
