Skip to: Site menu | Main content


Remember me?

Storing data between sessions

written by Helen Toomik - Last updated Mar 2006

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.

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.