JavaScript and Excel

There is no getting away from the fact that Excel is ubiquitous in finance. As much as some people might think of models and applications built with Excel as risky, the fact is that without it the finance world wouldn’t operate as it does. According to Forbes, “without Excel we’d not have had the incredible financialisation of the economy over the past 30 odd years”.

As well as being able to model complex financial instruments, what really sets Excel apart is the automation ability of VBA. From basic macro recording functionality to dedicated development teams building complex applications using windows forms, ActiveX objects and API calls, you really can build almost anything (whether you should is another discussion). However, VBA has been slightly neglected by Microsoft over the years. There have been no new language features for many years, although the object model has been updated to automate new Excel functionality. While other languages have implemented new features such as generics, asynchronous methods and lambda expressions, VBA is pretty much the same beast it was ten years ago.

This has started to create a closed environment as VBA developers don’t pick up techniques that are used in more modern languages. Any move to new technology now comes with a much steeper learning curve than say a Java developer starting to use C#. It also means developers brought up on more modern languages are not extremely enthusiastic to develop Excel applications because of the lack of language features in VBA. New methods around software engineering are not flowing through to VBA and Excel, meaning the full power of both is probably not being fully utilized!

With the release of Office 2013, Microsoft introduced JavaScript support in a very limited capacity. Through their own Office.js API you could perform some very basic functions such as reading and writing data to the Active Cell, and subscribing to the change event of a cell. On the outset, it seems like pretty basic stuff, but as a concept it opened the door to a whole new world. Now with the release of Office 2016, much more of the Excel object model has been exposed through the Office.js API making applications built in JavaScript a real possibility. Hopefully this article will peak the interest of some Java / JavaScript developers enough to explore the possibilities this new technology offers.

JavaScript in Office
So how does it all work? The feature of Excel that enables JavaScript integration is called Office Add-Ins. An Add-In consists of a manifest file, which is just an XML file that tells Excel where to load resources from, html pages and JavaScript libraries. On launching the manifest file, it specifies the home html page, which loads to a task pane inside Excel. To load an Add-In, a user selects the manifest file from inside Excel and the html page loads in an Internet Explorer pane inside Excel. User interaction is provided using JavaScript just as in a normal web page. The real magic appears when a reference to the Office.js API is made and you can manipulate the Excel model from JavaScript.

The examples below show snippets of some basic features that will be second nature to any VBA developer but are fundamental to manipulating the Excel model.

Writing to a range
BlackRock Writing to Range

Clearing the contents of a range
BlackRock Clearing Content

Selecting a range by defined name
BlackRock Selecting a range by defined name

Adding a workbook
BlackRock adding a workbook

Changing the font
BlackRock changing the font

The Context Object
One thing that is common throughout most of the code snippets above is the presence of a Context object (the ctx parameter) and the execution of the sync method. This gives an indication of how the technology works and while a full description is beyond the scope of this article, it is worth going over the main points.

When you execute a native VBA command such as Worksheets.Add, the command is interpreted, compiled and executed. A new worksheet is created as soon as the line is executed. The JavaScript API doesn’t work in the same way, instead it uses proxy objects to model actions. All code statements executed in JavaScript are against proxy objects and are not reflected in the Excel model until the document is synchronized. This is done using the sync command of the Context object. The context object is defined in the Office.js API and provides the connectivity to the Excel model. This model has an impact on the way that the flow of the JavaScript code must be engineered.

For example consider the two following statements, one in native VBA one in JavaScript;

VBA
startRange = Worksheets(“Sheet1”).Range(“C5”)
Debug.Print startRange.Column

JavaScript
var datasheet = ctx.workbook.worksheets.getItem(‘Sheet1’);
var startRange = datasheet.getRange(“Sheet1”).Range(“C5”);
console.log startRange.columnIndex;

