ExcelマクロVBA入門
第92回.名前定義(Names)

ExcelマクロVBAの基本と応用、エクセルVBAの初級・初心者向け解説
最終更新日:2019-10-27

第92回.名前定義(Names)


名前定義をマクロVBAで扱う場合の解説になります、
名前定義は、複数セル範囲や単一セルに対して名前を付けることで、
そのセル範囲を参照する時に名前で参照できるようにするものです。
名前で参照できることで、セル位置(行位置、列位置)を固定値で指定しなくて済むようになります。


名前定義の作成は、
WorkBookオブジェクトまたはWorkSheetオブジェクトのNamesコレクションにNameオブジェクトを追加することで行います。
・Namesコレクション
・Nameオブジェクト
または、
RangeオブジェクトのNameプロパティに値を設定することでも名前定義することができます。
順に詳細仕様をみてみましょう。

Namesコレクション

Namesコレクションのプロパティ、メソッド一覧

名前 説明
プロパティ Application 対象となるオブジェクトが指定されない場合は、Excel アプリケーション (Application オブジェクト) を返します。
対象となるオブジェクトが指定された場合は、指定されたオブジェクトを作成した Application オブジェクトを返します。
OLE オートメーションを使っていて、オブジェクトのアプリケーションにアクセスするときなどに、このプロパティを使います。値の取得のみ可能です。
Count コレクションに含まれるオブジェクトの数を表す長整数型 (Long) の値を返します。
Creator 現在のオブジェクトが作成されたアプリケーションを示す 32 ビットの整数を取得します。
値の取得のみ可能です。長整数型 (Long) の値を使用します。
Parent 指定されたオブジェクトの親オブジェクトを取得します。
値の取得のみ可能です。
メソッド Add セル範囲の新しい名前を定義します。
追加されたNameオブジェクトが返されます。
Item Names コレクションから、単一の Name オブジェクトを返します。

名前定義を追加するには、Addメソッドを使用します。

Addメソッド
式.Add(Name, RefersTo, Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1, RefersToR1C1Local)
式には、
WorkBookオブジェクト
WorkSheetオブジェクト

これらが指定できます。
戻り値はNameオブジェクトになります。



Name NameLocal パラメーターが指定されていない場合は、名前として使用する文字列を英語で指定します。
名前には、スペースを含めることはできません。また、セル参照として書式を設定することもできません。
RefersTo RefersToLocal、RefersToR1C1、および RefersToR1C1Local パラメーターが指定されていない場合は、A1 形式の表記法を使用して英語で名前の参照先を指定します。
参照が存在しない場合は、Nothing が返されます。
Visible 名前を表示する場合は True を指定します。名前を非表示にする場合は False を指定します。非表示の名前は、[名前の定義]、[名前の貼り付け]、および [ジャンプ] ダイアログ ボックスに表示されません。既定値は True です。
MacroType マクロの種類を、次のいずれかの値で指定します。
1 - ユーザー定義関数 (Function プロシージャ)
2 - マクロ (Sub プロシージャ)
3 または省略 - なし (定義した名前はユーザー定義関数またはマクロを参照しません)
ShortcutKey マクロのショートカット キーを指定します。"z" または "Z" のように、アルファベットの 1 文字を指定します。コマンド マクロに対してのみ適用されます。
Category MacroType 引数が 1 または 2 のときに、名前が参照するマクロや関数の分類を指定します。これは、関数ウィザードで使われる分類です。既存の分類では 1 から順に決まっている番号、または英語で分類名を指定できます。また、既存の分類以外の分類名を指定すると、自動的に新しい分類として追加されます。
NameLocal Name パラメーターが指定されていない場合は、名前として使用するローカライズされた文字列を指定します。名前には、スペースを含めることはできません。また、セル参照として書式を設定することもできません。
RefersToLocal RefersTo、RefersToR1C1、および RefersToR1C1Local パラメーターが指定されていない場合は、A1 形式の表記法を使用してローカライズされた文字列で名前の参照先を指定します。
CategoryLocal Category パラメーターが指定されていない場合は、ユーザー設定関数の分類を示すローカライズされた文字列を指定します。
RefersToR1C1 RefersTo、RefersToLocal、および RefersToR1C1Local パラメーターが指定されていない場合は、R1C1 形式の表記法を使用して英語で名前の参照先を指定します。
RefersToR1C1Local RefersTo、RefersToLocal、および RefersToR1C1 パラメーターが指定されていない場合は、R1C1 形式の表記法を使用してローカライズされた文字列で名前の参照先を指定します。

