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
This is the definition of JSON from json.org.
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.
|
1 2 3 4 5 6 7 8 |
' In thisworkbook Private Sub Workbook_BeforeClose(Cancel As Boolean) wbCloseShapeVersion End Sub Private Sub Workbook_Open() wbOpenShapeVersion End Sub |
|
1 2 3 4 5 6 7 8 |
Sub wbOpenShapeVersion() 'workbook has opened With Sheets(hiddenShapeSheet).Shapes(hiddenShape) .AlternativeText = _ openingData(CStr(.AlternativeText)).Serialize End With End Sub |
|
1 2 3 4 5 6 7 |
Sub wbCloseShapeVersion() 'workbook is closing With Sheets(hiddenShapeSheet).Shapes(hiddenShape) .AlternativeText = _ closingData(CStr(.AlternativeText)).Serialize End With End Sub |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Private Function openingData(s As String) As cJobject ' create serialization object Dim cj As cJobject Set cj = New cJobject Set cj = cj.deSerialize(s) If cj.key <> cKeyName Or Not cj.Valid Then Set cj = New cJobject cj.init Nothing, cKeyName End If ' setup the data relevant to opening With cj.Add("lastaccess") .Add ("username"), Environ("USERNAME") .Add ("startedat"), Now End With ' will need these later so add them in case they dontr exist already With cj.Add("summary") .Add ("timeopen") .Add ("countopen") End With Set openingData = cj End Function |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Private Function closingData(s As String) As cJobject ' create serialization object Dim cj As cJobject Set cj = New cJobject Set cj = cj.deSerialize(s) If cj.key <> cKeyName Or Not cj.Valid Then Set cj = New cJobject cj.init Nothing, cKeyName End If ' setup the data relevant to closing With cj.Add("lastaccess") .Add ("finishedat"), Now End With If cj.isValid Then With cj.Add("summary") .Add ("timeopen"), .Child("timeopen").asLong _ + DateDiff("s", cj.Child("lastaccess.startedat").value, Now()) .Add ("countopen"), .Child("countopen").asLong + 1 End With End If Set closingData = cj End Function |
Some note on cJobject class
The JSON string that is created above looks like this
|
1 |
{"hiddendata": {"lastaccess": {"username": "fhk647","startedat": "1/31/2011 4:47:29 PM","finishedat": "1/31/2011 4:24:57 PM"},"summary": {"timeopen": "10772","countopen": "3"}}} |
Lets look at how that was created. The methods and properties of interest are below and the code can be examined in the downloadable example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Public Property Get isValid() As Boolean Public Property Get Parent() As cJobject Public Property Get Root() As cJobject Public Property Get Key() As String Public Property Get Value() As Variant Public Property Get Children() As Collection Public Property Get hasChildren() As Boolean Public Property Get asLong() As Long Public Property Get asDate() As Date Public Property Get asString() As String Public Property Get asDouble() As Double Public Property Get asBoolean() As Boolean Public Sub init(p As cJobject, Optional k As String = "_null", Optional v As Variant = Empty) Public Function Child(s As String) As cJobject Public Function Add(k As String, Optional v As Variant = Empty) As cJobject Public Function ChildExists(s As String) As cJobject Public Function fullKey() As String Public Function Serialize() As String Public Property Get formatData() As String Public Function deSerialize(s As String) As cJobject Private Function dsProcess(job As cJobject, Optional whatNext As String = "{") As cJobject |
cJobject for test example
It is possible to refer to the properties of these items in a number of ways, for example
|
1 2 |
cj.child("summary).child("countopen").value cj.child("summary.countopen").value |
Serialization and De-serialization
|
1 |
cj.deserialize(str) |
|
1 |
cj.serialize(str) |
|
1 |
cj.deserialize(str).serialize |
Recursion and cJobject
Heavy use of recursion is made throughout this class – for example here is the serialize method. For a deeper dive into recursion see the section in this site
|
1 2 3 4 |
Public Function Serialize() As String ' make a JSON string of this structure Serialize = "{" & recurseSerialize(Me) & "}" End Function |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
'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 |
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.
Here are some related articles on this site.
For help and more information join our community, follow the blog or follow me on Twitter.