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

Question

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 saved in array.

Answer 1

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)

Answer 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