Office Scripts vs. VBA: Programming Excel for the Web and Desktop

Microsoft Excel has long provided the ability for users to write “macros”—pieces of programming code that let people automate both simple and advanced tasks. The official name of these macros is Visual Basic for Application (VBA) because they use Microsoft’s Visual Basic programming language.

VBA macros are extremely powerful, and we’ve used Excel macros and VBA for decades to create enterprise-grade software for accounting, retail, logistics, manufacturing, and dozens of other industries.

One of the drawbacks of Excel macros has been that it couldn’t automate Excel for the Web. Microsoft announced a step forward in this direction by launching a new web-friendly version of Excel VBA called “Office Scripts”.

Office Scripts vs. VBA: Major differences and similarities

The way Office Scripts work is fundamentally different to Excel VBA. Although Office Scripts is unlikely to ever replace VBA, it provides certain features that VBA can’t. Together, VBA and Office Scripts form a powerful combination.

Here are the major differences and similarities between these two powerful technologies:

Programming language

Desktop Excel Macros use the Visual Basic programming language, which tends to be easier for beginner programmers to learn. Office Scripts is based on JavaScript, the most popular programming language in the world, and used on virtually every modern website today.

JavaScript is more complicated to pick up for people without programming experience and is more challenging to debug.

Macro recording

Most casual users will likely use the “Record Macro” option for both desktop and web versions of Excel to create their macros. This Record Macro tool is designed for people who have little to no programming knowledge.

Recording macros is useful for simple office automation but is hopelessly inadequate when it comes to doing anything more advanced. To leverage advanced features of Excel VBA and Office Scripts, a knowledge of programming is essential.

Availability

Office Scripts is available exclusively to users with an Office 365 subscription. Excel VBA can be used by anyone with a valid Excel licence, whether Office 365 or standalone.

Integration

Both Excel VBA and Office Scripts allow for extensive integration between services, but they do it in different ways. Usually, Excel VBA is the best choice for desktop integrations, and Office Scripts is typically better for web integrations, but it all depends on the use case.

Office Scripts automation is achieved through Microsoft’s purpose-built Power Automate tool.

Power Automate lets users create workflows between web-based apps. Microsoft provides several pre-built automations, although these will likely need tweaking based on your use cases.

Integrating Excel VBA code with many other solutions is also possible, even web solutions. In just a few lines of code, Excel on the desktop can integrate with many other tools, both on the desktop and on the web, but this typically requires advanced programming knowledge.

Code that runs on Excel events

Using VBA, it’s possible to create full-scale applications with code that triggers based on Excel events, such as editing a cell or opening a workbook. This means that VBA-powered workbooks can function like first-class desktop applications. The experience is smooth and fast.

Office Scripts offers none of this functionality. To run Office Scripts, users must either click the relevant script button in their spreadsheet, or you have to connect the script to triggers defined in Power Automate.

Power Automate Triggers open up an immense amount of use cases that aren’t possible with Excel VBA, but Office Scripts’s lack of support for events limits that usefulness when using them directly inside a workbook.

Speed

Office Scripts are painfully slow when users run them directly. We created a simple script to update the value of a single cell through a button click and it took several seconds to perform this trivial task.

A similar script in Excel VBA would run in microseconds.

For large-scale processing tasks that need to be run on more than a few rows, Excel VBA is the obvious choice. If the task can be run in the background as part of a web automation, then Office Scripts is also an option.

Referring to other workbooks

Office Scripts can’t refer to other workbooks like VBA can.

In VBA, it’s possible to refer to any workbooks in the code and manipulate them in any way you wish. Office Scripts only let you run the script in the current workbook.

It’s possible to run an Office Script in a different workbook if it’s defined as part of a Power Automate flow, but the structure is completely different and far more complex than in Excel VBA.

If you want to refer to multiple workbooks using Office Scripts, you might want to hire an Excel specialist to help you.

Debugging code, and code readability

VBA provides a fully-fledged development environment that lets developers easily create reusable code for large applications. Office Scripts are limited to single code snippets that can quickly become messy for large projects.

The cumbersome structure of Office Scripts is probably the best clue that Microsoft never intended it to replace VBA, but rather to use it primarily with Power Automate for sophisticated background processing.

Debugging is also far more challenging in Office Scripts because of the tool’s inability to set “breakpoints”—places where programmers can pause the currently executing code to inspect what’s happening before it continues. This feature is what makes VBA one of the easiest languages in the world to program in. Office Scripts doesn’t support this, which is surprising seeing as similar functionality has been created in other online programming tools.

Possibly Microsoft will add this feature in the future. For now, debugging is a task better suited to experienced web developers.

Where Office Scripts shine: Web-based automation with Power Automate

Office Scripts shines as a web automation tool that runs in the background, connecting web-based tools through triggers, to process data. Using Power Automate, businesses can create scripts that do an extensive variety of things, such as:

• Updating an Excel workbook when a user submits an online form.
• Updating an Excel workbook when a user receives an email.
• Automatically running Excel scripts when an Excel file is added to a SharePoint library.
• Tracking file additions in SharePoint.
• Connecting Excel with ChatGPT for advanced processing.
• Scanning receipts added to OneDrive and putting the details in an Excel file.
• Generating PDFs and sending them for signature.
• Connecting Gmail with Excel.
• Connecting Social Media channels to Excel.
• And many more options.

Microsoft already offers numerous ready-made templates that let you connect well-known web apps. An experienced programmer can then take these templates and turn them into powerful business tools with advanced Office Scripts code, or create new combinations using Office Scripts.

A desktop version of Power Automate also exists, which lets users automate actions on their desktop.

Can you convert existing Excel VBA macro files to web-friendly office script files?

The short is not really, and certainly not at the click of a button.

Converting any existing Excel VBA application into an Office Scripts version would require extensive work from a VBA and Office Scripts programmer.

Many Excel VBA applications depend heavily on events, and Office Scripts’s lack of event functionality means this code will need to be replaced by buttons that users click, or Power Automate automations.

Probably, the most realistic solution to convert an Excel VBA project to Office Scripts would be to create a web version with fewer features that works in tandem with the desktop version, but it depends on your specific use case.

Will Office Scripts replace VBA?

Office Scripts won’t replace VBA, just as VBA won’t do everything that Office Scripts does. The two tools serve different purposes in the current ecosystem and will often work best when used together.

Excel VBA is best for fast processing and smooth apps that run on desktops. Office Scripts is best for web-based automation.

Also, if users absolutely must work on a web version of Excel, Office Scripts fills a gap that now makes that possible to some degree. Office Scripts is also useful for companies that use non-Windows operating systems, such as Linux or MacOS, and it will allow users of these operating systems to automate parts of Excel.

VBA has been used extensively in business applications because it lets businesses create professional desktop applications in a fraction of the time compared to other programming languages. When you know the ins and outs of VBA programming, it’s possible to create applications that look and feel like apps written in advanced other languages.

Expert Excel and Office Scripts development

Eppert Consulting has been building business-grade Excel VBA and Web Applications for over 20 years. Our team of developers is experienced in all the technologies necessary to create a robust desktop or web-based Excel app for you and your team, using either VBA or Office Scripts.

To learn more about our services, contact us today for a no-obligation chat.