Office Scripts vs VBA: What’s the difference?

Office Scripts are a new scripting language for automating Excel on PC, Mac and Online. Initially only available in Excel online, they are now available in the desktop application (subject to a compatible enterprise, educational or business licence).

Microsoft had already announced that VBA was no longer under active development. This lead many to tout Office Scripts as the successor to VBA.
Microsoft themselves state the following:

“Office Scripts and VBA macros have a lot in common. They both allow users to automate solutions through an easy-to-use action recorder and allow edits of those recordings. Both frameworks are designed to empower people who may not consider themselves programmers to create small programs in Excel.

The fundamental difference is that VBA macros are developed for desktop solutions and Office Scripts are designed for secure, cross-platform, cloud-based solutions.”

You will notice that there is no mention of the word “replacement”, yet much has been assumed and written about in relation to this.

There are significant differences in the technologies. So, in this post, we go deeper to really understand the differences and how that affects what we can or cannot do with each.

If you have never heard of Office Scripts and want to get started, you can find an introduction to the basics in Tip #470.

By far the most significant difference between VBA and Office Scripts is the environment in which they operate.

VBA has been the backbone of Excel automation since the mid 1990’s. This was an era before cloud applications, when collaboration was all about being in the same room as another person.

At the time, individual desktop installations of Excel were the only option. VBA was designed to work in this world. It combined Visual Basic, Microsoft’s programming language, with application specific features. Hence the name Visual Basic for Applications (VBA as we know it).

Given that each PC was mostly a separate silo, the risk of Excel bringing down an entire company IT system was relatively low. Therefore, VBA could be fully powered with scope to read/write files and work with other applications.

Office Scripts on the other hand, have been developed for the cloud era, where Microsoft has an entire cloud environment with applications that integrate and work together.

In this world, the concept of a stand-alone PC is outdated. Applications are always on and can be run from any connected device on any hardware, at any time.

While we tend to work with individual Excel files using a desktop application. That is no longer the only option.

Therefore, Office Scripts do not exist on your PC, or in one Excel file. They are saved on OneDrive or SharePoint so any Excel workbook can use them at any time.

So, we can see that VBA and Office Scripts exist in and have been designed for different environments. This is critical to understand, as the rules that govern those environments underpin every other consideration of how those tools operate.

Technology has come a long way since the mid 1990’s, especially in terms of interconnectivity.

Excel, which was previously in a PC level silo, is now part of an entire IT infrastructure. Therefore, VBA being a fully powered automation tool that can call out to the internet and read/write files poses significant risks.

Within an organization, internally generated VBA code is unlikely to pose a significant threat. However, given the ease of downloading and opening externally created Excel files, one piece of malicious VBA code in one Excel file can bring down an entire company network.

This is why, in 2023, Microsoft took steps to force users to “unblock” VBA files downloaded from the internet.

Office Scripts, on the other hand, exist in the Microsoft 365 environment, which is hosted in data centres across the world. Therefore, security is not just important; it’s critical to Microsoft’s entire product base.

As a result, Office Scripts have been designed to fit with the security of that environment. By itself, the worst havoc an Office Script can inflict is to change a single Excel workbook.

VBA has a vast scope. It can do almost anything a person can do with a PC: move data between workbooks, read/write files, download files from the internet and control other applications.

It is this, which makes VBA so powerful… and so dangerous in the wrong hands.

Office Scripts have a limited scope. The highest-level object in Office Scripts is an Excel workbook. They can’t even interact with another workbook.

This brings challenges for how we work. Simple actions, such as copying and pasting data between workbooks can’t be achieved with Office Scripts.

However, Office Scripts are part of a richer environment of tools. Therefore, we have access to applications such as Power Automate to provide cross-application functionality.

The difference in scope and environment means that VBA and Office Scripts have different use cases.

Excel developers undertaking work for external clients have found Office Scripts difficult to embrace.

With VBA, the code travels with the workbook. It doesn’t matter who opens the workbook; the code is available. Therefore, it is simple to develop solutions and distribute them to clients.

Office Script codes are saved separately to the Excel workbook and have their own file type.

This is great for re-usability as one Office Script file can be used on any workbook. There is no need to duplicate any code.

However, the Office Script file is saved on OneDrive or SharePoint, and is only available to those with direct access to an organisations Microsoft 365 environment. External parties cannot access the file to use it.

As a result, Office Scripts are excellent for internal sharing, but a poor choice for external sharing.

VBA has a long history with Excel on PC and can automate almost every feature of Excel.

Excel on the Mac has some significant differences to the PC. As a result, there are compatibility issues, resulting in a lower VBA feature set for Mac users.

Office Scripts, being a cross-platform solution, can automate features that are available on all versions of Excel. At present, Excel online is behind both PC and Mac; therefore Office Scripts do not have the same capabilities as VBA.

Over time, as Excel Online develops, we can expect to see Office Scripts obtaining the ability to automate more and more features.

If you look on your computer today, you may find no trace of Office Scripts or the Automate tab.

Office Scripts are intended to work on all versions of Excel. However, integration with the Microsoft 365 platform is required. So, at present, it is restricted to compatible enterprise, educational and business licences. Meaning many users will not have access to Office Scripts.

In contrast, VBA is not restricted by licences. It is available in every desktop version of Excel, and easily accessible to all users.

Millions of organizations are using VBA every day. So, while it is no longer being developed, Microsoft will not remove VBA from Excel.

The predecessor to VBA, known as Excel 4 Macros, is still available in the desktop application. If that is available after 30 years, I cannot see VBA being removed any time soon.

Office Scripts are being actively developed, with new features released every month.

So, the future of Excel will likely see VBA and Office Scripts coexisting for a very long time ahead. Each serving different needs and use cases.

So, where should you spend your time? Which should you learn? Like so many things… “it depends”. What do you want to achieve? In which environment do you want the solution to operate? Who will you be sharing the file with?

It is easy to discuss learning curves and compare programming languages. But these discussions are irrelevant if the tool does not do what you want. I trust this post has given you the context for VBA and Office Script and therefore, you have a better chance of selecting the right tool for your scenario.