Had an interesting cases lately regarding VSTO 2005 and Excel
So ..one of my customers was attaching a VSTO customization on an excel workbook, it was a pretty large workbook and their standard usage involved adding new sheets in the workbook.
After an certain point somehow VSTO customization just stopped loading in a mysterious manner. Initial troubleshooting reveled that custom properties of the workbook are vanished so the assembly is not getting loaded.
Then I tried adding some custom properties myself, still I could see that they are not being retained. At this point of time we thought that its an issue with excel. But, if it was a issue with excel, I should have been able to reproduce the issue manually (creating a workbook from scratch and adding sheets in a loop to increase the size). I tried it but no luck.
After further investigation, the problem had to do with the size of the DocumentSummaryInformation stream. The update in <http://support.microsoft.com/?id=917151> limits the stream size to 512KB. This is certainly large enough to hold the customization. However, every defined name is also persisted in this stream. The particular sheet being copied had many names defined and each copy of the Sheet added added the same number of names until we ultimately hit the 512KB limit. After which, the names are custom props are no longer persisted.