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.
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?
5 Replies
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).
- 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?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.