Namesコレクション使用例.
ActiveWorkbook.Names.Add Name:="test", RefersToLocal:="=Sheet1!$A$1:$B$2"

ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1:R2C2"

どちらも、A1~B2セル範囲に、"test"でブック範囲の名前定義を追加しています。
注意したいのは、シート名を含めて指定している点です。
指定しなければ、アクティブシートになりますが、
VBAを見てもどのシートに設定しているか分からなくなってしまいますので、
必ずシート指定するようにして下さい。

シート範囲の名前定義
Worksheets("シート名").Names.Add Name:="test", RefersToLocal:="=Sheet1!$A$1:$B$2"

このように記述すると、2007以降ではシート範囲の名前定義を追加できます。

Nameオブジェクト

Nameオブジェクトのプロパティ、メソッド一覧

名前 説明
プロパティ Application 対象となるオブジェクトが指定されない場合は、Excel アプリケーション (Application オブジェクト) を返します。
対象となるオブジェクトが指定された場合は、指定されたオブジェクトを作成した Application オブジェクトを返します。
OLE オートメーションを使っていて、オブジェクトのアプリケーションにアクセスするときなどに、このプロパティを使います。値の取得のみ可能です。
Category 指定された名前がユーザー定義の関数やマクロ (マクロ シートの関数マクロやコマンド マクロ) を参照するように定義されている場合は、その分類名をコード記述時の言語の文字列として設定します。
値の取得および設定が可能です。文字列型 (String) の値を使用します。
CategoryLocal 指定された名前がユーザー定義の関数やマクロ (マクロ シートの関数マクロやコマンド マクロ) を参照するように定義されている場合は、その分類名をコード実行時の言語の文字列として設定します。
値の取得および設定が可能です。
文字列型 (String) の値を使用します。
Comment 名前に関連付けられたコメントを設定します。値の取得および設定が可能です。
文字列型 (String) の値を使用します。
Creator 現在のオブジェクトが作成されたアプリケーションを示す 32 ビットの整数を取得します。
値の取得のみ可能です。長整数型 (Long) の値を使用します。
Index 類似するオブジェクトのコレクション内でのオブジェクトのインデックス番号を表す長整数型 (Long) の値を返します。
MacroType 指定した名前が何かを示す定数を設定します。値の取得および設定が可能です。
XlXLMMacroType クラスの定数を使用します。
Name オブジェクトの名前を表す文字列型 (String) の値を取得、または設定します。
NameLocal コード実行時の言語でオブジェクトの名前を設定します。値の取得および設定が可能です。
文字列型 (String) の値を使用します。
Parent 指定されたオブジェクトの親オブジェクトを取得します。
値の取得のみ可能です。
RefersTo 名前の参照先を示す数式を文字列として設定します。
数式は等号 (=) で始まり、A1 形式で、コード記述時の言語で表します。
値の取得および設定が可能です。文字列型 (String) の値を使用します。
RefersToLocal 指定された名前が参照している数式 (セル範囲) を設定します。
数式は等号 (=) で始まり、A1 形式で、コード実行時の言語の文字列です。
値の取得および設定が可能です。文字列型 (String) の値を使用します。
RefersToR1C1 指定された名前が参照している数式 (セル範囲) を設定します。
数式は等号 (=) で始まり、R1C1 形式で、コード記述時の言語の文字列です。
値の取得および設定が可能です。
文字列型 (String) の値を使用します。
RefersToR1C1Local 指定された名前が参照している数式 (セル範囲) を設定します。
数式は等号 (=) で始まり、R1C1 形式で、コード実行時の言語の文字列です。
値の取得および設定が可能です。文字列型 (String) の値を使用します。
RefersToRange Name オブジェクトの参照先の Range オブジェクトを取得します。
値の取得のみ可能です。
ShortcutKey Excel 4.0 のユーザー定義マクロ コマンドとして定義されている名前にショートカット キーを設定します。
値の取得および設定が可能です。
文字列型 (String) の値を使用します。
ValidWorkbookParameter 指定された Name オブジェクトが有効なブック パラメーターである場合、True を返します。
値の取得のみ可能です。ブール型 (Boolen) の値を使用します。
Value 名前の参照先を示す数式表す文字列型 (String) の値を取得、または設定します。
Visible オブジェクトを表示するかどうかを表すブール型 (Boolean) の値を設定します。
値の取得および設定が可能です。
WorkbookParameter 指定された Name オブジェクトをブック パラメーターとして取得または設定します。
値の取得および設定が可能です。
メソッド Delete オブジェクトを削除します。

