The latest news is a little, a very little, and very very late. Microsoft has announced that Excel will soon have JavaScript support but only for functions. The spreadsheet is still without a clear way to create scripts.

In one of the most disconnected decisions in the whole history of Microsoft, back in 1998 the languages team decided to kill Visual Basic 6 to make sure that its new .NET initiative had a better chance of success.

The reason it was, and still is, disconnected, is that Office used VBA as its macro or scripting language and VBA is essentially VB6. Despite several attempts to get .NET into Office most users are stuck with VBA as a scripting language. Now that .NET is also on its way out it seems doubtful that it will ever become the scripting environment for Excel.

Users have asked Microsoft to consider adding Python to Office and there have even been rumors that the idea was under consideration, so it is strange that what we actually have is announcement that Excel now supports JavaScript - but only for in-cell functions. This is not a move to add an alternative scripting language.

The key to any Office scripting language is access to the object model and JavaScript doesn't have access to this. Instead what has been provided is the ability to use JavaScript functions in cell formulas:

function ADD42(a, b) {
 return a + b + 42;
}
Such functions can then be used in the spreadsheet with cell references as arguments:

ADD42(A1,A2)

You can set up parameters to accept a cell range as well as single cells.

You don't even write the JavaScript in Office. A .js file contains the functions and a .json file registers these with Excel. An HTML file tells Excel where the .js file is and an XML manifest file tells it where all the files are - sounds like too much configuration if you ask me.  The JSON file is fairly straightforward and provides a description of the JavaScript function.

Functions can be synchronous or asynchronous. Async functions return a promise which allows Excel to stay responsive while waiting for a result. Typically async functions can fetch data over the web. Asynchronous functions can also be streaming in that they can return multiple data items to cells without waiting for a recalculation.

There is a GitHub repro for the trial but you still need to use a very recent build of Office and join the Office insider program to try it out.

Notice also that this isn't an Excel standalone facility. The HTML, JS and JSON files have to be served via localhost. Think of it as a way of creating JavaScript functions that can deliver their data to Excel rather than something with a deep integration with Excel.

It is difficult to believe that there is a deep technical reason why Office doesn't have multiple scripting languages. For example, LibreOffice has Basic (like VBA), BeanShell, JavaScript and Python - you might even say too many. It has to be office politics (pun intended) or marketing and it is surprising that after so long there is still little clear vision on Office as a component in a larger system. This wasn't always the case.

More Information

Excel Custom Functions on GitHub

Create custom functions in Excel

Related Articles

Office 2016 For Developers

New Model For Office Apps 

Microsoft Office Apps With JavaScript 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info