In today’s fast-paced business world, automation is key to enhancing productivity and streamlining workflows. While Visual Basic for Applications (VBA) has long been a go-to tool for automating tasks in Microsoft Excel and other Office applications, its role has evolved over the years. In this blog post, we will explore how VBA is losing space for other Microsoft tools, especially the Office Scripts.

VBA: Quick overview

Visual Basic for Applications (VBA) was introduced in the 1990s, offering users the ability to create macros and automate repetitive tasks within Excel and other Office applications. It was a revolutionary tool that allowed business users to enhance their productivity by reducing manual efforts and speeding up processes.

Over time, the limitations of VBA have become apparent in the face of evolving business needs and advancing technology. Businesses are encountering challenges stemming from the tool’s complexity, dependency on Microsoft Office applications, limited integration with other platforms, and difficulties in scaling solutions. Consequently, organizations are actively seeking alternatives to VBA for effective and future-proof business automation.

The Limitations of VBA in Microsoft 365 Web

As businesses embrace the web-based Microsoft 365 environment, VBA is losing space, due to its dependency on running code locally on users’ machines, which is not supported in the cloud-based environment. It means that many VBA macros and scripts developed for the desktop version may not work in the web environment.

Some companies even have some departments using either Microsoft 365 environments (Desktop and Web), resulting in limitations for those that need to run VBA solutions in the cloud application. It can hinder the company transition to the cloud and disrupt collaborative workflows, posing a challenge to their efficiency and productivity goals.

Office Scripts: the VBA from Web

Office Scripts is a powerful feature in Microsoft Excel that allows you to automate tasks and processes within your spreadsheets without requiring deep programming knowledge. As in VBA, it provides a way to write and execute custom scripts or macros to perform repetitive actions, streamline workflows, and increase productivity.

With some differences, think of Office Scripts as a VBA for Microsoft 365 Web. These scripts are written using TypeScript (which is a programming language based in JavaScript), which has a large online community for troubleshooting.

With Excel Scripts, you can automate various actions, such as data manipulation, calculations, formatting, data validation, and more. For example, you can create a script to automatically format data in a consistent manner, generate reports from raw data, perform complex calculations, or even interact with external data sources.

A feature that makes Office Scripts very powerful is its integration to Power Platform. As Power Automate has a specific action to run Office Scripts, it allows data from spreadsheets to be interacted with several Microsoft and third-part products. Check this article about how to integrate Excel and Power Automate via Office Scripts.

How to access the Office Scripts?

To find Office Scripts in Excel Web, you can navigate to the “Automate” tab located in the toolbar at the top of the screen.

Within the “Automate” tab, you will find a comprehensive overview of all the Scripts that have been either created by or shared with you. To create a new script, navigate to the “Scripting Tools” section and click on the “New Script” option.

A new side bar with the code editor may appear in your screen, with a space for entering your code, just like in VBA or in any other IDE (Integrated Development Environment).

Once you have created your script, you have a couple of options for executing it. Firstly, you can click on the “Run” button to test the script and observe the results. Alternatively, you can save the script and add a button to your spreadsheet, enabling you to run it directly from within your spreadsheet.

To learn more about how Office Scripts can improve your productivity, check this article on how to get started with this tool.