For years now Excel lovers have been anticipating what Microsoft’s move will be in terms of scripting within Microsoft Office. Many users till date are creating automations and small office use applications on top of Excel and other MS Office solutions for the better or worse, simply due to the ease with which you can extend them. However, the question was – what is Microsoft planning to do. VBA has been around already for more than 20 years and hasn’t been extended for a long time with any new capabilities. The Developer window also hasn’t seen many changes and there have been many rumors that VBA will be planned to be replaced with another programming language or platform (like Python). Let’s see what’s the story behind Office Scripts vs VBA…
The appearance of TypeScript for Excel
In recent years/months we did see see appearance of a new scripting language for Excel – TypeScript and Javascript. Typescript is a language owned by Microsoft and is a superset of Javascript.
The move to Javascript/Typescript probably made sense for Microsoft as Javascript maintains it #1 position as a popular programming language for years now. Typescript is in the top 7 which is an achievement as well especially as both languages have similar syntax. 
Office Scripts only work on the web version of MS Office and Excel
For months I was curious as to whether Microsoft will move and replace VBA with Typescript.
The new strategy for scripting in MS Office
The new updates on Microsoft documentation seem to suggest Office Scripts (Typescript/Javascript) will not move to the desktop version of Excel anytime soon and that this is a strategy they will pursue. As you can read on Microsoft’s website both VBA and Office Scripts have their own role to play.
VBA will be the preferred desktop language for automating your offline workbooks. However, quoting Microsoft VBA has no convenient way to call out to the internet – hence you won’t have an easy way of building connected scripts and API solutions. On the other end Office Scripts are the designated scripting solution for online version of Excel and MS Office applications. Microsoft suggests that Office Scripts will be more web connected and better in terms of portability and accessibility.
Office Scripts use a universal runtime for JavaScript. This gives consistent behavior and accessibility, regardless of the machine being used to run the script. They can also make calls to other web services.
It is clear Microsoft wants to allow you to automate Office Scripts using its other platforms like Power Automate. Allowing you to create E2E automated office task that you can schedule with its cloud platform without an Excel runtime. I think this is a great move and will create an incentive for many users to start learning Office Scripts.
LAMBDA, PowerQuery
It might not seem like directly linked but it is important to notice that Microsoft is investing in extending embedded Excel tools and the Formula language to limit cases where users are currently reaching for scripting. The idea is to limit any coding use cases.
Only recently I saw a news headline saying that the Excel Formula language is now Turing-Complete, having reached the milestone of allowing you to implement any algorithm in it’s language. This has come as of the introduction of the LAMBDA function has been long sought as a missing feature of the Excel Formula language in terms of creating your own complex algorithms. LAMBDA allows you to create custom Excel Functions that can take arguments and are based on other functions – similarly as you would with a VBA User Defined Function. Excel Functions are the way primary way Microsoft wants you to write your calculations and models gnawing away more and more use cases where it sees users writing complex scripts as workaround. It totally love this approach.
Aside from investing in extending the Excel Formula language, Microsoft noticed many users are using scripts to clean their data sets and run queries against their worksheets. With Power Query there is no need to run any more VBA scripts to fix you columns or run queries against multiple worksheets – you can just load them into Power Query, link your tables and refresh the query any time you need. Need to scrape data from a webpage? Power Query has got you!
Additionally – they want to hook users to Power Query and start moving them to the proper tools for their use cases – like Power BI. If you learned Power Query, there isn’t much of a learning curve to try Power BI, as it is has Power Query built in for importing data. Similarly even Azure, Microsoft’s cloud solution, has Power Query as an available data ingestion tool for Data Factory and some other services. They know that it will be easier to attract users familiar with Power Query to their other solutions.
Conclusions
I think Microsoft has made a good decision to keep VBA available for future generations of Excel users. There are many legacy solutions out there that run on VBA, and albeit the language hasn’t progressed it is still fit for purpose for small automation tasks.
Office Scripts are still in their infancy stage still. However, Microsoft have a good vision of how to incentivize users to switch to Office 365 and start learning Office Scripts. Companies who want to make it easier to automate their Excel solutions will want to move a platform that is easier to integrate with Intelligent Automation solutions (like Power Automate). Over time an Office Script will be safer, easier to share and run than VBA Macros. The transition will still take a lot of time given that there isn’t an easy way to start developing Typescript automation tasks on the desktop version of Excel.
Long ago, VBA & VBscript were going to be deprecated. Not all of a sudden, just as one more thing that would flash security warnings at you until you rescripted it (like some C++ did before it was decided C# would coexist alongside). As a replacement, you were offered PowerShell for the desktop (https://devblogs.microsoft.com/scripting/introducing-the-powershell-excel-module-2/) and TypeScript, as mentioned in the article. It’s been my experience that it’s great to learn a new way of doing things; but instead of basing that on any argument regarding depreciation; see if the new method is quicker, more portable, or more powerful. Decisions about what will be deprecated and when often vacilate, disappear completely, or jump up to surprise you.
Thanks for sharing Chris! I agree that it always make sense to learn new things! What is great that with the transition to open XML formats (XLSM, XLSX etc.) Excel has become an open format to be manipulated through many languages and platforms – including PowerShell. I wouldn’t ignore, however, hints of whether a language is depreciated as there are currently many solutions on VBA, especially in financial institutions and operations. What I found interesting to learn is that Microsoft is slowly pushing for TypeScript for integrated scripting inside Excel (Python, PowerShell are all external solutions), while not suggesting any depreciation decisions. I think it shows they don’t want to use a “burning platform” strategy (of depreciating VBA) and instead want to attract users to TypeScript by themselves so kind of more a “wait and see” approach.
Thanks for sharing.
Perhaps you know if Microsoft is going to create the same infrastructure that Google has created? In Google Sheets, it is very easy to link spreadshets to each other using, for example, the importrange() function. It is just as easy to do this with the help of GAS.
Thanks for sharing.
Perhaps you know if Microsoft is going to create the same infrastructure that Google has created? In Google Sheets, it is very easy to link spreadsheets to each other using, for example, the importrange() function. It is just as easy to do this with the help of GAS.
Good question – I believe in Excel Online you simply create Workbook links and then are able to reference other workbooks using the usual notation of Source.xlsx!Range. However, I don’t think this is a case of building infrastructure and more just one of the features. This doesn’t really impact the scripting language itself.
Hi, Office Scripts has been rolled out to Business and education tier Excel for desktop versions. Can you comment about that?