名前付きセルの利用方法 Excel2013 VBA

質問

ExcelでVBAで設定値を書いたシートを参照する際に、
Shell Worksheets("設定").Range("B1")
と、書くのが見にくいので、名前付き参照で見やすくできないかと試してみたのですが
うまくいきません。
※名前付きセル範囲は手動で作って、"ブック"という範囲扱いになっているようです。

標準モジュールのテスト関数で
Public Sub test()
MsgBox Range("起動Path").Value
End Sub
とすると正しく文字列が表示できるのですが、

同じ事をSheet1モジュールのダブルクリックイベントに入れたらエラーとなります。
※上記のMsgBox文をイベントの途中にコピペしてみました。

ちょっと理由がよくわからないので、どなたかお助けくださいませんでしょうか…

回答No.1

real beatin（@realbeatin） · ベストアンサー

こんにちは。

シンプルに、
　　 MsgBox Evaluate("起動Path").Value
または
　　 MsgBox Application.Range("起動Path").Value
という風に、参照先をExcelアプリケーション経由で取得します。
ブックレベルで定義した名前でしたらば、
Evaluateメソッドとの組み合わせが親和性が高いですね。

> Sheet1モジュール
で
> MsgBox Range("起動Path").Value
がエラーになるのは、
　Sheet1.Range("起動Path")
のように親オブジェクトを省略しないで書けば、
解るのではないでしょうか。
標準モジュールに書いた場合は
　Application.Range("起動Path")
の省略形になりますから、エラーにはなりません。

以上です。

お礼

たびたびの補足を頂きありがとうございました。
お礼を分散させるのも読みにくいと思いましたので、
こちらに書かせて頂きます。

設問に対してコメントをいただけると面はゆいですが、
こちらこそ回答No.1の内容は完璧と思っていたので、
「説明が雑過ぎた」という内省にびっくりしました。

質問者が「わかってないであろう事」に目星を付けた上で、
端的な事例で挙げてみて観測気球として使い、
「理解がここに到達しているか」の確認をしつつ、
質問者は単語を検索すれば、調べるべき周辺知識が
広がるという感じでしたので、某囲碁漫画の指導碁のようで
思わず唸っておりました。

補足の方も、かゆいところに手が届くような内容で、
とても助かりました。
完全につかめたわけではないですが、補足前はわからなかった
Application.Rangeとsheet1.Rangeはなんとなくわかりました。

気持ちとしては長文説明を頂いた回答3を持ち上げたいのですが、
QAとして他の人がわかりやすくなるように、こちらの回答をBAと
させて頂きます。

また何かありましたらよろしくお願いします。
ありがとうございました。

補足

いつもお世話になります。
内容を読んでみて、
「Evaluateって何？ってevalの事か…ってなんでこのeval式で値が出てるの！？」
とか
「Applicationって初めて見た…ってシートもブックも超えてこのレベルからRangeって有効なの！？」
とか
「標準モジュールでの省略Rangeってアクティブシートにつながってるんじゃなかったっけ？なんでApplication？」
とか元々のうろ覚えからの齟齬が激しくて混乱してますが、
試したら何の問題も無く動いてるので正しいんですよねコレ…

なんといいますか、ExcelVBAって少し欲を出して手を入れると途端に
絶壁のような知識の壁にぶち当たってなかなか厳しいですね

回答No.4

real beatin（@realbeatin）

誤解を招きそうなので、訂正します。
No.3、最後から２（３）行め
> ... 、でも ...
これは、
　... 、でも、質問者に対して ...
という意味で書きました。
それにしても私の文章の主述が乱れていることについてはスルーの方向ででお願いします。
重ねて失礼しました。

回答No.3

real beatin（@realbeatin）

回答No.1補足欄への返信です。

> 激しくて混乱してますが、
> 試したら何の問題も無く動いてるので正しいんですよねコレ…
ごもっともな感想だと思います。
私の方でも、説明が雑過ぎたなぁと思っていて、
追加補足を書いてはいたのですが、
ちょっと上げ難い雰囲気みたいなので、投稿ボタン押せませんでした。
今回のテーマ自体、Web上でも情報が少ない、ということもあって、
こちらもすべて自分の言葉で説明するよりないのですが、
細かい部分で説明や用語を間違うかもしれませんし、
重ねて下手な説明だったらゴメンナサイ。
概念的な理解への援けに少しでもなれば、と。
さて。

【Range】
例えばVBEでF2キーを押してオブジェクトブラウザーを表示して、
Rangeを検索すると、Range プロパティが
複数のクラスに在ることを確認できます。
つまり、親オブジェクトを省略した形の
　　Range ...
という記述は、
その記述を書いた場所、実行時の状況によって、
どのクラスのRange プロパティを呼び出すか、
予めVBAで定義されたルールに従って変動します。

【Sheet1 モジュール】（Worksheetのシートモジュール）
（この場合の"Sheet1"は、
　シートタブに表示されている名前（Name）ではなくて、
　VBProject内でのオブジェクト（クラス）名（CodeName）
　を指します。）
に、
　　Range ...
親オブジェクトを省略して、Range プロパティを呼び出すと、
　　Me.Range ...
　　Sheet1.Range ...
Sheet1 クラスのRange プロパティを参照します。
コードを記入したモジュールから見て
一番近くにあるクラス=Sheet1のメンバーを参照することになります。

