Excel Office Script voorbeelden

Inleiding

Sinds Juli 2020 kunnen Microsoft 365 gebruikers hun acties in Excel laten opnemen als Office Script macro's. Eerder plaatste ik al een artikel dat laat zien hoe je hiermee start. Op deze pagina toon ik wat voorbeelden van Office scripts die je niet kunt opnemen met de Script recorder, maar die wel nuttig zijn.

Sommige van deze scripts bevatten letterlijke waarden, (de waarden tussen aanhalingstekens). Dit betekent, dat zaken als namen van werkbladen hard-coded in de voorbeelden staan: "ToC". Wil je dus een andere naam gebruiken, dan zal je het script moeten bewerken. Omdat Microsoft ons nog geen mogelijkheden heeft gegeven om een waarde op te vragen bij de gebruiker, zullen we voor nu dit soort invoerwaarden zelf in onze scripts moeten opnemen helaas.

Download de volledige code (zip bestand met alle scripts) hier.

Een inhoudsopgave toevoegen aan je werkmap

Werk je in Excel Online en heb je snel een inhoudsopgave nodig in je werkmap? Hier is een Office Script waarmee je er een genereert. Dit script maakt een lijst van alle werkbladen en van alle grafieken, met snelkoppelingen naar hun locaties.

Eerst toon ik hier de "main" functie en geef ik een stapsgewijze uitleg:

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(2, 0);
  reportCell.getResizedRange(0, 1).setValues([["Worksheet", "Link"]]);

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

  reportCell = reportCell.getOffsetRange(newTable.getRowCount() + 2, 0)
  newTable = addTocTable("Charts", workbook, reportCell);
  reportCell = reportCell.getOffsetRange(2, 0)
  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(1, 3).setFormulaR1C1("=HYPERLINK(\"#\"&RC[-1],\"Link\")");

  tocSheet.activate();
}

Dit zijn de stappen die deze code uitvoert::

  • Controller of er een werkblad genaamd ToC is, zo ja, verwijder het werkblad;
  • Haal een lijst met alle werkbladen open stop de lijst in de variabele allSheets;
  • Voeg een werkblad to met de naam "ToC" en ken dit blad toe aan de variabele tocSheet;
  • Ken cel B2 toe aan de variabele reportCell;
  • Voeg een tabel toe aan de inhoudsopgave middels de functie addTocTable (zie hieronder);
  • Om ervoor te zorgen dat we beginnen met het toevoegen van bladnamen op de juiste rij, verschuiven we reportCell twee rijen zodat deze ny wijst naar cel B4;
  • We voegen de koppen toe aan de nieuwe tabel (Worksheet en Link);
  • Nu is het tijd om door alle werkbladen in de allSheets collectie te loopen en iedere naam in te vullen in de eerste kolom van onze tabel;
  • Nadat we alle werkbladnamen in de eerste kolom hebben gezet gebruiken we de HYPERLINK functie in de tweede kolom om, gebruik makend van de bladnamen uit de eerste kolom, een snelkoppeling naar ieder tabblad te maken;
  • We verschuiven de reportCell positie met het aantal rijen in onze tabel (plus twee extra rijen om ruimte te maken);
  • Nu voegen we de koptekst voor de grafieken toe en een lege tabel, wederom gebruik makend van de functie addTocTable;
  • De Grafieken tabel kent vier kolommen: Worksheet, Name, Location en Link;
  • We lopen alle werkbladen af en voor ieder werkblad:
    • loopen we door alle aanwezige grafieken;
    • vullen we de grafieken tabel met de juiste informatie: Werkblad naam, Garfiek naam en Locatie (om de locatie van een grafiek te krijgen moest ik een nieuwe functie schrijven genaamd getCellUnderChart omdat Office Script geen TopLeftCell eigenschap kent, zoals het grafiek in VBA wel heeft);
    • Er bleek een probleempje bij grafieken op tabbladen met een spatie in de naam; het juiste adres van de topleftCell in werkbladen die apostrofjes om hun naam krijgen is: 'Blad 2'!F1. Echter, als je 'Blad 2'!F1 in een cel zet, dan zal Excel die eerste apostrof verwijderen. Vandaar het if statement dat in zulke gevallen een extra apostrof toevoegt.
  • Nadat we alle grafieken op alle werkblden hebben gehad plaatsen we wederom middels de HYPERLINK functie een snelkoppeling naar de plaats van de grafiek, gebruik makend van de adresinformatie in de derde kolom.

De main functie gebruikt twee hulpfuncties, hier is de addTocTable functie:

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

Het enige dat deze functie doet is het invullen van een koptekst in de cel 'location', deze cel de stijl "Heading 1" geven en een neiuwe tabel toevoegen. De functie geeft de tabel als object door aan de aanroepende functie zodat we dat object daarin verder kunnen gebruiken.

Het kan zijn dat dit script een foutmelding geeft op de tweede regel. Dat komt dan omdat het script uitgaat van een Engelse Excel interface. Verander "Heading 1" dan in de juiste stijl. Beschikbare stijlen vind je op je Start tab onder de knop Stijlen.

En hier is de getCellUnderChart functie:

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

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

Het basisprincipe is simpel: de functie doorloopt eerst alle cellen van het werkblad in horizontale righting, beginnend bij cel A1, totdat de Left waarde van die cel groter is dan de Left positie van de grafiek. Vervolgens wordt hetzelfde gedaan, maar dan in vertikale richting om cel met dezelfde de Top positie als de grafiek te vinden. Omdat deze methode ons eigenlijk een cel teveel naar rechts en naar omlaag geeft, verschuiven we de gevonden cel één cel omhoog en naar links (-1, -1) zodat we de juiste cel als linkerbovenhoek teruggeven als functieresultaat.

Een lijst met bereiknamen genereren

Desktop Excel heeft een eenvoudige optie om een lijst met alle bereiknamen te krijgen (Formules tab, Gebruik in formule vervolgkeuzelijst, Lijst plakken). Maar Excel on-line kent deze functionaliteit niet. Hier is een Office script dat een werkblad genaamd "Names List" toevoegt aan je bestand waarin een lijst wordt geplaatst met alle bereiknamen en enkele eigenschappen.

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(RowCt, 1).getResizedRange(0, 3).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(RowCt, 1).getResizedRange(0, 3).setValues([
        [sheetName, myNames[j].getName(), "'" + myNames[j].getFormula(), "'" + myNames[j].getVisible()]
      ]);
    }
  }
  let newTable = workbook.addTable(selectedSheet.getRange("B2").getSurroundingRegion(), true);
  newTable.getRange().getFormat().autofitColumns();
}

De resulterende tabel ziet er ongeveer zo uit:
List of range names

Download de volledige code (zip bestand met alle scripts) hier.

Comments

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.




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