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

Question

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

Is it possible to permanently store items, so the items will be available even after closing the book?

The trick is that I don't want to store elements on a sheet and paste them back into the array when the workbook is open, but to save the elements in the array.

Answer 1

One of your best bet is to save the array values ​​on a worksheet and mark the worksheet as hidden using VBA.

Me.Worksheets("ArrayValuesWorksheet").Visible = False

, CSV, .., -, , .

( @Reafidy )

Answer 2

, .

Names.Add Name:="StoredArray", RefersTo:=myArray, Visible:=False

Answer 3

: , , - - .

, . " " VBA, , , .

Answer 4

/ ,

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 Visiblesheet property to hide how we answered.

Answer 5

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

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

For the 2D version of the array: range () = array.

Or save it on the Menu command line (they can be multidimensional)

or whatever remains after rebooting the workbook ...