Forum Discussion
Discussion: Persisting variable values—Named ranges vs hidden sheet
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. ...
HansVogelaar
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?
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?
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.