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

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プロシージャを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