Name.RefersToから定義された名前のシート名を取得

「エクセルvba referstoからシート名だけ取り出す」
という検索で、このサイト・インストラクターのネタ帳へのアクセスがあることに気づきました。

RefersToというのは、名前の参照先を取得・設定するプロパティです。

例えば、「Sheet1」というワークシートの「A1:G7」セルに「abc」という名前を設定しているときに、
　MsgBox Application.Names("abc").RefersTo
というコードを実行すると、「=Sheet1!$A$1:$G$7」とメッセージボックスに表示されます。

「エクセルvba referstoからシート名だけ取り出す」
というのは、この「=Sheet1!$A$1:$G$7」からシート名「Sheet1」だけを取得する、VBA（Visual Basic for Applications）のコードを探している方による検索です。

関数を使ってシート名を取得するサンプルマクロ

「エクセルvba referstoからシート名だけ取り出す」
という検索に対して、そのまま答えるとしたら、以下のようなマクロでしょう。

NameオブジェクトのRefersToプロパティで取得した名前の、参照先文字列を変数・addrに格納して、
　addr = Names("名前のサンプル").RefersTo

Mid関数とInStr関数を使ってシート名部分だけを取得してメッセージボックスに表示しています。
　MsgBox Mid(addr, 2, InStr(addr, "!") - 2)

InStr関数で「!」が何文字目かを取得して「InStr(addr, "!")」、Mid関数で「2」文字目から、「!」までの文字数から「=」と「!」の2文字分をマイナスした文字数分「Mid(addr, 2, InStr(addr, "!") - 2)」を取得しています。

Mid関数とInStr関数を使う処理は、RefersToプロパティからシート名部分だけを取得するに限らない、VBAで文字列を扱うときの定番の処理です。

RefersToRange・Parentを使ってシート名を取得するサンプルマクロ

RangeオブジェクトのParentプロパティを使うと、セル・セル範囲の親オブジェクトであるWorksheetオブジェクトが取得できます。このRange.Parentプロパティを利用したコードです。

Name.RefersToプロパティなら参照先のアドレスを取得できますが、Name.RefersToRangeプロパティを使うと、参照先のRangeオブジェクトを取得できます。

取得したRangeオブジェクトの親オブジェクトであるWorksheetオブジェクトをParentプロパティで取得して、そのNameプロパティを取得しているのが、「.RefersToRange.Parent.Name」というオブジェクト式です。

• VBAのParentとは