Help us understand the problem. What is going on with this article?

[EXCEL]名前定義にはブックとシートの指定があって取り出し方によって値が変わることがある。

More than 3 years have passed since last update.

VBAでセルから値を取り出すときには名前定義が便利でよく使わせていただいております。
ただ、名前定義には落とし穴がいくつかあるので知っている範囲でメモしておきます。

まず名前定義とは

Microsoft 数式で名前を定義し使用する
簡単に言えば、セルに変数名をつけるやつです。
いつもは「=A1」みたいにセル番地を指定していますが、名前を付けると「=タイトル」みたいな書き方もできるというもんです。
ライトユーザにはそれほど恩恵はありませんが、VBAを使う方にとっては重宝します。

sample
'名前定義使わない書き方
msgbox Range("A1").value

'名前定義使う書き方
msgbox Range("empNo").value

何を取得しているか明確になることも大きいですが、一番の効果は「列や行を追加・削除してもコードの修正が不要」ではないでしょうか。
A1と書いてしまうと列を増やしたとたんにB1とかに毎回書き換えないといけないですよね。大変です。
(シート保護や、「行/列を増やしたり減らしたりしないでください」という運用回避をしているファイルを何度か見かけたことはあります。)

VBAでの使い方

今回は参照メインで書きます。
名前定義で参照する方法は主に2つあります。

sample
'取り出し方1: Rangeを使う
msgbox Range("hoge").value

'取り出し方2: Namesプロパティを使う
msgbox Names("hoge").RefersToRange.Value

ただ、この取り出し方ですが実はいろいろと罠が仕込まれています。
ケースによっては思いもよらないものを持ってきます。

例を紹介する前に前提知識が必要です。

[前提]「ブック」の名前定義と「シート」の名前定義がある

名前の管理で名前定義をつける時に「範囲」という項目があります。
M.png

ここでブックとシート名を選択するのですがこの指定によって参照できないケースが発生します。

「ブック」と「シート」の違いは、私の中では完全に「Public変数とPrivate変数の違い」として捉えています。
「ブック」に指定するとどこからでも参照できますが、「シート」に指定すると厳密に指定しないと使えなくなります。
上記に記述した[取り出し方1]のような記述だと「シート」に指定した名前定義を他のシートから使えなくなります。
厳密に書かないといけません。

※以下、ワークシートと範囲の「シート」が混同しないよう、名前定義の範囲「シート」には鍵かっこをつけて説明します。

範囲が「シート」の名前定義を使う例
'取り出し方3: Worksheetを指定する
msgbox Worksheets("Sheet1").Range("hoge").value

'取り出し方4: Worksheetをアクティブにする
Worksheets("Sheet1").select
msgbox Range("hoge").value

この前提があった上でエクセルの仕様に翻弄されることになります。

名前ボックスから登録すると範囲が「ブック」になる

名前ボックスは↓ここです。
N.png

ここに名前を入れてEnterすると「ブック」の名前定義として登録されます。

名前定義が登録されているワークシートをコピーすると、コピー先の名前定義は範囲が「シート」になる。

Public変数で同じ変数名が使用できないのと同様に、ワークシートをコピーすると名前が重複するわけですからそれを回避するため、コピー先は「シート」の名前定義として登録されます。
これが結構な曲者です。
あるワークシートをコピーして使いまわすなんて結構あるケースですが、コピー元だけ「ブック」として登録されているかもしれません。
VBAの書き方が悪いとユーザの操作の仕方によってはエラーが起こります。

「シート」として登録された名前定義が優先される。

例1:

  • ワークシート「色」のA1にcolorという名前定義(「ブック」扱い)をつけます。値は赤にします。
    H.png

  • ワークシートをコピーして「色(2)」を作ります。A1の値は青にします。
    I.png

    • 色(2)のA1に登録されているcolorは前述したとおり「シート」として登録されます。J.png
  • ワークシート(Sheet3)を挿入します。A1の値は緑にします。
    O.png

この状態での挙動は以下の通りとなります。

挙動
'取り出し方5: 「色」をアクティブにする
Worksheets("色").select
msgbox Range("color").value   '' 赤

'取り出し方6: 「色(2)」をアクティブにする
Worksheets("色(2)").select
msgbox Range("color").value   '' 青

'取り出し方7: 「Sheet3」をアクティブにする
Worksheets("Sheet3").select
msgbox Range("color").value   '' 赤

「シート」の名前定義が登録されている色(2)のワークシートをアクティブにした時のみ青が表示されました。
1つのワークシートに「ブック」と「シート」の名前定義を同時に登録することもできますが、その時は「シート」の値を持ってきます。

Namesプロパティでの参照はよくわからない動きをする。

例2:

  • 例1と同じように3つのワークシートを作成する。
    • 色: A1にブックの名前定義、値は「赤」
    • 色(2): A1にシートの名前定義、値は「青」
    • Sheet3: 名前定義なし、A1の値は「緑」
  • 一番右のワークシートをアクティブにする
  • ワークシートの順番を変更して下記を実行
sample
msgbox Names("color").RefersToRange.Value

結果

順番 結果
色、色(2)、Sheet3
色、Sheet3、色(2)
色(2)、色、Sheet3
色(2)、Sheet3、色
Sheet3、色、色(2)
Sheet3、色(2)、色

どう解釈してよいのやら。
Namesだと「ブック」の名前定義の方が優先されているように見えます。ただ、一番左のワークシートに名前定義があったらそのワークシートの名前を見ている感じもします。
正直、説明できません。

一応、Rangeでの結果も隣に記載して以下に残しておきます。Rangeの方が「アクティブになっているワークシートの名前定義」を参照しているのでまだ素直な気がします。

sample
msgbox Range("color").Value
ワークシートの順番 Names Range
色、色(2)、Sheet3
色、Sheet3、色(2)
色(2)、色、Sheet3
色(2)、Sheet3、色
Sheet3、色、色(2)
Sheet3、色(2)、色

で、どうすりゃいいのさ

名前定義があるワークシートをコピーする運用をする場合は、極力「シート」の名前定義を使用し、参照時には厳密にワークシートを指定してRangeで参照するのがよいのではないでしょうか。(取り出し方3や4参照)
Namesはちょっと予測ができないので個人的には使用を控えたいところです。

そのほか

Namesを使用して参照すると時々名前定義の参照範囲が変わることがありました。
今回の場合ですとA1に定義を入れていましたが、VBAを何度か実行するとA1048576に変わっていました。
原因は不明です。こわいです。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away