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
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?
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?
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.
- 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?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.