Discussion: Persisting variable values—Named ranges vs hidden sheet

[MichaelJSchulz]

Let's say you have a variable, the value of which is determined with VBA code during a particular event, and you want to use that resulting value in the code that is run during a separate event.

I have seen several times the idea of writing those values to a hidden sheet so that the values could easily be retrieved at another time.

What I have not seen suggested is creating a Named range and setting that equal to the value. 

What are the pros and cons of these approaches and are there any other approaches?

[#2]

@MichaelJSchulz 

Both methods should work.

Another is to use a custom document property (View or change the properties for an Office file).

They can be set and retrieved in the interface and using VBA (Store Values in Custom Document Properties).

[#3]

@Hans Vogelaar
Thank you! I know both approaches can work but what considerations are there in using each? In what situations would it be better to use one approach vs. another? Are there any downsides to any of these approaches?

[#4]

@MichaelJSchulz 

Using a document property means that users can change the value in File > Info.

That can be an advantage or a disadvantage, depending on your preference.

If it is important that users cannot view/edit the values, use a separate sheet and set its Visible property to Very Hidden. That way, the sheet cannot be unhidden from the Excel interface.

[#5]

@Hans Vogelaar

Very interesting! Thank you. That gives me something to ponder.
What about the notion of using VBA to create a Name range and setting the RefersTo to the value of the variable instead of a cell or range of cells? This could apply best to simple values, such as a string value or a numerical value. My thought is that any procedure could obtain the value from the Name range so long as the the procedure was run after the one that creates the Name range and sets the value. Additionally, if there is a procedure after which the Name range is no longer needed, you could even add a bit of code to delete the Name range.

What do you think? Are there potential issues with this approach?

[#6]

@MichaelJSchulz 

By default, a defined name is visible in Formulas > Name Manager, so the end user could edit it or even delete it.

But you can specify that the defined name is hidden when you create it using VBA:

ThisWorkbook.Names.Add Name:="Test", RefersTo:=37, Visible:=False

That way, it won't be listed in Name Manager.