メソッドは、Deleteメソッドだけになります。

ブック範囲の名前定義の削除
ActiveWorkbook.Names(インデックス).Delete

または、



ActiveWorkbook.Names("名前").Delete

シート範囲の名前定義の削除
Worksheets("Sheet1").Names(インデックス).Delete

または、

Worksheets("Sheet1").Names("名前").Delete

プロパティの使い方や、いろいろな削除方法については、以下のページでサンプルVBAを掲載しています。
名前定義の一覧と削除(Name)
名前定義は使い方によっては、とても便利な機能ですが、長く使っているブックでは、とても多くの名前定義が入ってしまっていたり、参照エラーを起こしている名前定義が多数あったりと、管理に困る場合も多々出てきます。これらが発生する原因としては、ブック間のシートコピーで増えていってしまったり、シートおよびセルの削除によって参照エラーになったままにしておくことで、

RangeオブジェクトのNameプロパティ

RangeオブジェクトのNameプロパティに名前を入れることで、
そのRange範囲に名前を定義することができます。

Range("A2:B10").Name = "test"

A2:B10の範囲に、"test"というブック範囲の名前定義をします。
既に、指定した名前定義が存在する場合は、後から実行した名前定義で上書きされます。
つまり、上記の後に、

Range("E2:G10").Name = "test"

これを実行すれば、名前"test"は、E2:G10の範囲に変更されます。
ただし、この書き方ではブック範囲の名前定義しか作成できません。

名前定義を使ったRangeの書き方

ブック範囲の名前定義
Range("名前1") = 123
※Application.Rangeとしても同じです。

このようにシートを指定せずにRangeだけで記述ししても正しく名前定義のセル範囲にアクセスできます。
ただし、
同じ名称のシート範囲の名前定義がアクティブシートに存在している場合は、アクティブシートの名前定義になります。

Worksheets(1).Range("名前1") = 123

このようにシート指定したRangeの場合は、指定したシートに名前定義が無ければエラーになります。
また、
同じ名称のシート範囲の名前定義が当該シートに存在している場合は、シート範囲の名前定義が優先されます。

シート範囲の名前定義
Worksheets(1).Range("名前1") = 123

このように必ずRangeにシート指定してください。
シート範囲の名前定義は使い方によっては便利ですが、
少なくとも、どのシートかがはっきりわかる状態になっていないとVBAを書く際には不便に感じることもあるでしよう。

Dim s As String
s = ActiveWorkbook.Names("名前1").RefersTo
s = Left(s, InStr(s, "!") - 1)
s = Mid(s, 2)

