Hiding data in Excel Objects

Why would you want to?

If you want to learn about the cJobject and jSon with VBA you may want to start with How to use cJobject

First off I talk about 'hiding data'. What I really mean is finding somewhere to put data we might need later, in an unobtrusive place. This first came to me as a problem I would need to solve during the roadmap project on this site. If you take a look at that you can see that the objective of that project was to build standardized roadmaps from a set of data. But once built, i wondered if it would be possible to somehow be able to manipulate the shapes created and to be able to reflect those interactive changes back in the source data. To do that I would need to somehow embed, in each shape, something about how it was created. This section looks at how to do that, and in a more generalized way how to embed and encode data in a number of excel objects. The example referred to here is available for download. For a generalized form of this to store default persistent data in any userform, try Persistence of data for userforms

Getting started with encoding data

Shapes have a text property - .AlternativeText, that doesn't seem to be much used. This would be a good place to embed the data required to 'find' the data that would be needed to traceback to the source data that created each shape. The other benefit here is that pasting the shape into another application such as Powerpoint would preserve the shapes' .AlterativeText field, giving me the ability to embed tracking and other information between applications. This was starting to look like an opportunity - but the very usefulness of it meant that the contents of that field would be hard to standardize over time. So that meant using some kind of format that both defined and encapsulated the data that was to be embedded. I thought of XML but dismissed it as too wordy, so I turned to JSON and decided this would do the job rather well, especially since it would potentially give me access to javascript use for the embedded data. This turned my focus onto the 'how to embed', rather than 'what to embed.

Creating JSON from Excel data

JSON(JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate

Despite the last sentence, I decided to implement just enough of a JSON decoder and encoder to meet my needs. The decoder is more complex than the encoder, so I would implement just enough decoding to be able to decode what my encoder could encode - so valid JSON, but not the full specification. The VBA to JSON and JSON to VBA serializer is implemented within the cJobject class discussed below.

Creating an Adhoc hierarchical object framework

One of the other things required would be the ability to create a linked collection of objects 'on the fly', that was based on the deserialization of a JSON string. This ended up being a useful data structure in itself that I have used in a number of other projects. It is implemented in a custom class I have called cJobject. Here For the purposes of this example, I will use a small example to capture some tracking statistics on how many times a worksheet has been opened, some data on who last used it, and how long it's been worked on in total which we will track in a shape (which could be hidden).

Here is what happens when the workbook is opened and closed. You should notice that the .AlternativeText property of the shape is deserialized into a cJobject on opening, various fields are updated, then the cjobject is serialized back into the .alternative text property. The only difference between opening and closing is the fields that are updated.

This is a class that looks like this. (by the way, I'm using a Google Visualization to display an orgchart view of the object below - see section on Google Visualization on this site

cJobject for test example

It is possible to refer to the properties of these items in a number of ways, for example

cj.child("summary).child("countopen").value

cj.child("summary.countopen").value

both refer to the same thing.

Serialization and De-serialization

cj.deserialize(str)

takes the JSON string str, and returns the root of a hierachy of cJobject containing that data. If cj already had children when the deserialize method was called then the new heirachy represented by str will become a child of cj.

cj.serialize(str)

takes the heirachy of cJobects represented by cj, and returned a serialized JSON string. Although this string is standard, valid JSON, arrays are not implented and all values are converted to strings. This is not an issue for the use case in this section, but may be if you wanted to use the class in a different context.

Public Function recurseSerialize(job As cJobject, Optional soFar As String = "") As String

Dim s As String, jo As cJobject

s = soFar & quote(job.Key) & ": "

If Not job.hasChildren Then

s = s & quote(CStr(job.Value))

Else

s = s & "{"

For Each jo In job.Children

s = recurseSerialize(jo, s) & ","

Next jo

s = Left(s, Len(s) - 1) & "}"

End If

recurseSerialize = s

End Function

Embedding in other objects

Although the original purpose of this topic was to find a way to embed data for later re-use in shapes, clearly this same technique can be used to embed in other objects - for example in a hidden cell.

The only difference between a cell and a shape is as below - everything else is the same

'Using a cell

Sub OpenCellVersion(sr As String)

With Range(sr)

.Value = openingData(CStr(.Value)).Serialize

End With

End Sub

'using a shape

Sub wbOpenShapeVersion()

'workbook has opened

With Sheets(hiddenShapeSheet).Shapes(hiddenShape)

.AlternativeText = openingData(CStr(.AlternativeText)).Serialize

End With

End Sub

In the downloadable example I have implemented a workbook tracker in both a cell and shape, as well as form tracker which collects statistics about the usage of a particular form.

Next Steps

With these tools, I can now implement an update to the Roadmapper project to embed genealogy data in each shape, so that interactive changes can be reflected back in the source data. In addition I now also have the capability to track data across workbooks, forms as well as an adhoc object structure.