In the example above, the JavaScript will cause an error. This is because the startRange object is a proxy object and not an actual reference to the range. Proxy objects only contain the values for properties that they have explicitly been instructed to load. To return the columnIndex value it must first be loaded and then the context must be synched to populate the property with the value.

Valid JavaScript
var datasheet = ctx.workbook.worksheets.getItem(“Sheet1”);
var startRange = datasheet.getRange(“Sheet1”).Range(“C5”);
startRange.load(“columnIndex”);

ctx.sync().then(function () {
console.log startRange.columnIndex

A More Detailed Example
Let’s look at a more complete example covering a scenario that native VBA cannot easily handle. JSON was created many years after VBA and has been gaining popularity over the last few years. One of the main reasons is that it is much more lightweight than XML. JSON can dramatically increase speeds when transferring large datasets over a network. VBA has access to a DOM parser for XML through the COM component MSXML2.DomDocument but there is no COM equivalent for JSON. JSON is of course native to JavaScript and so is easily parsed.

In the sample app you can see a very basic web page loaded in the IE frame. There is an input box to specify the target range and a ‘Load’ button to run the process. The code snippet here shows the JavaScript function that is called when the Load button is clicked.

BlackRock Example

BlackRock Example2

The code starts with a basic JSON string containing employee details which is parsed to an employee variable. Pretty simple stuff so far, but in VBA this represents a difficult task, even more so if you are unsure of the structure of the JSON. Add another field and you’re probably going to have to reengineer your code. The next step is Excel specific, I want to extract the JSON to a worksheet, using the cell range specified in the web page as the top left starting point. In native VBA, this is quite simple; I can resize the single cell range using the upper bounds of the array and update the values by passing it an array variable. Unfortunately the resize method isn’t yet mapped to JavaScript so it’s a bit more manual, but still fairly easy to achieve.

To write the data I need to know the row and column index values and increment these as we iterate through the properties. First I get a handle on the range the user has specified, but as discussed earlier this is only a proxy object and doesn’t actually contain those values. I tell the proxy object to load the properties and then I issue a sync command on the context. Using a Promise, I then process the employee object after a successful sync. The row and col variables can now be properly populated and using the JavaScript flavour of reflection I can loop through the properties of the object, writing the name to a header row and the values to the row below. Finally I need to sync the context object again to realise the values that I have just written in the actual worksheet. The final output is shown below.

BlackRock Excel Example

The Office.js API covers roughly 20% of the model compared to native VBA but the word from Microsoft is that this technology is one of their main focuses so this will surely increase in later releases of Office.

Blurring OS Boundaries
Microsoft Office has been around on Macs longer than it has on Windows. In its lifetime, Office for Mac has had an on-off relationship with VBA and macros. They are now back in, and relatively basic macros are compatible between OSX and Windows. Macros developed on Windows that implement features such as ActiveX, COM, Win32 API calls, are not compatible as the technology doesn’t exist on OSX.

Office Add-Ins and JavaScript are much more compatible, in the same way that a web page usually runs as well on a Mac as in Windows. Where Office Add-Ins really start to break new ground though is with the support for them in Office for iPad. The mobile version of Office currently has no support for VBA but it does allow you to load Office Add-Ins. The Office.js API is compatible across Windows and OSX for Mac and iPad (iPhone doesn’t currently support add-ins). This means that an Add-In can be deployed to all three without the need for OS specific code. There are plans to extend Add-In support to Android devices at a later date (presumably once it is stable on iPad), but it doesn’t currently exist. This compatibility with mobile devices is where the technology gets really exciting and opens up so many new opportunities.

Conclusion
What I have tried to show in this article is not a how-to guide but to hopefully bring to a wider audience how the two previously separate worlds of open-source and Microsoft are now moving in to a model where they can communicate and co-exist. It opens up new possibilities for web developers to collaborate with Excel developers, and for VBA developers to move out of their world but bring their expertise of the Excel model. Hopefully both sides can start to embrace and explore new technologies leading to better end user experiences and more efficient, stable and modern applications.

TECH-0056