Excel VBAでWorksheetオブジェクトを取得する方法として、ワークシート名を指定したり、インデックス番号を指定したり、ActiveSheetを対象としたりしますが、
- ワークシート名を指定した場合、もし、そのワークシート名が変更されたら取得できなくなります。[ブックの保護]で[シート構成]を保護するとシート名変更不可になりますが、シートタブの移動なども出来なくなるので利用者の自由度も下がります。
- インデックス番号を指定すると、シートタブの順序を変更すると対象が変わってきます。これも[ブックの保護]で移動出来なくすることは出来ますが。
- ActiveSheetを対象にした場合、そのVBAマクロを使用するシートが1つだけで、かつ、そのシートにのみVBAマクロを実行するボタン等があるのであれば問題ないと思いますが、アクティブでないシートがマクロの対象の場合や、複数のシートに対して条件分岐するようなVBAマクロの場合は、シート名の指定などが必要になってきます。
そこで、シート名を取得する関数を任意のセルに用意し、VBAマクロからはそのセルを参照してシート名を取得すると、仮にシート名が変更された場合でも対応できるようになります。以下がシート名を取得する関数です。
=RIGHT(CELL("filename",Sheet1!A1),LEN(CELL("filename",Sheet1!A1))-FIND("]",CELL("filename",Sheet1!A1)))
この場合は、Sheet1 のシート名を取得します。もしシート名が Sheet1 から変更されても、それに伴って新しいシート名を取得します。
任意の作業セルなどに上記関数を入れてシート名を取得し、VBAから Worksheets でそのセルの値を参照すれば、もしシート名が変更されてもVBA側に影響が出ません。
例えば該当のシートをアクティブにする場合のVBAは以下のような感じです（この場合は作業シートのA1セルにシート名取得関数）。
Sub SheetActive()
Dim ws As Worksheet
Set ws = Worksheets(worksheets("作業シート").Range("A1").Value)
ws.Activate
End Sub
分解して解説
CELL関数
CELL("filename",Sheet1!A1)
CELL(検査の種類,対象範囲)関数は、セルの情報を取得します。
検査の種類は複数あり、今回使用する filename は、対象範囲を含むファイルのフルパス名を取得します。つまり、シート名取得が目的の場合は、対象範囲はどのセルでも構いません。
LEN関数
LEN(CELL("filename",Sheet1!A1))
LEN(文字列)関数は、文字列の文字数を返します。この場合は、Sheet1 を含むファイルのフルパス名の文字数を返します。
FIND関数
FIND("]",CELL("filename",Sheet1!A1))
FIND(検索文字列, 対象, 開始位置)関数は、検索文字列を他の文字列の中で検索し、その検索文字列が最初に現れる位置を左端から数えた位置を番号で返します。
CELL関数で取得したファイルのフルパス名にはブック名が含まれ、ブック名は[]で括られているので、FIND関数でシート名までのブック名を含む文字数を数えます。
RIGHT関数
=RIGHT(CELL("filename",Sheet1!A1),LEN(CELL("filename",Sheet1!A1))-FIND("]",CELL("filename",Sheet1!A1)))
RIGHT(文字列, 文字数)関数は、文字列の末尾（右端）から指定された文字数の文字を返しますので、
フルパス名の全文字数－シート名までのブック名を含む文字数＝シート名の文字数
となり、シート名を取得することになります。
以上、ご参考になれば幸いです。
コメント