Page Content
hide
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.
' In thisworkbook Private Sub Workbook_BeforeClose(Cancel As Boolean) wbCloseShapeVersion End Sub Private Sub Workbook_Open() wbOpenShapeVersion End Sub
Sub wbOpenShapeVersion() 'workbook has opened With Sheets(hiddenShapeSheet).Shapes(hiddenShape) .AlternativeText = _ openingData(CStr(.AlternativeText)).Serialize End With End Sub
Sub wbCloseShapeVersion() 'workbook is closing With Sheets(hiddenShapeSheet).Shapes(hiddenShape) .AlternativeText = _ closingData(CStr(.AlternativeText)).Serialize End With End Sub
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
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
{"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.
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
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 hierarchy 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.
cj.deserialize(str).serialize
then, should simply return a string equivalent to str.
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
Public Function Serialize() As String
' make a JSON string of this structure
Serialize = "{" & recurseSerialize(Me) & "}"
End Function
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.
Here are some related articles on this site.
For help and more information join our community, follow the blog or follow me on Twitter.