Home Newsletter

Deze pagina in het NederlandsHome > Article index >

Excel Office Script Examples

Introduction

Since July 2020, Microsoft 365 users can record their actions in Excel on-line into Office Script macros. Earlier, I've posted an article that shows you how to get started. On this page, I will list some example scripts which you cannot record but might come in handy.

Some of these scripts contain literal values, (the values between quotes). This means things like worksheet names are hard-coded into the example scripts: "ToC". So if you need a different worksheet name, edit the script accordingly. Because Microsoft has not yet given us a way to prompt the user for a value, we must edit the script to change what sheet it affects.

Download the full code (zip file with all scripts) here.

Adding a table of Contents to your workbook

Working in Excel Online and need a table of Contents for your workbook quickly? Here's an Office Script that produces one. It generates a list for all worksheets and all charts, with hyperlinks pointing to their locations.

First let me show the main function and explain it step by step.

function main(workbook: ExcelScript.Workbook) {
  
  if (workbook.getWorksheet("ToC")) {
    let temp = workbook.getWorksheet("ToC")
    temp.delete();
  }

  let allSheets = workbook.getWorksheets();
  let tocSheet = workbook.addWorksheet("ToC");
  let reportCell = tocSheet.getRange("B2");
  let newTable = addTocTable("Worksheets", workbook, reportCell);
  reportCell = reportCell.getOffsetRange(20);
  reportCell.getResizedRange(01).setValues([["Worksheet""Link"]]);

  for (let i = 0; i < allSheets.length; i++) {
    reportCell.getOffsetRange(i + 10).setValue(allSheets[i].getName());
  }
  reportCell.getOffsetRange(11).setFormulaR1C1("=HYPERLINK(\"#'\"&RC[-1]&\"'!A1\",RC[-1])");

  reportCell = reportCell.getOffsetRange(newTable.getRowCount() + 20)
  newTable = addTocTable("Charts", workbook, reportCell);
  reportCell = reportCell.getOffsetRange(20)
  reportCell.getResizedRange(0,3).setValues([["Worksheet""Name""Location""Link"]]);
  let k = 0;
  for (let i = 0; i < allSheets.length; i++) {
    let allCharts = allSheets[i].getCharts();
    for (let j = 0; j < allCharts.length; j++) {
      k++;
      let theSheet = allSheets[i];
      reportCell.getOffsetRange(k, 0).setValue(allSheets[i].getName());
      reportCell.getOffsetRange(k, 1).setValue(allCharts[j].getName());
      let topLeftCell = getCellUnderChart(allCharts[j], allSheets[i]);
      let topLeftAddress = topLeftCell.getAddress();
      if( topLeftAddress.charAt(0)=="'"){
        topLeftAddress="'" + topLeftAddress;
      }
      reportCell.getOffsetRange(k, 2).setFormula(topLeftAddress);
    }
  }
  reportCell.getOffsetRange(13).setFormulaR1C1("=HYPERLINK(\"#\"&RC[-1],\"Link\")");

  tocSheet.activate();
}

These are the important steps in this code:

The main function uses two helper functions, here's the addTocTable one:

function addTocTable(caption: String, workbook: ExcelScript.Workbook, location: ExcelScript.Range): ExcelScript.Table {
  location.setValue(caption);
  location.getResizedRange(01).setPredefinedCellStyle("Heading 1");
  location = location.getOffsetRange(20);
  let newTable = workbook.addTable(location, true);
  return newTable;
}

All it does is add the caption in the location cell, style it as "Heading 1" and add a new one-column table. The function returns the table as an object so we can use that object in our calling routine.

And here is the getCellUnderChart function:

function getCellUnderChart(cht: ExcelScript.Chart, ws: ExcelScript.Worksheet): ExcelScript.Range {
  let topLeftCell = ws.getRange("A1");
  let i = 0;
  do {
    i++;
    topLeftCell = topLeftCell.getOffsetRange(0, i)
  }
  while (topLeftCell.getLeft() < cht.getLeft());
  i = 0;
  do {
    i++;
    topLeftCell = topLeftCell.getOffsetRange(i, 0)
  }
  while (topLeftCell.getTop() < cht.getTop());

  return topLeftCell.getOffsetRange(-1, -1);
}

The basic principle is simple: the function first loops through the cells of the worksheet horizontally, starting from cell A1, until it has found a cell with a Left value that is greater than or equal to the chart's Left position. After that it does the same vertically to find the Top position. Because the two loops take us one column too far to the right and one row too far down, we offset the found cell by (-1, -1) so we return the correct cell which denotes where the top-left corner of the chart is located.

Adding a list of Range names

Desktop Excel has a simple feature to add a list of range names to your workbook (Formulas tab, Use in Formula drop-down, Paste List). But Excel on-line does not (yet) have that feature. Here is an Office script which adds a worksheet calles "Names List" to your workbook, listing all range names and some of their properties.

function main(workbook: ExcelScript.Workbook) {
  var RowCt = 1;
  var sheetName = "";
  let MyNames = workbook.getNames();
  let selectedSheet = workbook.getWorksheet("Names list");
  if (selectedSheet) {
    selectedSheet.delete();
  }
  let worksheets = workbook.getWorksheets();
  selectedSheet = workbook.addWorksheet("Names list");
  selectedSheet.getRange("B2:E2").setValues([["Scope""Name""RefersTo""Visible"]]);
  for (let i = 0; i < MyNames.length; i++) {
    RowCt++
    selectedSheet.getCell(RowCt1).getResizedRange(03).setValues([
      ["Workbook"MyNames[i].getName(), "'" + MyNames[i].getFormula(), "'" + MyNames[i].getVisible()]]);
  }
  for (let i = 0; i < worksheets.length; i++) {
    sheetName = worksheets[i].getName();
    var myNames = worksheets[i].getNames();
    for (let j = 0; j < myNames.length; j++) {
      RowCt++;
      selectedSheet.getCell(RowCt1).getResizedRange(03).setValues([
        [sheetName, myNames[j].getName(), "'" + myNames[j].getFormula(), "'" + myNames[j].getVisible()]
      ]);
    }
  }
  let newTable = workbook.addTable(selectedSheet.getRange("B2").getSurroundingRegion(), true);
  newTable.getRange().getFormat().autofitColumns();
}

The resulting table looks like this:
List of range names

Download the full code (zip file with all scripts) here.

 


Comments

All comments about this page:


Comment by: John from Ireland (24-8-2021 19:21:00) deeplink to this comment

This is an extremely useful script which I will use for my daily reporting needs.


Comment by: Jan Karel Pieterse (25-8-2021 11:00:00) deeplink to this comment

Hi John,

Thanks. Please note that I've updated the script today so it works correctly for both sheets with and without a space in the sheet name


Comment by: MIKE SMITH (27-8-2021 21:12:00) deeplink to this comment

Thanks for this. I have so many questions...
Does this code editor expand?
How do you debug?
Is there an equivalent of VBA's Immediate Window?


Comment by: Jan Karel Pieterse (27-8-2021 23:45:00) deeplink to this comment

Hi Mike,

We're limited to a maximum width of the editor.
Debugging is a struggle, there is no way to step the code for example so you have te revert to inserting console.log statements at strategic places. Cumbersome.
There is no immediate window.
But the intellisense is way better and indenting is automatic.


Comment by: LE GUEN Frédéric (29-8-2021 05:58:00) deeplink to this comment

Very good example to learn Office Script.
The way to select the target cells to write the result with getOffsetRange, getResizedRange is confusing for the moment for me. I have to practice ;)
Thanks for sharing this


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website according to our Privacy Policy.