How do I store program state information in a saved excel file from an application-level add-in?

Question

I'm trying to make an application-level add-in for Excel and I would like to store certain information about the program's state. The information I want to store pertains to the state of each Excel file (rather than across all Excel files).

Answer 1

I use CustomDocumentProperties in the workbook itself (not the add-in)

http://www.dailydoseofexcel.com/archives/2004/07/16/custom-document-properties/

Then when I open a workbook, I check for the existence of the CDP to determine if it belongs to my app, if it's a particular type of file used by my app (for context sensitive menu items), and to read state information for that workbook.

That link doesn't show a good example of writing to CDP, but I think it looks like

ActiveWorkbook.CustomDocumentProperties.Add ...

Answer 2

There's also CustomProperties, which exists at both the workbook and worksheet level. I've been using that to store all sorts of information (including serialized object information) to the workbook.