Is it possible to store elements of array permanently in VBA in Excel?

I wrote macro in Excel that uses dynamic array. Users will add elements to that array.

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 sa开发者_StackOverflowved in array.

One of your best bets is to store the array values in a worksheet and mark the worksheet as hidden using 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.

Edit (Excellent suggestion in @Reafidy's comments below)

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

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.

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.

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

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

For 2D variant array : range()=array.

Or store it in a commandbarMenu (those can be made multidimensional)

or whateverthat keeps after reloading workbook...