May 24 2022 07:25 PM
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?
May 25 2022 12:53 AM
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).
May 25 2022 07:01 AM
May 25 2022 08:19 AM
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.
May 25 2022 01:07 PM
May 25 2022 01:39 PM
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.