VBAサンプル集
指定セルに名前定義されているか判定する

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日:2019-10-27

指定セルに名前定義されているか判定する


名前定義は、マクロVBAでは、セル位置の特定において重要な役割を持ちます、
あるセルが名前定義されているか判定するマクロVBAになります。
ここでは、名前定義されている場合は、その名前定義を削除するVBAサンプルとしています。


指定セル範囲が何らかの名前定義に含まれているか



Sub sample()
  Dim rng As Range
  Set rng = Range("A1")
  Call sample1(rng)
End Sub

Sub sample1(rng As Range)
  Dim nm As Name
  For Each nm In Names
    If Not Intersect(rng, Range(nm.RefersTo)) Then
      nm.Delete
    End If
  Next
End Sub

上記では、指定セルが、何らかの名前定義に含まれている場合、
その名前定義を削除しています。

指定セルは、単一セルである必要はありません。
複数セルであるセル範囲で指定した場合は、
指定されたセル範囲のいずれかのセルが、名前定義に含まれている場合に削除されます。
これで良い場合もあるとは思いますが、ちょっと不都合な場合が多いと思います。

指定セル範囲と同一範囲の名前定義があるか

Sub sample()
  Dim rng As Range
  Set rng = Range("A1")
  Call sample2(rng)
End Sub



Sub sample2(rng As Range)
  Dim nm As Name
  For Each nm In Names
    If rng.Address = nm.RefersToRange.Address Then
      nm.Delete
    End If
  Next
End Sub

sample1との違いは、指定セル範囲が名前定義の参照範囲と完全一致で判定している事だけです。
その使うシーンに合わせて使い分けて下さい。

すでに設定されている名前定義を削除してから再設定

単一セルに対して、名前定義を行うマクロVBAにおいて、
すでに設定されている名前定義を削除してから再設定する場合は、
sample2を応用して、以下のようなマクロVBAコードにすれば良いでしょう。

Sub sample()
  Dim rng As Range
  Set rng = Range("A1")
  Call sample3(rng, "NewName")
End Sub

Sub sample3(rng As Range, strName As String)
  Dim nm As Name
  For Each nm In Names
    If rng.Address = nm.RefersToRange.Address Then
      nm.Delete
    End If
  Next
  rng.Parent.Parent.Names.Add Name:=strName, RefersToLocal:="=" & rng.Address(External:=True)
End Sub

最後の、
rng.Parent.Parent.Names.Add Name:=strName, RefersToLocal:="=" & rng.Address(External:=True)
この部分が、ちょっと分かりづらいかもしれません。
Rangeの親(Parent)はSheetになり、
そのSheetの親(Parent)はBookになります。
つまり、てっとり早く書くなら、
Thisworkbook.Names.Add

Activeworkbook.Add
これで問題ありません。

ここでは、ブックにとらわれずに、引数に指定されたRangeのBookに対して、
名前定義をするようにする為に、
rng.Parent.Parent.Names.Add
このように、ちょっと回りくどい書き方をしました。

単に名前定義の参照範囲を変更したい場合は、

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

このような書き方をすれば、名前定義が無ければ新規に名前定義されますし、
既に名前定義が存在すれば、参照範囲が変更されます。
ただし、この書き方ではブック範囲の名前定義しか作成できません。

マクロVBAでの名前定義について

名前定義は、マクロVBA開発において、
フレキシブルなVBAコードにする為には必須機能となります。
その場合に、大量の名前定義は手操作で追加するのでは大変になりますので、
このように、マクロVBAで簡単に設定できるようにしておくと効率が良くなります。

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

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



同じテーマ「マクロVBAサンプル集」の記事

素数を求めるマクロ
入力規則のリスト入力の妥当性判定
配色を使用したカラー設定を固定カラーに変更
指定セルに名前定義されているか判定する
Excel2003(xls)を2007以降(xlsx,xlsm)に変換する(HasVBProject)
ハイパーリンクからファイルのフルパスを取得する
ボタンのテキスト名のシートへ移動(Application.Caller)
Excelの表をPowerPointへ図として貼り付け
VBAで表やグラフをPowerPointへ貼り付ける
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除
Shift_JISのテキストファイルをUTF-8に一括変換


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

Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)
VBAで表やグラフをPowerPointへ貼り付ける|VBAサンプル集(6月19日)
アクティブシート以外の表示(Window)に関する設定|VBA技術解説(6月17日)
マクロ記録での色のマイナス数値について|エクセル雑感(6月16日)
ツイッター投稿用に文字数と特定文字で区切る|エクセル雑感(6月15日)
日付の謎:IsDateとCDate|エクセル雑感(6月14日)
IFステートメントの判定|エクセル雑感(6月13日)
インクリメンタルサーチの実装|ユーザーフォーム入門(6月12日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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