JavaScript for VBA Developers: A Short History


Editor’s note: The following post was written by Office 365 MVP Cindy Meister as part of our Technical Tuesday series.

Those of us who’ve been developing with the Office applications for Windows since they were introduced in the early 1990’s are no strangers to programming language changes. The transition in version 97 from the UI-oriented WordBasic / Excel XLM / AccessBasic languages to the more object-oriented VBA meant not only learning a new language, but making a significant paradigm shift: Instead of thinking about how the user interface behaves the developer needed to learn to think in terms of objects, with their properties and methods, as well as events. On the whole, I believe most of us ended up embracing the new technology (and certainly the IDE!), although not everyone made the transition at the same speed or with the same amount of enthusiasm!

Then VSTO was introduced with Office 2003 and Office developers were confronted with the question whether to familiarize themselves with the .NET Framework and VB.NET and/or C#. (In hindsight, it turns out this wasn’t the real future of Office development. Cause for those who didn’t make the effort to feel smug and those who did to feel somewhat betrayed.)

A few years later, the Office Open XML file format was introduced in version 2007, providing the possibility of creating and editing Office documents directly, independent of the host Office application. This is especially interesting for those who need to process documents server-side, want to increase processing speed or avoid conflicts with the user working in the application interface. In order to take advantage of the new file format, the developer requires knowledge of XML and ZIP packaging in his programming language of choice or, in order to leverage the Open XML SDK, knowledge of VB.NET or C#. (So maybe the effort to learn these was worth it, after all?)

With Office 2013 another major paradigm shift took place with the introduction of “Apps for Office” (recently renamed “Web Add-ins” – see more). At that moment, Office expanded from a desktop suite to the Internet and mobile environments, at the same time adapting JavaScript as the programming language – the language of the Internet. And the programming language is not the only change: a new set of APIs has been (and is continually) being developed.

The big difference compared to the change for Office 97 is that the old language – VBA – is not being replaced. It’s still there, with all the functionality, but as before it runs only in the desktop environment. VBA (as well as COM Add-ins and VSTO) is also still in the Office Development Roadmap, so legacy applications will continue to work – investments won’t be lost.

The biggest drawback of these original “Web Add-in” APIs is that they lack most of the capabilities required to work effectively with Office documents and their applications. They are, however, being expanded for Office 2016 and should offer many more possibilities.
With the advent of JavaScript, developers are no longer locked to Microsoft IDEs and will have their choice of developer tools. Distribution is not as simple as handing over an Office document containing macro code, as can be done with VBA. But on the other hand it is more straightforward than for a VSTO solution. Updating the code across multiple users is certainly simpler.

This change is perhaps more compelling than the introduction of VSTO, which due to deployment complexities never really caught on outside of corporations. While VBA is still not endangered, due to the huge amount of legacy code and investment in legacy applications, it is limited to the desktop environment. But how easy is the transition from VBA to JavaScript?

There are two factors that need to be considered when comparing programming languages: the language syntax and the language “philosophy” – the underlying concepts. Syntactically, JavaScript is closer to C# than the VB languages, which raises the barrier for VBA coders. Conceptually, however, the VBA developer will generally feel more comfortable with how JavaScript works than C# developers.

My series of blog articles will attempt to provide an introduction to JavaScript for the VBA developer, in the context of the Web Add-in APIs. I’m no JavaScript expert and have no intention of trying to explain JavaScript in great depth – there are many books and on-line articles available for those who want to really get into it. My sole purpose is to provide a resource for VBA developers who are interested in “testing the waters” and find available resources hazy, dense, or downright confusing! (Yes, I identify with your pain – BTDT.)

Between syntax and concepts it seems to me easier to begin with syntax. Discussion of code examples won’t make a lot of sense if you can’t understand what you’re looking at! So the first follow-up articles to this post will deal mainly with JavaScript syntax with some conceptual ideas thrown in where appropriate.

About the author


Cindy specializes in automation of Microsoft Office applications and, most recently, in the Office Open XML file formats and Open XML SDK. She has worked with all releases of Microsoft Office applications since the early 1990’s and has been an MVP since 1996. You can find her on the MSDN forums for VSTO, Word, Open XML SDK and General Office Development where she’s a moderator.

Comments (3)

  1. Tomas Vasquez says:

    Awesome interpretation about this "migration times"! Keep it up!

  2. rajendra vyas says:

    I am a vba devloper. This article is ver useful for basic difference in office version and future requirement.

    thanks

    regards

    Rajendra Vyas

    m. 9166929999

    Rajendra.vyas1982@gmail.com

    pls mail new blogs

  3. André Santo says:

    Excelent post !

    VBA has its days numbered.

Skip to main content