For those who have used VBA before, you will know that VBA code was always executed in a linear (synchronous) fashion. This is very natural for an automation task, where you’re essentially manipulating a document through a series of steps (and where, more often than not, the steps are similar to the sequential series of steps that a human would do). For example, if you needed to analyze the current selection in Excel and highlight any values that were greater than 50, you might write something like this:
When run, such macro would execute line by line, reading cell values and manipulating them as it went. The macro have complete access to the in-memory representation of the workbook, and would run almost at the native Excel level, blocking out any other native Excel operations (or, for that matter, any user operations, since the VBA code executes on the UI thread).
With the rise of .NET, VSTO – Visual Studio Tools for Office – was introduced. VSTO still used the same underlying APIs that VBA accessed, and it still ran those APIs in a synchronous line-by-line fashion. However, in order to isolate the VSTO add-in from Excel – so that a faulty add-in would not crash Excel, and so that add-ins could be resilient against each other in a multi-add-in environment – VSTO had each code solution run within its own fully-isolated AppDomain. So while .NET code itself ran very fast – faster than VBA by pure numbers – the Object-Model calls into Excel suddenly incurred a significant cost of cross-domain marshaling (resulting in a ~3x slowdown compared to VBA, in my experience).
Thus, the VBA code above – translated into its VB.NET or C# equivalent – would continue to work, but it would run far less efficiently, since each subsequent read and write call would have to traverse the process boundary. To ameliorate that, the VSTO incarnation of the code could be made significantly faster if all of the read operations were lumped into a single read call at the very beginning. (The write operations, of setting the background of each cell one-by-one, would still have to remain as individually-dispatched calls).
Here is C# Version of the code that addresses the above scenario, but this time reads all of the cell values in bulk (see line #4 below).
Note that the VSTO code, when interacting with the documents, still runs on (a.k.a., “blocks”) the Excel UI thread, since – due to the way that Excel (and Word, and others) are architected – all operations that manipulate the document run on the UI thread. But fortunately for VSTO, the process-boundary cost – while an order of magnitude higher than that of VBA – is still relatively small in the grand scheme of things, and the batch-reading technique described above can alleviate a chunk of the performance issues… and so, VSTO could continue to use the same APIs in the same synchronous fashion as VBA, while letting developers use the new goodness of the .NET Framework.
If synchronous programming was out, the only remaining choice was asynchrony. In the Office 2013 model, this was embodied by a whole bunch of methods that ended with the word “Async”, such as:
In this Office 2013 design, every operation was a standalone call that was dispatched to the Office host application. The browser would then wait to be notified that the operation completed (sometimes merely waiting for notification, other times waiting for data to be returned back), before calling the callback function.
Thus, while the Office 2013 APIs solved the Async problem, the solution was very much a request-based Async solution, akin to server web requests, but not the sort of automation scenarios that VBA users were accustomed to. Moreover, the API design itself was limiting, as there were almost no backing objects to represent the richness of the Office document. The omission was no accident: a rich object model implies objects that have countless properties and methods, but making each of them an async call would have been not only cumbersome to use, but also highly inefficient. The user would still be waiting their 2.5 seconds or 25 seconds, or 4+ minutes for the operation to complete, albeit without having their browser window frozen.
The new Office 2016 API model offers a radical departure from the Office 2013 design. The object model – now under the
Excel namespace for Excel,
Word for Word,
OneNote for OneNote, etc., – is backed by strongly-typed object-oriented classes, with similar methods and properties to what you’d see in VBA. Interaction with the properties or methods is also simple and sequential, similar in spirit to what you’d do in VBA or VSTO code.
Whoa! How is this possible? The catch is that, underneath the covers, setting properties or methods adds them to a queue of pending changes, but doesn’t dispatch them until an explicit
.sync() request. That is, the
.sync() call is the only asynchrony in the whole system. When this
As you can see, the code is pretty straightforward to read. Sure, there are the unfamiliar concepts of
sync, but if you squint over the
sync statements and the
Excel.run wrapper (i.e., only look at lines #2-3, and then #9-18), you still have seemingly-synchronous code with a familiar-looking object model.
In fact, if you ignore the
Excel.run wrapper code (the first and last lines), and if you squint over the
sync statements (lines #5-6 and #18), the code looks reasonably similar to what you’d expect to write in VBA or VSTO!
Still, programming using the new Office.js model – and, for VBA or VSTO users, adapting to some of the differences – has a definite learning curve. This site will guide you through getting started, and understanding the API basics. The book will then pick up from there, diving deeper into Office Add-in concepts and API patterns.