このVBAは、シート範囲の名前定義"名前1"がどのシートかを取得しています。
s = "=Sheet1!$A$2:$B$10"
s = "=Sheet1"
s = "Sheet1"
変数sに最後に入っている文字列がシート名になります。

シートコピー時は名前定義に注意

名前定義は、シートコピーすると増えていきます。
名前定義のセルがあるシートをコピーすると、
同じ名前で、コピー先のシート指定の名前定義として作成されます。
コピー元の名前定義が、ブック指定でもシート指定でも、コピー先ではシート指定の名前定義として作成されます。

そして、名前定義が増えすぎると管理が大変になってきます。
シートをたびたびコピーする場合は、名前定義の使い方については良く検討したほうが良いでしょう。

また、ブック間でシートコピーすると、以下のようなメッセージが表示される場合があります。
「移動またはコピーしようとしている数式またはシートには、移動またはコピー先のワークシートに既にある名前・・・」
これは、メッセージとおり同じ名前定義があり、かつ一定要件を満たしている場合に表示されるものです。
通常は、コピーまたは移動先の名前を使う「はい」で応答することが多くなるはずですが、
いずれにしても、コピー後には名前定義がどうなっているか、必ず確認するようにしてください。

名前定義の実践例

名前定義の一覧と削除(Name)
名前定義は使い方によっては、とても便利な機能ですが、長く使っているブックでは、とても多くの名前定義が入ってしまっていたり、参照エラーを起こしている名前定義が多数あったりと、管理に困る場合も多々出てきます。これらが発生する原因としては、ブック間のシートコピーで増えていってしまったり、シートおよびセルの削除によって参照エラーになったままにしておくことで、

指定セルに名前定義されているか判定する
名前定義は、VBAでは、セル位置の特定において重要な役割を持ちます、あるセルが名前定義されているか判定するVBAになります。ここでは、名前定義されている場合は、その名前定義を削除するVBAサンプルとしています。指定セル範囲が何らかの名前定義に含まれているか Subsample1(rngAsRange) DimnmAsName ForEachnmInNam…

名前定義は、
マクロVBAでセル位置を特定する場合に重要な役割を持ちます。
積極的に活用してみてください。



同じテーマ「マクロVBA入門」の記事

第89回.オートフィルタ(AutoFilter)
第90回.フィルタオプションの設定(AdvancedFilter)
第91回.条件付き書式(FormatCondition)
第92回.名前定義(Names)
第93回.ピボットテーブル(PivotTable)
第94回.コメント(Comment)
第95回.ハイパーリンク(Hyperlink)
第96回.グラフ(Chart)
第97回.図形オートシェイプ(Shape)
第98回.Findメソッド(Find,FindNext,FindPrevious)
第99回.Replaceメソッド(置換)


新着記事NEW ・・・新着記事一覧を見る

VBAにおける変数のメモリアドレスについて|VBA技術解説(11月8日)
空文字列の扱い方と処理速度について(""とvbNullString)|VBA技術解説(1月7日)
Errオブジェクトとユーザー定義エラー|VBA入門(11月5日)
シングルクォートの削除とコピー(PrefixCharacter)|VBA技術解説(11月4日)
ユーザー定義型の制限とクラスとの使い分け|VBA技術解説(11月3日)
クリップボードに2次元配列を作成してシートに貼り付ける|VBA技術解説(11月1日)
VBAクラスを使ったイベント作成(Event,RaiseEvent,WithEvents)|VBA技術解説(10月31日)
VBAクラスのAttributeについて(既定メンバーとFor Each)|VBA技術解説(10月19日)
VBAの用語について:ステートメントとは|VBA技術解説(10月16日)
VBAのマルチステートメント(複数のステートメントを同じ行に)|VBA技術解説(10月14日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.セルに文字を入れるとは(Range,Value)|VBA入門



  • >
  • >
  • >
  • 名前定義(Names)

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。




    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