COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
Should you use JavaScript or VBA to program in Excel?
Is JavaScript for Office (also known as OFFICE-JS) the next big thing? Unless you're a full-time web developer, emphatically no - this blog gives 3 reasons why you should continue to write your Office programs in humble VBA.

Posted by Andy Brown on 28 June 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Should you use JavaScript or VBA to program in Excel?

Microsoft dislike VBA in Microsoft Office, and would like to get rid of it (I'm never quite sure why).  They're now on their second bite of this (indigestible) cherry.  The two bites are:

Bite Notes
Visual Studio Tools for Office VSTO was introduced a fair few years ago, and we proudly announced a course in it, believing that this would eventually allow people to develop applications based on Microsoft Office.  We never got a single enquiry about this course, and it has never run.
JavaScript for Office Office-js has been being gradually improved over the past few years.  The big selling-point is that whereas VBA only works for Microsoft Office on Windows or the Mac, JavaScript will work for versions of Excel running on the web, your phone or your tablet.

So what's not to like about JavaScript for Office?  Plenty, as it turns out.  This blog lists 3 really good reasons not to bother learning JavaScript for Office unless you absolutely have to.

In the interests of full disclosure, I can see one reason why you might want to learn JavaScript for MS Office: it's supported within OneNote, which VBA isn't.

Reason 1 - Set-up and Deployment Overheads

Want to write a program in VBA within Excel?  Here are the instructions:

  1. Open Excel.
  2. Press Alt + F11.
  3. Start coding.
  4. Run your macros to test them by pressing F5.
  5. Save your workbook as a macro-enabled file.
  6. Send it out into the world!

Want to develop in JavaScript for MS Office? Here are the (simplified) instructions:

What you'll need Notes
A development environment If you're a Microsoft developer, it makes sense to use Visual Studio as your IDE (Integrated Development Environment).  The Community edition is available free of charge, although you'll have to get your IT department's blessing to install this.
Node.js You can use either JavaScript or Microsoft's TypeScript language to create your Office add-ins, but for either you'll need to install this framework of files to allow you to create your programs.
Yeoman generator This is a bit like a wizard, allowing you to create Office add-ins more easily.

So I tried doing this.  I ended up having to run a program from my command line, which generated reams of messages before finally hanging. 

Even when you've got the above installed, you still have a long way to go, as this misnamed Microsoft Quick Start guide shows.  And don't get me started on packaging your finished apps and deploying them ...

Reason 2 - The Language is Harder

I've been programming in VBA for nearly 30 years (!), and in C# and JavaScript for more than 5.  But I much prefer the VB-type interface, and have written an article on Simple-Talk to this effect (to date this has had 460,000 views and 2,700 comments, mostly critical!).

There are two reasons why you'll find it harder to program in JavaScript than in VBA:

  1. It's less like English.
  2. You can't record JavaScript macros in Excel or Word, as you can for VBA.

If you're an experienced developer, there's plenty to find fault with about VBA, as summarised by this article, but the vast majority of VBA users will never need to know what classes, delegates and lambda functions are.

Reason 3 - JavaScript is Asynchronous, but VBA is interpreted

To simplify:

  • When you tell VBA to do A and B, it waits until A is complete before beginning to do B.
  • When you tell JavaScript to do A and B, it sends out requests (they're called promises), so that A and B can both run in parallel.

Surely then the JavaScript method is better?

Program tasks

Because the JavaScript method runs tasks in parallel, it will finish more quickly than the VBA equivalent.

 

But ... try making a cake with this method.  The basic instructions are:

  1. Cream together butter/margarine and sugar.
  2. Mix in eggs.
  3. Mix in flour and baking powder.
  4. Bake.
  5. Decorate and serve.

Now imagine the JavaScript version of this, in which all of the tasks begin at the same time, and there's no waiting for one task to finish before the next begins.  If you're lucky, you'll get this:

Bad cake

Apologies if this was your picture that I took from Google Images.  I hope the birthday went well.  Realistically, though, we won't get anything this recognisable as a cake from our process.

 

Almost every macro that you write in Excel requires that steps occur in a certain order.  A typical algorithm might be:

  1. Go to the last non-blank cell in a column.
  2. Copy its value.
  3. Paste it into the cell below it.

To write this sort of thing in JavaScript, you're going to have to keep checking that one promise has been fulfilled before requesting the next one (although for this simple example you could combine the three instructions into a single routine).

I take about 5 times longer coding in JavaScript for websites than C#.  There are a few reasons for this, but the main one is bugs introduced when I'm assuming that process A has finished when in fact it's still running. 

So unless you're already a full-time JavaScript or TypeScript web developer, my advice would be to ignore Office-js until Microsoft shut down VBA, which I don't see happening in the next decade.

This blog has 1 thread Add post
19 Aug 20 at 12:41

Thanks for this Andy! I didn't know this move is underway...