D
Dag Johansen
Hi everyone!
I'm making a plug-in that allows getting data from a web
app into a spreadsheet. Users make their settings in
userforms and these settings must be persisted - to allow
modifying settings as well as refreshing data based on the
same criteria at a later time.
Persisting settings in a cell formula is not very good for
several reasons. First of all what I'm making is not a
function; it's result depends not just on the arguments
but on external data. Secondly, settings does not apply to
a single cell, but rather to a collection of cells (a
range). Third, I don't want to show a string
representation of the settings in either the cell or the
formula bar.
Having thought about this for a while I figured I could
serialize the custom types that hold the settings in
memory into an xml string. The range to which they apply
can be named, and an association made by including the
range name in the serialized settings.
I set out to make a small class module that would
represent a "persistor" object. The idea is to make an xml
document a la
<data>
<item key="key1">(Item1)</item>
...
</data>
and a simple interface (GetItem(key), RemoveItem(key),
SetItem(key)) to work with it. Kinda like a cheapish
implementation of a hashtable (the number of items I need
to serialize is quite small; way under 100 at any rate).
For persistance, it would also have methods like Load
(workbook) and Save(workbook). Save() could add a sheet
with visibility xlVeryHidden and some predefined name, and
put the xml string in, say, A1 of this sheet. Load would
try to find this sheet and load the xml string
(loading "<data/>" if the sheet is not found).
So far so good. Trouble is, since my vbproject is running
as a plug-in, one instance of this persistor is needed for
each open workbook, and I need to get the right instance
when adding/modifying entries. For this purpose I created
the shortest class module, ObjectPair, ever written:
Public First as Object
Public Second As Object
Then I made a small class module called
PersistanceManager, responsible for managing the
association between a workbook and it's persistor object.
It is coded as follows:
Dim list As New Collection
Dim WithEvents app As Excel.Application
' ---- Gets the PersistorObject instance associated with a
given workbook.
' - If the book is omitted, gets the instance associated
with the active
' workbook.
' - Returns Nothing if no persistor is associated with
the book.
Public Function GetPersistor(Optional book As Workbook =
Nothing) As PersistorObject
Dim item As ObjectPair
If book Is Nothing Then book = app.ActiveWorkbook
For Each item In list
If item.First Is Workbook Then
Set GetPersistor = item.Second
Exit Function
End If
Next item
Set GetPersistor = Nothing
End Function
' ---- PRIVATE implementation.
Private Sub attachPersistor(book As Workbook)
Dim p As New ObjectPair
Set p.First = book
Set p.Second = New PersistorObject
Call list.Add(p)
End Sub
' ---- Application Event Handlers.
Private Sub Class_Initialize()
Set app = Application
End Sub
Private Sub app_NewWorkbook(ByVal Wb As Workbook)
Call attachPersistor(Wb)
End Sub
Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
Call attachPersistor(Wb)
End Sub
Private Sub app_WorkbookBeforeSave(ByVal Wb As Workbook,
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call GetPersistor(Wb).Flush
End Sub
This is a good time to say "thank you" to anyone who is
still reading! But I can't discuss this properly in a few
words.
So now what? This is where you experts come in. Should I
abandon this entire approach to persisting my metadata? If
so, how could I do it instead?
What I would love was something like the Tag property, but
on the range. That way, if a user deletes the range
without my knowledge the persisted settings disappear as
well. And I wouldn't need to hide any sheets. And it's
only for programmatic use, the user won't see it anywhere,
leaving it up to me to create some UI for the user to
interact with.
I've tried all sorts of silly things that I don't know
what is (the try-and-see, hope-and-pray approach -
probably the dominating methodology in our industry). I
assigned Range.Phonetic.Text, hoping nobody would ever use
whatever that is, but even though I get no error making
the assignment, reading it reliably returns the empty
string! I can't help but long back to a real programming
framework rather than the Excel Object Model when those
kinds of things happen.
Comments, tips, any discussion in general
concerning "metadata persistance" in Excel is highly
welcomed!
My e-mail: (e-mail address removed)
I'm making a plug-in that allows getting data from a web
app into a spreadsheet. Users make their settings in
userforms and these settings must be persisted - to allow
modifying settings as well as refreshing data based on the
same criteria at a later time.
Persisting settings in a cell formula is not very good for
several reasons. First of all what I'm making is not a
function; it's result depends not just on the arguments
but on external data. Secondly, settings does not apply to
a single cell, but rather to a collection of cells (a
range). Third, I don't want to show a string
representation of the settings in either the cell or the
formula bar.
Having thought about this for a while I figured I could
serialize the custom types that hold the settings in
memory into an xml string. The range to which they apply
can be named, and an association made by including the
range name in the serialized settings.
I set out to make a small class module that would
represent a "persistor" object. The idea is to make an xml
document a la
<data>
<item key="key1">(Item1)</item>
...
</data>
and a simple interface (GetItem(key), RemoveItem(key),
SetItem(key)) to work with it. Kinda like a cheapish
implementation of a hashtable (the number of items I need
to serialize is quite small; way under 100 at any rate).
For persistance, it would also have methods like Load
(workbook) and Save(workbook). Save() could add a sheet
with visibility xlVeryHidden and some predefined name, and
put the xml string in, say, A1 of this sheet. Load would
try to find this sheet and load the xml string
(loading "<data/>" if the sheet is not found).
So far so good. Trouble is, since my vbproject is running
as a plug-in, one instance of this persistor is needed for
each open workbook, and I need to get the right instance
when adding/modifying entries. For this purpose I created
the shortest class module, ObjectPair, ever written:
Public First as Object
Public Second As Object
Then I made a small class module called
PersistanceManager, responsible for managing the
association between a workbook and it's persistor object.
It is coded as follows:
Dim list As New Collection
Dim WithEvents app As Excel.Application
' ---- Gets the PersistorObject instance associated with a
given workbook.
' - If the book is omitted, gets the instance associated
with the active
' workbook.
' - Returns Nothing if no persistor is associated with
the book.
Public Function GetPersistor(Optional book As Workbook =
Nothing) As PersistorObject
Dim item As ObjectPair
If book Is Nothing Then book = app.ActiveWorkbook
For Each item In list
If item.First Is Workbook Then
Set GetPersistor = item.Second
Exit Function
End If
Next item
Set GetPersistor = Nothing
End Function
' ---- PRIVATE implementation.
Private Sub attachPersistor(book As Workbook)
Dim p As New ObjectPair
Set p.First = book
Set p.Second = New PersistorObject
Call list.Add(p)
End Sub
' ---- Application Event Handlers.
Private Sub Class_Initialize()
Set app = Application
End Sub
Private Sub app_NewWorkbook(ByVal Wb As Workbook)
Call attachPersistor(Wb)
End Sub
Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
Call attachPersistor(Wb)
End Sub
Private Sub app_WorkbookBeforeSave(ByVal Wb As Workbook,
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call GetPersistor(Wb).Flush
End Sub
This is a good time to say "thank you" to anyone who is
still reading! But I can't discuss this properly in a few
words.
So now what? This is where you experts come in. Should I
abandon this entire approach to persisting my metadata? If
so, how could I do it instead?
What I would love was something like the Tag property, but
on the range. That way, if a user deletes the range
without my knowledge the persisted settings disappear as
well. And I wouldn't need to hide any sheets. And it's
only for programmatic use, the user won't see it anywhere,
leaving it up to me to create some UI for the user to
interact with.
I've tried all sorts of silly things that I don't know
what is (the try-and-see, hope-and-pray approach -
probably the dominating methodology in our industry). I
assigned Range.Phonetic.Text, hoping nobody would ever use
whatever that is, but even though I get no error making
the assignment, reading it reliably returns the empty
string! I can't help but long back to a real programming
framework rather than the Excel Object Model when those
kinds of things happen.
Comments, tips, any discussion in general
concerning "metadata persistance" in Excel is highly
welcomed!
My e-mail: (e-mail address removed)