• Excel Vba Examples

Background While developing a roadmap generator for Excel, I realized that being able to manipulate the generated shapes, and as a result have any changes in position be reflected in the source data, would not only be an interesting problem to solve, but would also be very useful. This article discusses how to solve the problem of embedding useful data in an Excel object, and in the process will show how to create an arbitrary linked object structure and how to serialize/deserialize data from this into JSON format. You can find a downloadable example, which tracks workbook and form usage for illustration, and maintains the data in JSON format concealed in both and Excel shape and a hidden cell. Using JSON with VBA Unfortunately, JSON and VBA are not made for each other in the way that say JSON and javascript are.

Excel Vba Examples

I did consider using XML (too heavy) or even some unstructured format (too unpredictable and constrained), but in the end decided on JSON. The first task then is to figure out how derialized JSON data will be accessed in VBA procedures. This is done through a series of linked arbitrary objects which are created during the deserialization process. The cJobject class implements these as as follows.

Hiding the serialized data in Excel objects Now that we have a mechanism for coding and decoding arbritary data, let's turn to the topic of where to store it. In the example provided, I store it in both a shape and in a hidden cell.

As usual there are downloable examples and VBA classes you. Get Excel data in and out of your workbook with. Serialize the original object into Json text.

You will see from the code shortly that there is very little difference in implementation. For my Roadmap Generator I intend to hide this data in the.AlternativeText property of each shape. This give me the added benefit of being able to access the source data when the shape is copied into another application such as Powerpoint and since.AlternativeText is little used, it seems like a good target. If you are tracking form or worksheet usage, it might be better to use a hidden cell. Summary This article brings together the work from two previous articles on this forum - and to provide a some capabilities required to start using JSON effortlessly within VBA and also to be able to pass arbritary data around inside Excel Objects. In a future article I will cover how to create and handle events in shapes generated programatically in VBA, and apply the tools from this article to access source data embedded within shapes.

As always I welcome suggestions, improvements, questions and bug fixes at excel@mcpher.com. All code in this article is free to use as you wish for non commercial use and can be.