VBA (Visual Basic for Applications) was created in 1993 by Microsoft and integrated into version 5 of Excel (who remembers!?). Since then, millions of users around the world have sought to automate repetitive tasks in Excel, often turning Excel into applications that serve the most diverse purposes (some quite funny!). 30 years later, VBA for Excel is still alive and with legions of enthusiastic followers and some critical processes in some companies continue to depend on it.

Maybe that's why Excel Desktop maintains the availability of VBA, but without major development. It's as if Microsoft didn't really have the courage to remove it for fear of disappointing users and provoking all kinds of incidents with different kinds of consequences. Here's what's available in my version of Excel updated to November 2023:

It doesn't seem very current and the reason is that development has been cloud-oriented. Excel and the Power platform now work entirely in the cloud, so Office Scripts is slowly replacing VBA for Excel.

What is Office Scripts?

Office Scripts is an automation tool that allows you to create and run scripts in Office applications. Scripts are blocks of code that can be used to automate tasks, such as formatting spreadsheets, creating reports, or sending emails.

Key features of Office Scripts:

• They are written in JavaScript, a programming language that is relatively easy to learn and use
• They can be created by using the Action Recorder, which records the actions that the user performs in an Office application, or by using the Code Editor, which allows you to create custom scripts
• They can be used in any Office application, including Word, Excel, PowerPoint, and Outlook
• They can be used to automate simple tasks, such as entering formulas or charts into spreadsheets, or to perform more complex tasks, such as creating custom apps or reports.

Office scripts are a powerful tool that can be used to increase Office productivity and flexibility. It does not seem, however, that they have all the flexibility and scope of VBA for the time being, but that is the way forward at the moment.

How to run Office Scripts in Excel?

You can run an Office script in Excel from the "Automate" menu on the top ribbon (you'll need to enable it first) by choosing the script you want.

An interesting feature of scripts is that they can be run on any Excel file in the domain to which they belong. That is, the script is a file that is stored in a certain cloud within an Office 365 domain and that can run "on top" of any Excel file within that domain.

Obviously, it must be taken into account that the logic present in the code must be compatible with the file in question.

Another way to run Office scripts is from Power Automate, which allows integration with the Power platform. From here, you can automate just about everything:

• Create Excel files
• Send emails with Excel files attached
• Manipulate existing Excel files
• Get data from Excel files and send it to another application or save it to a file
• Among many other solutions

In this post, we're going to read and write to Excel cells from Power Automate. I think it's obvious that this type of automation will help you discover many solutions:

• Collecting Excel data allows you to use existing files and do something with them. For example, an email address saved in a particular cell will allow Power Automate to send a message to a recipient. Obtaining the value of units in stock of a certain item can be used to inform the sales or logistics team that it is above or below the security value.
• Writing data in Excel can be used to automate dynamic reports or calculations. For example, entering a certain value in a cell will update all the formulas and charts that depend on it.

Let's start with a simple example: our current stock map is as follows:

We intend to collect the value of the stock from the city of Porto and send the respective value (10,000 units) to the respective manager, Manuel.

As a requirement, we already know, the Excel file will have to be stored in the cloud, within our domain so that it is accessible by Power Automate in the same cloud. The script can be stored in the same location and can be created in Excel Desktop:

Creating Office Scripts in Excel

The development of the script will be carried out in the editor that opens in the right-hand side pane of Excel. The demo code loads immediately and serves to help us get started:

Let's replace this code with this one:

function main(workbook: ExcelScript.Workbook, cidade: string) {

  let ws = workbook.getActiveWorksheet();

  let rng = ws.getRange("B3:E5");

  let rgnArr = rng.getValues();

  for (let i = 0; i <= rgnArr.length - 1; i++) {

    if (cidade === rgnArr[i][0]) {

      return rgnArr[i][3];

    }

  };

}

Basically, this script takes the following arguments:

• the workbook in which it will be executed, i.e. the Excel file represented above
• the city for which we intend to obtain the data

And the function returns the manager's email.

Let's save the script to SharePoint and go back to Power Automate to create a new flow with a manual trigger that requires the input "City" ("Cidade"):

To this trigger, let's add an action to run an Office Script:

The third step will make it easier for us to visualize the result of the script. Let's add a "Compose" action with the dynamic content "result" from the previous step:

The flow will now look like this:

To validate that the flow and script work, let's do a test. Let's run the flow with the input "Coimbra" and analyze the result:

Apparently, the flow went as predicted. The script considered the "City" parameter and searched for it in the Excel table. Once found, you returned the email of the respective manager, which is This email address is being protected from spambots. You need JavaScript enabled to view it..

Assuming that we are interested not only in the manager's email, but also in the value of the stock, the manager's name and his email, we could change the above script to the following:

function main(workbook: ExcelScript.Workbook, cidade: string) {

  let ws = workbook.getActiveWorksheet();

  let rng = ws.getRange("B3:E5");

  let rgnArr = rng.getTexts();

  let stock: string | number | boolean;

  let nome: string | number | boolean;

  let email: string | number | boolean;

  for (let i = 0; i <= rgnArr.length - 1; i++) {

    if (cidade === rgnArr[i][0]) {

      stock = rgnArr[i][1];

      nome = rgnArr[i][2];

      email = rgnArr[i][3];

      return stock + '|' + nome + '|' + email

    }

  };

}

As you can see in this code, the value that the script returns is now not a single value, but rather an array that contains the values of the three columns. We named this array rngArr and chose to return the results by concatenating the three texts with a "pipe" (|) separator.

On the Power Automate side, this flow could be complemented right after the "Compose" step, which obtains the result of the script with the initialization of an array whose expression in the "Value" field would be:

split(outputs('Compose_Resultado'), '|')

So what we can do next is initialize three distinct variables:

The code for the StockValue variable is as follows:

trim(variables('ResultadoArray')?[0])

Basically, this expression returns the first element of the array (index 0) and removes unnecessary spaces. The code for the variables ManagerName and ManagerEmailName is identical, changing only the index, which would be, respectively, 1 and 2.

Finally, to send the result by email to the respective manager, we would add the following step:

With the variables properly defined, it's quite easy to work with Power Automate's dynamic content! The end result would then be an email identical to the following:

By identifying business rules and organizational processes, the possibility of automating email notifications gains new contours. It is possible to get management information to decision-makers at the right time.