Services‎ > ‎Desktop Liberation‎ > ‎Recursion‎ > ‎

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

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 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.

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.