VBAでセル範囲の「名前の定義」の有無を取得

質問

エクセルのセル範囲（結合セル）にいろいろな名前を定義してあります。
名前の定義されたセル範囲を変更した場合、マクロが動くようにしたいのです。
ところが、名前の定義のないセルを変更すると
「実行時エラー1004、アプリケーション定義またはオブジェクトの定義のエラーです」
になってしまいます。
一応、下記のような方法で解決はできましたが、エラーで判断するのではなく、名前の定義の有無を取得して分岐させるのが正しい？やりかたなのではと思います。
ご教示いただけましたら幸いです。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As String
On Error Resume Next
x = Target.Cells(1).Name.Name
On Error GoTo 0
If x = "" Then Exit Sub
Select Case Target.Cells(1).Name.Name
Case "住所"
Range("送付先住所").Value = Target.Value
Case "氏名"
Range("送付先氏名").Value = Target.Value
End Select
End Sub

回答No.1

okormazd · ベストアンサー

エラーで判断するのが正しいやり方ではないなどとは言えないと思います。
それで安全に動けばいいのではないですか。

nameを調べるなら、全部のnameにあたるしかないでしょう。

次のようなコードでどうでしょう。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As String, ad As String, lad As Integer

ad = Target.Address
lad = Len(ad)
For Each nm In ActiveWorkbook.Names
If ad = Right(nm.RefersTo, lad) Then
x = Target.Name
End If
Next

If x = "" Then Exit Sub

Select Case Target.Name.Name
Case "住所"
Range("送付先住所").Value = Target.Value
Case "氏名"
Range("送付先氏名").Value = Target.Value
End Select

End Sub

お礼

さっそくありがとうございます。
全部の「名前の定義」に総当りしなければいけないということは、ある任意のセルに名前が定義されているかどうかを直接取得する方法はないということなのでしょうか。

回答No.4

end-u

>「名前の定義」の有無を取得
するわけではないですが、提示の例題に限って言えば、
Private Sub Worksheet_Change(ByVal Target As Range)
　　Application.EnableEvents = False
　　With Target
　　　　Select Case True
　　　　Case Not Intersect(.Item(1), Range("住所")) Is Nothing
　　　　　　Range("送付先住所").Value = .Value
　　　　Case Not Intersect(.Item(1), Range("氏名")) Is Nothing
　　　　　　Range("送付先氏名").Value = .Value
　　　　End Select
　　End With
　　Application.EnableEvents = True
End Sub
このように、とにかくIntersectメソッド実行してNothing判定という方法もあるかもしれません。
ただ、
Range("住所")とRange("氏名")を含めたセル範囲を同時に選択してクリアしたり、
などはどういう対応になりますか？

そのケースも考慮するなら、
それぞれIntersectメソッドの結果を取得して処理する必要があります。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim r As Range

　　Application.EnableEvents = False
　　Set r = Intersect(Target, Range("住所"))
　　If Not r Is Nothing Then
　　　　Range("送付先住所").Value = r.Value
　　End If
　　Set r = Intersect(Target, Range("氏名"))
　　If Not r Is Nothing Then
　　　　Range("送付先氏名").Value = r.Value
　　End If
　　Application.EnableEvents = True
End Sub

でも提示のケースはあくまで例題なのですよね？
それだけの用途なら関数をセットしておくほうが簡単な気がします。

お礼

ありがとうございます。
勉強になります。

回答No.3

okormazd

#1です。
補足について、
Rangeオブジェクトのメンバーに、nameを持つかどうかの判定できるようなプロパティはないと思います。
したがって、総当たりになります。
このような場合エラーで判断する方法もありかと思いますが、質問の場合はエラーの限定がし難い(1004じゃ広すぎる)ので、適切ではないかもしれません。

お礼

ありがとうございます。

回答No.2

kybo

以下の様な記述でもいいです。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As String
Dim N As Name
For Each N In ActiveWorkbook.Names
If Not Intersect(Target, Range(N.RefersToLocal)) Is Nothing Then
x = N.Name
Exit For
End If
Next N
If x = "" Then Exit Sub
Select Case x
Case "住所"
Range("送付先住所").Value = Target.Value
Case "氏名"
Range("送付先氏名").Value = Target.Value
End Select
End Sub

お礼

ありがとうございます。
この方法も総当りで調べるのですね。
直接、名前の定義の有無を取得する方法はないのですね？