エクセルの関数やVBAでシート名を取得する方法|関数&VBA

2018/03/29

セル名取得
Excelの シート名を取得・・・これができると意外と便利なんです。

たとえば、ある エクセルのシート名が 「2014年5月」 だったとします。
この、シート名「2014年5月」を「2014年6月」に変更しただけで、シート内で「2014年5月」と表示されていたセルがすべて 「2014年6月」 に変わってくれたら便利ですよね。

さらに、VBAで直接シート名を取得 したいけれど難しい、という方はいったん関数でセル上にシート名を取得してしまえば、VBAではそのセルの値を取得するだけの簡単なコードで済み、メッセージとして表示することも可能です。
※もちろん、関数を使わずにVBAのみで取得することも可能です。(後述)

今回は シート名を取得 し、関数の場合は セルに表示、VBAならばメッセージとして表示する方法について説明します。

シート名を取得してセルやメッセージに表示する

エクセルの関数を使ってシート名を取得する場合

エクセルでシート名を取得する方法としては、関数でシート名を取得する 場合と VBAでシート名を取得する 場合が考えられます。

まずは関数でシート名を取得する場合、つぎにVBAでシート名を取得する場合について説明します。


CELL関数 を中心に複数の関数を組み合わせる

下の例ではシート名 「5.まとめ」 をセルに反映させています。

シート名
まずは関数で解決する方法を紹介しますが、もちろんVBAでも可能です。この記事の後半をご覧ください。

解決を急いでいる方のために、まず先にシート名を取得セルに表示する関数を教えます。

=RIGHT ( CELL ("filename"), LEN ( CELL ("filename") ) - FIND ( "]", CELL ("filename") ) )

▲スマホでご覧の方は横画面でどうぞ。

CELL関数 でシートのフルパス情報を引き出し、さらにフルパスから 必要な文字列(シート名)だけを切り出すために、RIGHT関数LEN関数FIND関数 を組み合わせれば、シート名を取得セルに表示できます。


CELL関数

CELL関数は、そのセルの位置、書式、ファイル名を含むフルパスなど、選択されたセルに関するさまざまな情報を引き出すことができる関数です。

=CELL ( 検査の種類, [参照] )

「検査の種類」の部分には、引き出したい情報によってさまざまな引数が入ります。

▼CELL関数の引数一覧
CELL関数の引数一覧
例のように「検査の種類」を "filename" にするとセルのフルパスを値として返します。

[参照] は対象範囲ということですので省略が可能です。
今回のシート名を取得する関数でも省略されています。

エクセルファイル「Book1.xlsx」をローカルの Cドライブのtempフォルダに格納しているときに、ワークシート[5.まとめ] のA1セルに;

=CELL ( "filename" )

という関数を入力した場合の戻り値はフルパス情報となり;

C:\ temp\ [Book1.xlsx]5.まとめ

となります。

今回は上記フルパス情報から、“5.まとめ” の部分だけを切り出すことによってシート名を取得できました。
この 「切り出し」 のために、RIGHT関数LEN関数FIND関数が必要になります。

RIGHT関数、LEN関数、FIND関数についてはこのブログをご覧になっている方は問題なく使えるのではないかと思いますので、詳細は割愛させていただきます。

ちなみに下の図は、シート名を取得してさらに文字列を組み合わせた応用例です。

シート名取得
シート名取得の関数のあとに、「を取得する」という文字列をつづけて短文を作ります。

では、次はVBAを使ってシート名を取得する方法について説明します。


VBAを使ってシート名を取得する場合

VBAでシート名を取得

Excel VBAのコードの記述方法を知っている方ならば、VBAでシート名を取得するのはとてもシンプルで簡単です。

Sub Get_Sheet_Name()

  MsgBox Sheets(1).Name

End Sub

これだけです。

MsgBox関数とNameプロパティ

VBAでシート名を取得する場合、MsgBox関数Nameプロパティ を使います。

上のコードは1番目のシートのNameをメッセージボックスとして表示するサンプルです。

「Sheets(1) 」の部分は「Sheet1」という表現も可能です。
※Sheetと1の間にスペースは入れません。

みなさんの参考になれば幸いです。いろいろ試してみてください。

関連記事



併せて読んでおくとさらに便利

VBAでシート数を取得する方法と そこからできること|Excel VBA
VBA・Countプロパティ
VBAでシート数を取得する方法の解説。VBAを使って各シートに同じ作業をさせる場合は流動的に変化するシート数を取得しておく必要があります。Countプロパティを使って...

INDIRECT関数で別シートの同一セルにある値をリスト化・集計する方法
INDIRECT関数・集計
別シートの同一セルに入力された値を参照し、リスト化・集計する方法について解説します。Excelで複数シートの同一セルの値を一覧にまとめるワザがあるのです。

VBA カレントフォルダの取得と設定・変更|Excel VBA
VBA・CurDir関数
VBAで カレントフォルダ名 を取得したり、設定(指定)したり変更(移動)したりする方法について解説します。CurDir関数 や ChDirステートメント などの使い方についても触れています。

VBAで最終行を取得する方法と そこからできること|Excel VBA
VBA・最終行
VBAで最終行を取得する方法を解説。最終行の行番号を取得できることにより最終行の削除や、最終行までセルを選択したり繰り返したり色々なことが可能になります。

VBAで空白行を削除する方法|Excel VBA 高速技
VBA・Unionメソッド
VBAを使って空白行の削除方法を解説します。Unionメソッド を使用して空白行を高速で一括削除する方法をサンプルコード付きで解説。ぜひ参考にしてください。

※その他関連記事は、このページの下の方にもあります。

-EXCEL&VBA
-, ,

シェアしていただけると助かります。