VBA->JS: Error handling (Syntax & Concept)

As is often the case with code samples, error-handling is omitted for the sake of clarity and space. But serious code requires, of course, error handling, no matter which programming language is used.

VBA is old, comparatively speaking, and classic VB, upon which it was built, even older. We’re all familiar with On Error GoTo [label], On Error Resume Next and related commands.

More recent programming languages use a different pattern, generally known as “try…catch”. The .NET Framework languages use it (although VB.NET can still work with On Error) and it’s become a widely accepted standard. This is the basic pattern in synchronous JavaScript, as well.

In addition, error-handling is needed for asynchronous calls. Up until relatively recently, this has been done using function callbacks, such as appear in the code samples for the Office 2013 APIs. More on this, later.

On Error is usually declared at the beginning of a procedure and (with limited exceptions) is in-force until the end of the procedure. The last line of the procedure needs to force an exit so that execution does not continue through the error handling. Then follows the [label]: to which execution jumps when an error is encountered. Here, the error information is evaluated and the desired action defined, which could include displaying a message, exiting the procedure or jumping back up into the code to continue execution at a specified point.

try...catch (also referred to as “structured error handling”) enables the developer to handle errors in the written code where they occur, rather than at the end of a procedure.

Code can contain multiple try...catch blocks and blocks can be nested.

The code to be executed is within the try section, error-handling is located in the catch section, immediately following. In addition you can have a third section, finally where you put code that should be executed irregardless whether the procedure ran with or without an error. For example, if resources should be released this would come in the finally block.

As an example, let’s go back to the discussion on Coercion types and the function that writes formatted text to the Word document. The first part of the code, where the selected format type is determined, is syncronous, so comes in the try block; catch and finally blocks follow in case execution fails on anything in the try block. (Notice that the blocks are denoted by a pair of curly braces.)

function writeFormattedText() {
        var formatType = $('input:radio[name=formats]:checked
        var coercType;
        var textValue = $('textArea[id=formatInput]').val();
        switch (formatType) {
            case "HTML":
                coercType = Office.CoercionType.Html;
            case "OOXML":
                coercType = Office.CoercionType.Ooxml;
                textValue = sOOXML;
            default: //"Plain"
                coercType = Office.CoercionType.Text;
    catch (error) {
         app.showNotification('Syncronous finally');
    finally {
        console.log('Syncronous finally');

   //For debugging purposes, in case something's weird:
//console.log(formatType + " " + coercType.toString() + " " 
              + textValue);
       { coercionType: coercType }, function (result) {
        if (result.status === Office.AsyncResultStatus.Succeeded) {
            app.showNotification('Following was written to the document', 
                                  '"' + textValue + '"');
        } else {
            app.showNotification('Error:', result.error.message);

catch returns an error object containing usual information such as error number and a description. This can be used in the error-handling code.

If you set a break point on the first line of code then step through (F11 in Visual Studio) you’ll notice that things proceed through the synchronous section the way you’d expect and are accustomed to from VBA. When you hit the asynchronous calls, things tend to jump around a bit and it’s difficult to track what’s happening, when. This is the nature of asynchronous and, quite honestly, it takes some getting use to!

The asynchronous error-handling occurs in the optional function callback (function (result) {. This is not triggered until the asynchronous call returns – code execution waits and the host (Office) application is not blocked. When the function is triggered the if...else tests the result.status returned by the asynchronous call (setSelectedDataAsync). If it succeeded, something happens; if not, something else happens.

If more than a “it worked” message should occur when the call succeeds, this is then nested within the if block. And should this also require an asynchronous call the pattern repeats itself. You very quickly have quite a few levels of nesting and it becomes extremely difficult to keep things straight. This is sometimes referred to as “callback hell” and if you look at a number of complex code samples you’ll soon see why.

For this reason, JavaScript introduced the concept of “promises”, which helps flatten out the asynchronous error-handling. Unfortunately, the Office 2013 APIs don’t support promises, so a discussion must wait until the 2016 APIs are publicly available.

Leave a Reply