Home » エクセルマクロ・Excel VBAの使い方 » ユーザー定義関数 » シート名を取得するユーザー定義関数

シート名を取得するユーザー定義関数

対象:Excel2003, Excel2007, Excel2010, Excel2013

Googleスプレッドシートの[スクリプトギャラリー]の中に、getAllSheetNamesというスクリプト(カスタム関数)があります。

シート名を取得するユーザー定義関数

このカスタム関数をインストールしてから、例えば、GoogleスプレッドシートのA1セルに
「=getAllSheetNames(ROW()-1)」
という数式を入力して、オートフィルすると、そのスプレッドシートに含まれるシート名一覧を作成することができます。

中身は以下のようなコードになっています。

function getAllSheetNames(index) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var allSheets = ss.getSheets();
 var allSheetNames = new Array();

 for (var i = 0; i < allSheets.length; i++)
  {
   allSheetNames.push(allSheets[i].getName());
  }
 return allSheetNames[index];
}
[スポンサードリンク]

SpreadsheetクラスのgetSheetsメソッドで、全シートを配列として取得し、
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var allSheets = ss.getSheets();

ループ処理の中で、
 for (var i = 0; i < allSheets.length; i++)

シート名を配列allSheetNamesに格納し、
   allSheetNames.push(allSheets[i].getName());

引数で指定された数値に該当するシート名を返す、
 return allSheetNames[index];

という作りになっています。

Excelで同じようなユーザー定義関数を作る場合、どのようなコードになるのか気になり、VBA(Visual Basic for Applications)で作ってみました。

▼シート名を取得するユーザー定義関数
Function SheetNames(シート番号 As Variant) As String

 Dim idx As Variant

 idx = シート番号
 Application.Volatile

 On Error GoTo ERR_HNDL

 If IsNumeric(idx) = False _
 Or idx <> Int(idx) _
 Or idx = 0 _
 Or idx > Sheets.Count Then
  Err.Raise -1
 Else
  SheetNames = Sheets(idx).Name
 End If

 Exit Function

ERR_HNDL:

 SheetNames = ""

End Function

上記のFunctionプロシージャをVBE(Visual Basic Editor)の標準モジュールに貼り付けてから、ワークシート上に
「=SheetNames(1)」
という数式を入力すると、そのブックの1枚目のシート名が表示されます。

A1セルに
「=SheetNames(ROW())」
という数式を入力して下方向にオートフィルすると、シート名が一覧表示されます。

GoogleスプレッドシートのgetAllSheetNamesカスタム関数は、引数に「0」を指定すると1枚目のシート名が取得できますが、「1」を指定したときに1枚目のシート名が取得できるほうが使いやすそうですから、ここは仕様を変更しました。

GoogleスプレッドシートのgetAllSheetNamesカスタム関数と同じような動きにさせるために、上記のコードは少々長くなっていますが、実は以下のような一行のプロシージャで、ほぼ目的は達成できてしまいます。

Function SheetNames(シート番号 As Long) As String
 SheetNames = Sheets(シート番号).Name
End Function

Excel VBAの、Sheetsプロパティの引数に数値を指定すると、その数値に該当するWorksheetオブジェクトやChartオブジェクトが返されます。

取得できたWorksheetオブジェクトやChartオブジェクトの名前を、ユーザー定義関数SheetNamesの戻り値としているのが
 SheetNames = Sheets(シート番号).Name
というコードです。

Excel VBAの場合は、特にループを回すこともなくシート名を取得するユーザー定義関数ができてしまうのです。

但しこのままでは、引数によってはエラーが返されてしまいます。

GoogleスプレッドシートのgetAllSheetNamesカスタム関数では引数に、例えば、シートが存在しないような大きな数値や、文字列指定された場合でもエラーを表示したりしません。

同じような動きにするために、
Function SheetNames(シート番号 As Long) As String
 SheetNames = Sheets(シート番号).Name
End Function
に、処理を追加したのが上記のコードです。

エラーが発生したときはERR_HNDLに飛ばすようにして
 On Error GoTo ERR_HNDL

ERR_HNDL内で、空白文字列を返すようにしています。
 SheetNames = ""

引数が、数値でないとき、整数でないとき、0のとき、シート枚数より多いときは、
 If IsNumeric(idx) = False _
 Or idx <> Int(idx) _
 Or idx = 0 _
 Or idx > Sheets.Count Then

架空のエラーを上げてERR_HNDLに飛ばしています。
  Err.Raise -1

そうでない場合に、シート名を返すようにしています。
 Else
  SheetNames = Sheets(idx).Name

[スポンサードリンク]

Home » エクセルマクロ・Excel VBAの使い方 » ユーザー定義関数 » シート名を取得するユーザー定義関数

「ユーザー定義関数」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.