【標準モジュール】
に
　　Range ...
親オブジェクトを省略して、Range プロパティを呼び出すと、
関連付けされたクラス（オブジェクト）が標準モジュールにはないので、
一番近くにあるクラス=Excel.Applicationのメンバーを参照することになり、
　　Excel.Application.Range ...
になるのですが、この場合の動作結果は、通常、
　　Activesheet.Range ...
になりますので、
　「標準モジュールに親オブジェクトを省略した形でRangeプロパティを呼び出すと、
　　アクティブシートのRangeオブジェクトを返します」
という説明をするのが一般的な慣習になっています。

Sheet1 モジュールで
　　Range("起動Path") ...
を指定すると、
Sheet1に（シートレベルで）定義された名前が
あれば、正常にセル範囲を返しますし。
無ければ、実行時エラーを返します。
仮に、ブックレベルで定義された名前として存在している名前であっても、
Sheet1 クラスに無ければエラー、ということです。

【Evaluate メソッド】
元々はVBAが生まれる前の古いマクロに用意されたものだったと記憶していますが、
Excelメニューの
　[名前ボックス]
　[数式バー]
　[名前の定義] > [参照範囲]
等の参照や演算をメモリ上で再現するようなものです。
[名前ボックス]に
　起動Path
を記入（表示）した状態でEnterキーを押せば、
[起動Path]という名前で定義された名前の参照範囲にジャンプしますが、
これをシミュレートした結果として、
　Evaluate("起動Path") ...
は、Range オブジェクトとしてのセル範囲[起動Path]を返します。
意味合いとしては、
　アクティブなシートにシートレベルで定義された名前の参照範囲、
もしシートレベルに該当する名前が無ければ、
　アクティブなブックにブックレベルで定義された名前の参照範囲
もし、どちらにも該当する名前が無ければ
　エラー
といった感じになります。

【Application.Range】
名前の定義を参照する場合にはEvaluate メソッドとまったく同じ結果になります。
何故、Application.を付けるかというと、
Sheet1で親オブジェクトを省略して、Range プロパティを呼び出すと、
一番近くにあるクラス=Sheet1のメンバーを参照する為、
意図に沿うようにする為には、
親オブジェクトをApplication（実際の挙動としてはActiveSheet）として
正しく指定する必要があるからです。

今回は「Sheet1モジュールのダブルクリックイベント」ということでしたから、
アクティブなブックは自ブックである（アクティブなシートは必ずSheet1である）
という（少なくとも今のVBAでは絶対的な）前提を利用した相対的な参照として、
　　MsgBox Evaluate("起動Path").Value
　　MsgBox Application.Range("起動Path").Value
という例を紹介しています。
[起動Path]という名前が定義されていないブックがアクティブな場合には、
どちらも実行時エラーになりますのて、他の場面で扱う際は注意して下さい。

絶対的な参照としては、
　　MsgBox Workbooks("hoge").Names("起動Path").RefersToRange.Value
のように、ブック＞Namesオブジェクト＞セル参照範囲、
階層を辿る形になります。

補足への返信としては以上です。
うまく整理できなくてすみません。
尚不明な点があれば遠慮なくお訊ねください。

、、、迷ったけど、一言、自分が書いておくべきと思うので、書いておきますね。
私は、
貴方の質問は、今回も、よく整理されていて、解り易い、
と感じています（実際、読んですぐ解答に着手できました）。
起動Pathという名前からしてShell関数を使う目的を理解するのも容易なことですし、
質問の趣旨からしてそれ以上説明する必要もなく、
といって全く目的に触れないでいては混乱を招く可能性があることを考えると、
ピッタリ必要十分だと、私は、思っています。
まぁ、人それぞれでいいのですけれどね、でもケチつけるのは違うと思います。

失礼しました、それではまた。

回答No.2

imogasi

何を聞いているのかよくわからない。
ーー
エクセルＶＢＡで名前を設定して参照するのは、（私が言う「名前」とは、セル範囲に名前を付けるという意味です）
Sub test01()
Names.Add Name:="範囲A", RefersTo:="=$A$2:$C$5"
'Worksheets("Sheet1").Range("範囲A").Select 'Sheet1において
Range("範囲A").Select　’ＡｃｔｉｖｅＳｈｅｅｔにおいて
End Sub
のようにするのが骨子ではないでしょうか。
＞テスト関数で
関数ではありません。関数ならFunction aaa() 　End Function
のようになるはずです。
＞Shell
ここでＳｈｅｌｌを使う意味がわからない。
ーー
Range("起動Path").Value
は１セルだけに、名前を定義したのかな。
複数セルだとＶａｌｕｅプロパティを使う意味が分からない。
Sub test02()
Names.Add Name:="範囲A", RefersTo:="=$A$2:$a$3"
MsgBox Range("範囲A").Value
End Sub
はエラー（Ｍｓｇｂｏｘの関連？）
ーー
Sub test02()
Names.Add Name:="範囲A", RefersTo:="=$A$2"
MsgBox Range("範囲A").Value
End Sub
を実行してみるとＯＫのよう。
ーー
１セルだけの名前定義の場合
Ｓｈｅｅｔ１のイベントで
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Worksheets("Sheet2").Range("範囲A").Value
End Sub
はＯＫ
・イベントを登録しているシートと名前範囲を指定しているシート
・名前定義が、ブックレベルかシートレベルか
などチェックしてみては。
ーー
わたしが無知のおそれもあるので、その際は後免。

補足

すいません、何を書かれているのかよくわかりません…