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.
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).
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)
- Be realistic in what it can do, it's not the new VBA