Home » エクセルマクロ・Excel VBAの使い方 » 定義された名前 » Name.RefersToから定義された名前のシート名を取得

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

対象:Excel2007, Excel2010, Excel2013

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

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

Name.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からシート名だけ取り出す」
という検索に対して、そのまま答えるとしたら、以下のようなマクロでしょう。

Sub 名前からシート名を取得_Mid関数_InStr関数()
 Dim addr As String

 addr = Names("名前のサンプル").RefersTo
 MsgBox Mid(addr, 2, InStr(addr, "!") - 2)
End Sub

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を使ってシート名を取得するサンプルマクロ

RefersToプロパティに拘らなければ、以下のようなコードでも、名前の参照先シート名だけを取得できます。
Sub 名前からシート名を取得_RefersToRange_Parent()
 MsgBox _
  Names("名前のサンプル").RefersToRange.Parent.Name
End Sub

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

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

Name.RefersToからシート名だけを取得する

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

[スポンサードリンク]

Home » エクセルマクロ・Excel VBAの使い方 » 定義された名前 » Name.RefersToから定義された名前のシート名を取得

「定義された名前」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.