Store objects in excel worksheet?

Question

Hello does anyone know of a way to store objects within the worksheet object itself ? Or do we have to serialize it and save it as a custom property? The data that i want to store i don't really want to sit in a sheet.

Answer 1

Depending upon what you want to add to the sheet, (data I assumed meant text), if so. Add a reference to Applications Extensibility

and add what you will. whether a sub procedure into a module, or into the sheet. I used something like this to add a sub procedure into a new module but the same idea will work in a sheet.

 Sub AddSomething()
    Dim VBCodeMod As CodeModule
    Dim LineNum As Long
    Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
    With VBCodeMod
    LineNum = .CountOfLines + 1
    .InsertLines LineNum, _
    "Dim thing as String"& Chr(13) & _
    "thing = ""toothpaste"""& Chr(13) & _
    " 'comments too"
    End With

Of course you can adapt this to overwrite your items if you prefer and assign persistent values explicitly if you want. Actually adding a module would make things easier to access, but you can write to the sheet if you want

Answer 2

Because a saved Excel spreadsheet is just a serialization of an object graph, there is no way around the issue of serializing your object - I wouldn't look too hard for ways to offload that - if your objects can round trip to a string, that gives you many options..

I'm still wondering, myself if CustomDocumentProperties is the only place, or if its limits are going to be too cumbersome.

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.customdocumentproperties%28v=vs.80%29.aspx