Storing data between sessions
Sometimes, you might want to store the value of a variable even when you close the workbook, and be able to access it when you next open the workbook. At the same time, you don't want the users to be able to easily see or modify the values - or you might simply want to keep the user interface clean.
There are three convenient ways of storing values and keeping them hidden from users: hidden names, hidden worksheets, and document properties.
- Names take less space and require the least code to create and access
- Worksheets allow you to store more information, in particular if you have large arrays of data.
- Document properties cannot be hidden, but most users are unlikely to ever look for them. Unlike names and worksheets, they can be read from the outside, without opening the workbook.
None of these is a secure way of storing information. Even if the name/sheet is hidden, any worksheet formulas referring to it will still work. So if the user knows the data are there, they can access them. And of course any user who knows VBA can unhide both names and worksheets.
Hidden worksheets
A worksheet can be completely hidden from the user, so that it cannot be unhidden via the user interface (Format... Sheets... Unhide), only via code. This can be achieved by setting the sheet's visibility to "very hidden":
ThisWorkbook.Worksheets("secret").Visible = xlSheetVeryHidden
Referring to a very hidden worksheet is no different from referring to a normal, visible sheet.
Hidden names
Names can be created manually (Insert... Name... Define), or through code:
ThisWorkbook.Names.Add Name:="secret", RefersTo:="42"
To hide the name from users, simply set its Visible property to False:
'either when creating the name ThisWorkbook.Names.Add Name:="secret", RefersTo:="42", Visible:=FALSE 'or later ThisWorkbook.Names("Secret").Visible = FALSE
Next time you open the workbook and want to access the stored values, you simply need
MsgBox Evaluate("secret")
'or even shorter:
MsgBox [secret]
Bear in mind that if the user unwittingly creates a new named range with the same name ("secret"), that will overwrite the name you created.
Document properties
Document properties can be accessed through the user interface (File...Properties) or through code. There are built-in properties like Title, Author, Creation Date etc, but you can also add your own CustomDocumentProperties, and store any kind of data there.
ThisWorkbook.CustomDocumentProperties.Add Name:="Secret", Value:=42, _
LinkToContent:=FALSE, Type:=msoPropertyTypeNumber
Custom document properties can be read without opening the file, with the help of the DSOleFile type library. This MS KnowledgeBase article has more information about that.
