A Method To Store Workbook Settings...

[Rick Rothstein]

For VBA programmers... how do you store settings specific to your program(s) or other values you wish to preserve between Excel sessions? Probably most of you simply add a new worksheet, place the values in some prescribed order (maybe even adding a description along side of the value) and then hide that worksheet. This is a good option given the settings/values will travel with the workbook should it be circulated to others.

It may surprise some of you that there is another way to do this which does not require you to change the structure of the workbook... custom document properties. I can hear some of you saying "What's that?!?!!". They are the user definable equivalents of the built-in document properties, both are properties of the ThisWorkbook object. An Excel workbook maintains several built-in properties (Author, Creation Date, and so on). Some are given values when the workbook is created, some are given values automatically by Excel when one of its monitored properties changes and others can be filled in manually on-the-fly. Well, custom document properties are the same except that they are created at will and given names and values when created (the values can be changed later as desired).

You should look up CustomDocumentProperties in the VBA help files, but I think this example might help to get you started. The following code creates two custom document properties...


Sub CreateTwoCustomDocumentProperties()
With ThisWorkbook.CustomDocumentProperties
.Add Name:="MyCustomDate", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=DateSerial(1980, 9, 23)
.Add Name:="MyCustomText", _
LinkToContent:=False, _
Type:=msoPropertyTypeString, _
Value:="This is a custom property."
End With
End Sub

This next code shows you one way to retrieve the information that you stored...


Sub SeeMyCustomDocumentProperties()
With ThisWorkbook.CustomDocumentProperties
MsgBox "My Custom Date Property: " & _
Format(.Item("MyCustomDate"), "mmmm dd, yyyy")
MsgBox "My Custom Text Property: " & _
.Item("MyCustomText")
End With
End Sub

Finally, here is how to change these values (run the SeeMyCustomDocumentProperties code again afterwards to see that the changes actually took)...


Sub ChangeCustomDocumentProperties()
With ThisWorkbook.CustomDocumentProperties
.Item("MyCustomDate") = Now
.Item("MyCustomText") = "Here is my new text."
End With
End Sub

Okay, one more "finally"... you can delete any single custom document property like this...


Sub DeleteCustomDocumentProperties()
With ThisWorkbook.CustomDocumentProperties
.Item("MyCustomDate").Delete
.Item("MyCustomText").Delete
End With
End Sub

Note that if you now run the SeeMyCustomDocumentProperties code, VB will raise an error so error checking code is recommended.

And, in closing, don't forget to save the workbook so that your custom document properties are retained for use the next time you open the workbook.