Home Nieuwsbrief

This page in EnglishHome > Artikelen index >

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(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();
}

Dit zijn de stappen die deze code uitvoert::

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(01).setPredefinedCellStyle("Heading 1");
  location = location.getOffsetRange(20);
  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.

En hier is de getCellUnderChart functie:

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);
}

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(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();
}

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.

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.