さまざまなツールを開発している最中、見栄え重視になりつつあり、各シートの冒頭へ共通タイトルを設置するようなデザインをし始めています。
となると、共通のタイトルに対して各シートごとのサブタイトルが必要となり、何ならシート名と同じであれば合理的＆メンテしやすいなーと思いまして、このブログタイトルに行き着きました！笑
ただ、ネット上に転がっているノウハウを漁ってみたのですが、後述するやり方しか見つからず…、なんかスマートじゃない💦
■ 動作環境
OS: Windows 8.1（64ビット）
Excel: 2013（32ビット）
■ 完成イメージ
イメージ図どおり、B3セルへシート名を自動表示します。
■ CELL関数
テクニックの本質は、「シート名を含めたファイルパス名から、シート名を抜き出す」です。このうち、「シート名を含めたファイルパス名」を取得するためにCELL関数を利用します。
＜構文＞
> CELL(検査の種類, [対象範囲])
＜引数＞
検査の種類は、返す情報の種類を指定するテキスト値を指定します。
"filename": 対象範囲を含むファイルのフル パス名 (文字列)。 対象範囲を含むワークシートが保存されていなかった場合は、空白文字列 ("")。
"protect": セルがロックされていない場合は 0、ロックされている場合は 1。
"type": セルに含まれるデータのタイプに対応する文字列定数。 セルが空白の場合は "b" (Blank の頭文字)、セルに文字列定数が入力されている場合は "l" (Label の頭文字)、その他の値が入力されている場合は "v" (Value の頭文字) になります。 など…
対象範囲は、省略可能で、情報が必要なセルを指定します。
＜戻り値＞
セルの書式、位置、または内容に関する情報を返します。
■ ファイルパス名の文字列編集
最終的に設定する式は、次のとおりです。
> CELL("filename",A1)
"filename"と指定することで、（シート名を含めた）Excelファイルのフルパス名を取得します。対象範囲は、該当シートのどのセルでも構いませんのでA1を指定しています。
この式では、次のような値が返ってきます。
『C:\Users\(user)\Desktop\ツール\シフト表\[tool_shift.xlsm]＠カレンダー管理』
返される文字列の構成は、次のとおりです。
『（パス名）\[（Excelファイル名）]（シート名）』
※ （Excelファイル名）の前後にある"["、"]"は、それぞれ固定文字列として挿入される
最終目標は、（シート名）を取得することですので、着目すべきは（シート名）の左に位置する"]"になるわけです。よって、次のとおり文字列編集するだけです💦
FIND関数で"]"の位置把握
LEN関数でフルパス名の長さ把握、そこから"]"の位置を引く （それは即ち（シート名）の長さ把握…に繋がる）
RIGHT関数でフルパス名の右から、（シート名）の長さ分を取り出す
ただし、以下注意点を念のため補足しておきます！
該当のExcelファイルは、（新規作成で開かれたばかりのものではなく）一度は保存されていること
フルパス名の文字列として"]"が含まれていないこと
■ まとめ
いかがでしたか？
これで、シート名が変更されたとしても、自動的にセル上の値も更新されるため、メンテナンス性が高いですよね！さまざまなツールデザインにおいて調整しそうです♪