是否可以在Excel中的VBA中永久存储数组元素？

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.

诀窍是我不想在工作表上存储元素并在工作簿打开时将它们插回到数组，而是将元素永久保存在数组中。

5 个解决方案

#1

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的评论中的优秀建议）

#2

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

#3

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代码看到，所以对于用户来说，它与数组始终在那里并在内存中活动是一回事。

#4

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 Visible property of sheet to hide as we have responded.

在我们响应后，您可以使用工作表的Visible属性隐藏。

#5

"there is no way..." , >>> yes can do !

“没有办法......”，>>>是的可以做到！

some examples : listbox/ComboListBox (on a sheet) = array ...

一些例子：listbox / ComboListBox（在工作表上）=数组...

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...

或重新加载工作簿后保留...

#1