Excelでセルの値を変化させた時にマクロを実行するには？

質問

"A1"のセルに値を入れるとマクロが実行するように組んだのですが、問題が発生しました。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
　　　　　cells(1,2)=5
　　　　　・・・・
End If
End Sub

ここでA1に"5"を入力すると、B1に5と入力されるのですが、Target=5と認識してしまい、A1と同じ値になるので無限ループになってしまいます。

なにか回避策はないでしょうか？
よろしくお願いします。

回答No.1

misatoanna · ベストアンサー

If Target = Range("A1") Then
　　　　cells(1,2)=5

の部分を

If Target.Address <> "$A$1" Then Exit Sub
Cells(1,2) = Target

ではいかが？

お礼

回答ありがとうございます!
.Addressという入力したセルの位置が分かる関数があるのですね。
問題が解決できました。

回答No.9

Wendy02

KenKen_SP様へ

この内容を、質問主さんの趣旨とは外れていないと信じております。
*
ExcelのVBAでは原則的に、ワークシート上のRangeオブジェクトに、アクセスするたびに遅くなります。Intersect のRangeの範囲は、ワークシート上に現実に存在するもので、それを、Targetのオブジェクトと現実のRangeオブジェクトに照らし合わせているわけです。

イベントのTargetのRangeオブジェクトは、イベントに取り込まれた段階で、単独の存在になって扱われています。そこから情報を取り出すほうが速いと考えています。オブジェクトで比較するのと値(Value)で比較するのでは、どちらが速いでしょうか？それを抱える、メモリの大きさ依存していくものだと思います。

もちろん、この時間というのは、おそらく、1000分の1秒に満たないかもしれないレベルの話です。だから、その違いをイベント・プロシージャで見出すことは、困難かもしれません。

＞Target に複数の範囲が設定され、そのなかに処理の対象となるセルが存在するかをRow と Colum でとるためには、 For Each でループさせてチェックする方法しか思い浮かばないのですが、この方法だと調べる範囲が大きくなると処理に時間がかかりますよね?

あくまでも、Targetは、Rangeオブジェクトです。そこには、Rowの行情報と、Columnの列情報がすでに含まれています。つまり、範囲は、行の範囲が、1 ～5 まででしたら、Target.Row < 6 で済みます。また、列が、A～D でしたら、Target.Column < 5 で済みます。しかし、可読性は落ちてしまいます。また、VBAは、インタープリタ言語だということです。だから、ボテボテに書いてしまうと、その分、またロスが出てしまうということです。私も、Intersect は多用しますが、必ずしも、その方法がベストだとは思っていません。

ですから、
With Target
　If Target.Row <6 And .Column <5 Then
　　・
　　・
　End If
End With

　 'cf. If Not Intersect(Target, Range("A1:D5") Is Nothing Then

また、その範囲が、複雑になれば、とても、Row/Column で取っていくことはできなくなるのは言うまでもありませんが、しかし、また別の方法もあります。

もちろん、For Each ～ In ～Next ループ文を使うようなことは、よほど複雑な内容でなければ、通常はしないと思います。

**
＞「セルアクセスなどの処理をする場合は、イベントの発生を停止した方が良い」とした方がベターなのではないでしょうか?

EnableEventsの件は、今回は、Changeイベントで、単に、ひとつのセル指定で、書き込むセルもひとつでしたから、その可能性を考慮しませんでしたが、書き込むセルが、複数あって、イベント・プロシージャにフィードバックするなら、EnableEvents =False は、しなければなりませんね。おっしゃるとおりです。

お礼

