Excelのシート名を取得する方法！関数の他VBAで一括取得も！

Excelでシート名を取得したいときはないでしょうか。

けど、そんな中で悩むことは、

・Excelでシート名を取得したいが方法がよくわからない
・VBAでExcelにあるシート名を一括で取得したいが方法がよくわからない

ですよね。

今回はそんなお悩みを解決する

・Excelでシート名を取得する方法
・VBAでExcelにあるシート名を一括で取得する方法

についてまとめます！

Excelのシート名を取得するイメージ

Excelのシート名を取得するイメージについて説明をします。

まず、ワークシート関数で、関数を実装したシートのシート名を表示させます。

次にVBAでExcelブック内にあるすべてのシートのシート名を一括で取得していきます。

ブック内のシート構成を把握したいときに便利ですね。

早速実装して試してみましょう。

Excelブックにシートを作成する

Excelブックにシートを作成しましょう。

サンプルでは3つのシートを用意しました。

ワークシート関数でシート名を取得する

ワークシート関数でシート名を取得する方法について説明をします。
シート名を取得する関数はCELL関数となります。

CELL関数の書式/機能/引数

関数名	CELL
機能	セルの書式、位置、内容についての情報を返します。
書式	=CELL(検査の種類, ［対象範囲］)
引数	検査の種類：セル情報の種類を指定します。範囲：情報が必要なセルを指定します。省略すると、現シートの情報が返されます。

CELL関数はセルの書式、位置、内容についての情報を返す関数で、パラメータを指定することにより取得情報を絞り込むことができます。

検査の種類で指定できるパラメータは以下の通りです。

パラメータ	説明
“address”	対象範囲の左上隅にあるセルの参照を表す文字列。?
“col”	対象範囲の左上隅にあるセルの列番号。
“color”	負の数を色で表す書式がセルに設定されている場合は 1。それ以外の場合は 0 (ゼロ)。
“contents”	対象範囲の左上隅にあるセルの値 (数式ではない)。
“filename”	対象範囲を含むファイルのフルパス名 (文字列)。対象範囲を含むワークシートが保存されていなかった場合は、空白文字列 (“”)となります。
“format”	セルの表示形式に対応する文字列定数。各種の表示形式に対応する文字列定数については、次の表を参照してください。セルが負数に対応する色で書式設定されている場合、文字列定数の末尾に “-” が付きます。正数またはすべての値をかっこで囲む書式がセルに設定されている場合、結果の文字列定数の末尾に “()” が付きます。
“parentheses”	正の値またはすべての値をかっこで囲む書式がセルに設定されている場合は 1。それ以外の場合は 0。
“prefix”	セルの “文字位置” に対応する文字列定数。セルが左詰めの文字列を含むときは単一引用符 (‘)、右詰めの文字列を含むときは二重引用符 (“)、中央揃えの文字列を含むときはキャレット (^)、両揃えの文字列を含むときは円記号 (\)、また、セルにそれ以外のデータが入力されているときは空白文字列 (“”) になります。
“protect”	セルがロックされていない場合は 0、ロックされている場合は 1。
“row”	対象範囲の左上隅にあるセルの行番号。
“type”	セルに含まれるデータのタイプに対応する文字列定数。セルが空白の場合は “b” (Blank の頭文字)、セルに文字列定数が入力されている場合は “l” (Label の頭文字)、その他の値が入力されている場合は “v” (Value の頭文字) になります。
“width”	2 つの項目を含む配列を返します。配列の 1 つめはセルの列幅で、整数に丸められます。セル幅の単位は、既定のフォントサイズの 1 文字の幅と等しくなります。配列の 2 つ目の項目はブール値で、列幅が既定値の場合は TRUE、幅がユーザーによって明示的に設定されている場合は FALSE です。?

参照：https://support.microsoft.com/ja-jp/office/cell-%E9%96%A2%E6%95%B0-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

今回指定するパラメータは”filename“となります。

まずはブックのパス情報を取得して、後でシートのみに絞り込み抽出していきます。

関数を実装してシート名を取得する

使用する関数は以下の通りです。

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

A1セルに数式を入れると、

はい、今のシート名がセルに表示されましたね。

数式の説明

数式の説明をします。

はじめにCELL(“filename”)でブックのパスを取得します。

CELL("filename")

数式をセルに入力すると実際に取得できるパス情報が確認できます。

ただ、パスの中にシート名が含んでいますので、次はシート名のみに絞り込んでいく処理を追加します。

使う関数はREPLACE関数で、不要なパス情報を消していきます。

REPLACE関数の書式は以下の通りです。

関数名	REPLACE
機能	指定された文字数の文字を別の文字に置き換えます。
書式	REPLACE(文字列, 開始位置, 文字数, 置換文字列)
引数	文字列：必ず指定します。置き換えを行う文字列を指定します。開始位置：必ず指定します。置換文字列と置き換える先頭文字の位置 (文字番号) を数値で指定します。文字列の先頭文字の位置が 1 になります。文字数：必ず指定します。置換文字列と置き換える文字列の文字数を指定します。置換文字列：必ず指定します。文字列の一部と置き換える文字列を指定します。

参考：https://support.microsoft.com/ja-jp/office/replace-%E9%96%A2%E6%95%B0-replaceb-%E9%96%A2%E6%95%B0-8d799074-2425-4a8a-84bc-82472868878a#:~:text=REPLACE%20%E9%96%A2%E6%95%B0%E3%81%AF%E3%80%81%E6%96%87%E5%AD%97%E5%88%97,%E3%81%AE%E6%96%87%E5%AD%97%E3%81%AB%E7%BD%AE%E3%81%8D%E6%8F%9B%E3%81%88%E3%81%BE%E3%81%99%E3%80%82

REPLACE関数の引数の文字列に「CELL(“filename”)」を割り当て開始位置は1とします。

REPLACE(CELL("filename"),1,

次にFIND関数でブックのパスにある”]”を検索し、文字数を取得します。

FIND("]",CELL("filename"))

これによりシート名とパス情報の境目の文字の位置が取得できます。

REPLACE関数の引数の文字数に境目の文字の位置を割り当て、最後の置換文字列を””にすることにより、パス部分の文字列を削除していきます。

パス情報が削除され、シート名のみに絞られましたね。

VBAで複数のシート名を一括取得する

次にVBAで複数のシート名を一括取得する方法について説明をします。

シートは3つあり、そのシート名すべてを取得し今見ているシートのA列へ出力していきます。

VBAコード

VBAは以下の通りです。

Sub シート名一覧取得()
    Dim objSheetName As Worksheet
    Dim i As Integer

    i = 1
    
    With ActiveSheet

        'Sheetsコレクションから1シートづつオブジェクトを取得します。
        For Each objSheetName In Sheets
            
            'シート名を取得し、表示シートのA列へ出力します。
            .Cells(i, 1).Value = objSheetName.Name
        
            i = i + 1
        Next
    
    End With
    
End Sub