【Officeスクリプト】OfficeスクリプトでExcelVBAのCurrentRegionプロパティっぽいことをしてみる

alice

2023年2月14日 07:39

こんにちは。aliceです。
最近はまっていることは、YouTubeで和風な曲をかけながら日本茶を飲むことです。そういえば最近ほうじ茶を飲んでいないなー🍵

今日はOfficeスクリプトでExcelVBAのCurrentRegionプロパティっぽいことをしてみました。

getSurroundingRegionメソッドを使うと同じようなことができますよ✨

getUsedRangeメソッド

まずはいつものgetUsedRangeを使ってみます

まず、こんな表があります。

A3セルからはじまる表があります。
A1からはじめよう！というツッコミは置いておいて、こんな表があります。

全体像です。

このようなときは、いつものgetUsedRangeメソッドで表を取得できます。

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Sheet1");
  const range = sheet.getUsedRange();
  console.log(range.getAddress());
}

ちょっと見にくいですが「Sheet1!A3:C8」とログが出力されていますね。

では、A1セルに表のタイトルを追加します。

ここでgetUsedRangeメソッドを使って表のアドレスを取得してみます。

「Sheet1!A1:C8」とログが出力されました。
まぁ、そうなりますよね。

getSurroundingRegionメソッド

そんなときはgetSurroundingRegionメソッドを使います。

getSurroundingRegionメソッド

この範囲内の Range 左上のセルの周囲の領域を表すオブジェクトを返します。周囲の領域は、この範囲に相対の空白の行と空白の列の任意の組み合わせで囲まれた範囲です。

Microsoft Learn

うん。わからない。
私の日本語レベルが崩壊しました…。

読んでもわからないので書いてみました。

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Sheet1");
  const range = sheet.getRange("A3").getSurroundingRegion();
  console.log(range.getAddress());
}

ちょっと見にくいですが「Sheet1!A3:C8」とログが出力されています。
表のアドレスを取得できました🤗

表のタイトル行以外の値をクリアする

それでは、ExcelVBAのCurrentRegionプロパティを使ってよくやる「表のタイトル行以外の値をクリア」してみます。

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Sheet1");
  const range = sheet.getRange("A3").getSurroundingRegion().getOffsetRange(1,0);
  range.clear(ExcelScript.ClearApplyTo.contents);
  console.log(range.getAddress());
}

実行前

実行後

値がクリアされました。
でもクリアするセル範囲が「Sheet1!A4:C9」になります。
気になる方はお気をつけください。

テーブルを動的に作成する

また、getSurroundingRegionメソッドを使うと、テーブルを動的に作成したいときにいい感じに作れます。

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Sheet1");
  const range = sheet.getRange("A3").getSurroundingRegion();

  const table = sheet.addTable(range, true);
  table.setName("tblPoteto");
}

実行前

実行後

A1セルに表のタイトルがないときはgetUsedRnageメソッドでも動的に作成できます。

Power Automateにテーブルを渡して処理したいときなど、使えそうですね。

Officeスクリプト記事まとめ📝

『Officeスクリプト入門講座』を書きました📝