私はあまりマクロのスピードとかを考慮して
プログラムを組んだことはないのですが、
(考えてもメモリの領域ぐらい）
今後の為に参考にさせていただきます。

回答No.8

KenKen_SP

#5,6です。またまた、済みません。

#4のお礼欄に書いていただいたとおりです。#4の回答は問題の本質からはずしていました。#7のWendy02さんがコメントされているとおり、

＞If Target = Range("A1") Then
＞この意味は、入力した値が、A1 と同じだったら、次に実行しなさい、という意味ですね。

この点がポイントでした。

Wendy02さんへ

＞Target に複数の範囲を設定する場合は、Intersect(Target, 自分の望む範囲) として使用するのが通例ですが、それは可読性のためで、#3 さんのように、Row と Colum で取ったほうが、全体的に軽くなるはずです

この点ですが、Target に複数の範囲が設定され、そのなかに処理の対象となるセルが存在するかチェックするという使用では、コンパイルされたメソッドである Intersect で取った方が早い気がするのです。

Target に複数の範囲が設定され、そのなかに処理の対象となるセルが存在するかをRow と Colum でとるためには、 For Each でループさせてチェックする方法しか思い浮かばないのですが、この方法だと調べる範囲が大きくなると処理に時間がかかりますよね?

ですから、使い方にもよると思いますが、今回の件では「可読性のため」とは一概に言えないと思うのです。どうでしょうか?

また、「セルがひとつならイベントを停止させる必要ではない」とのことですが、コード中にセルへのアクセスがあれば、イベントは2重発生しますよね?

今回は実質的に問題なし、、ということであって、イベントが2重発生することで例えわずかであっても余分な負荷が発生することは事実ですし、また思わぬトラブルをまねく可能性もありますので、「セルアクセスなどの処理をする場合は、イベントの発生を停止した方が良い」とした方がベターなのではないでしょうか?

回答No.7

Wendy02

もう回答は、ほぼ出揃っているから、あまり直接の回答ではないかもしれませんが、

一応、あまり、Variant のキャスト（つまり、.Valueに自動変換される）を使った方法はしないほうがよい、と聞いていますね。私は、この辺りの詳しい情報は知らないのですが、そう言われてきたのは、ここ数年のようです。

例:
△　Cells(2,1) = 5

○　Cells(2,1).Value = 5

それで、
If Target = Range("A1") Then
この意味は、入力した値が、A1 と同じだったら、次に実行しなさい、という意味ですね。

まず、Targetセルを限定しなくてはなりません。
そのままですと、シート全体が、Target(入力）セルになってしまいます。

Target に複数の範囲を設定する場合は、Intersect(Target, 自分の望む範囲) として使用するのが通例ですが、それは可読性のためで、#3 さんのように、Row と Colum で取ったほうが、全体的に軽くなるはずです。

EnableEvents は、Target（入力）セル が1個の指定の場合は、必要ではありませんが、Target（入力）が、セル複数の範囲の場合は、EnableEventsを入れます。

それは、Target で入力されたセルが、ふたたび、Target(入力）値になるからです。

それと、イベント自体が遅いので、あまり関係がありませんが、Rangeで書いたほうが、多少速度が遅くなります。しかし、可読性を重視するとき、やはり、Range("A1")方式で書きます。

'参考例:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
　　　 Range("B1").Value = 5　
　　　 '........
End If
End Sub

お礼

なるほど～色々勉強になることを教えていただきありがとうございます。

回答No.6

KenKen_SP

済みません、、#5の回答で他の方の名を語ってしまいました。

＞#3です。参考までですが、、、

不注意をお詫びいたします。

回答No.5

KenKen_SP

#3です。参考までですが、、、

Worksheet_Change イベントは Target に複数のセルがセットされることがあります。その場合でも エラーを起こさず A1 セルのみを条件として処理する点、および、A1 セルがクリアされた場合、B1 セルをクリアする点を考慮したサンプルコードです。ご参考までに。

Private Sub Worksheet_Change(ByVal Target As Range)

　　Dim rngCel As Range

　　'Targetが複数セルが複数セルの場合はA1セルのみ処理する
　　Set rngCel = Intersect(Target, Range("A1"))
　　If Not rngCel Is Nothing Then
　　　　Application.EnableEvents = False 'イベント発生を一時的に停止してから
　　　　'A1セルが空ならB1セルをクリア、空でなければ値を書込む
　　　　If IsEmpty(rngCel) Then
　　　　　　Cells(1, 2).ClearContents
　　　　Else
　　　　　　Cells(1, 2).Value = 5
　　　　End If
　　　　Application.EnableEvents = True　'イベント発生を許可する（元に戻す）
　　End If

　　'オブジェクト変数をクリアする
　　Set rngCel = Nothing

End Sub

お礼

ご回答ありがとうございます。

回答No.4

KenKen_SP

こんにちは。

Cells(1, 2) = 5 でセルに値を書込んだときに、再び Worksheet_Change イベントが発生するため、無限ループになっているのです。

Application.EnableEvents でイベントを一時的に停止して下さい。

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target = Range("A1") Then
　　　　Application.EnableEvents = False 'イベント発生を一時的に停止してから
　　　　Cells(1, 2) = 5　'セルに対して処理を行い
　　　　Application.EnableEvents = True　'イベント発生を許可する
　　End If
End Sub

お礼

ご回答ありがとうございます。
この方法で無限ループを止めることが出来ました。

しかし、質問してから気づいたのですが、
例えば既にA1に「3」と入力している状態で、A2に「3」と入力してしまうとマクロが起動してしまう。
また複数セルを選択している状態でDeleteを行うとエラーになってしまう。
という問題が発生してしまいました。

#1さんのようなアドレスで判定する方式だと問題はなかったです。

イベントの停止について勉強になりました。ありがとうございますm(_ _)m

回答No.3

noname#122067

If文のところを以下のように変えてみてはいかがですか？

If (Target.Row = 1) And (Target.Column = 1) Then

お礼

ご回答ありがとうございます。
.Rowは行、.Columnは列を意味しているのですね。
このような方法もあるのですか、勉強になりました。

回答No.2

GreatDragon

試してみたら本当に無限ループのようになってしまいました。

下記のように変更したらどうでしょうか？

Private Sub Worksheet_Change(ByVal Target As Range)
　If Target.Address = Range("A1").Address Then
　　Cells(1, 2) = 5
　End If
End Sub

お礼

ご回答ありがとうございますm(_ _)m