There are many paths into software development, and I suspect a few will be similar to mine. With no formal education or training my first taste of coding was with Excel and VBA. From the first time I hit the record macro button to writing my first sub from scratch was incredibly quick, with simple language syntax and great auto code completion it was a joy to learn (don’t shout at me but I’m sure Python learned its ease of use and learning curve from VBA). Once I understood with the breadth of references and how few walls between other Microsoft programs I used VBA for everything, from workbooks to file management, to html scraping, to processing emails with Outlook. I was able to bypass my IT depts and create my own RPA and even somethings that could almost be classified as a standalone piece of software. But I always hit that one ceiling, I always had to be there (or some other Human in the Loop), I wanted it online and that simply wasn’t possible.
So I moved on, JavaScript for Cordova and web apps, and eventually got into full RPA solutions with Blue Prism and the Power Platform, and as we all do I got a little embarrassed of my VBA background and tried to move away from it. But a passing article caught my attention, with Microsoft looking at using JavaScript to eventually replace VBA. I heard nothing of it again until a spotted a new ‘Automate’ ribbon appear on Excel online. Even the record button was back, and although it was based on Typescript it was close enough, I could finally do VBA online, or could I?
I decided (as I always do) the best way to learn was by doing, so I decided to spin up a simple POC with Office Script rather then my normal VBA. The POC was just to compare some data sets and I wanted to check there was value in the output (I know there are a lot better tools for the job, but for a speedy POC VBA works for me, and I was hoping Office Scripts would too).
After couple of hours I had the POC complete for what I needed and felt like I had learned the basics. I was surprised how easy it was to pick up, the record button and the auto code completion (but without the easy syntax, though my JavaScript background helped massively) it felt like VBA again, and that was also the problem. I thought I was back in VBA with the ‘no walls’ to go beyond Excel, that is simply not the case with Office Scripts. Want to interact with another program, no chance, you can’t even interreact with 2 different Excel workbooks. Create a new workbook, nope, save a copy, no, legacy support for xls, swing and a miss, and the list goes on and on.
You could do formatting, copy between cells, loop over ranges, charts and more, but in the real world it’s not something that you do repetitively enough to put effort into coding it. So what is the point of Office Scripts, I think I found the answer, and it’s Power Automate.
In silo Office Scripts don’t make sense, they are too limited and there will always be VBA that can do it all and more (and easier). But when you look a little further afield you see a little Power Automate connector called ‘Run Script’, this connector means you can call a script without that Human in the Loop. And there’s more:
- You can pass data to and from Power Automate
- The standard Excel Power Automate connectors are very limited, only working with tables and only in a read/update/delete of data way
- The script can be reused on different workbooks
- Scripts can be used in conjunction and chain together to become more powerful
So for Microsoft I think Office Scripts have 2 purposes:
- - Fill in the missing features for Power Automate to make it more of a complete solution
- - Entice people into Power Automate when they hit the walls in Office Scripts
So I return to my original question,
Are Office Scripts the new VBA?
and the answer is No. But can Power Automate, Office Scripts and the rest of the Power Platform be the new VBA, I think yes.
Microsoft realised that VBA was the first tool that enabled a generation of Citizen (or Shadow IT) developers, that wasn’t what VBA was originally designed for, but this time, with Power Platform, they are targeting them, and Office Scripts is part of that strategy.
So would I recommend learning Office Scripts? Yes and no. If you are using Power Automate then yes, it adds so much functionality, makes flows a lot simpler/efficient and is a lot quicker. But if you are not in the Power Platform, then stick with VBA (or even Google App Scripts).
If you do want to learn, there are a few things to bear in mind:
- Although Microsoft’s documentation is a lot better then for VBA, the community and knowledge library simply isn’t there (and probably wont be for a while)
- The script editor is good, but a knowledge of JavaScript/Typescript would be a big advantage
- Be realistic in what it can do, it's not the new VBA
Top comments (0)