[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBA特定の範囲に変更あったら実行を複数範囲・条件で』（ハム園）
・B2～C100で変更があったら変更があった行のA列に○マーク
・E2～G100で変更があったら変更があった行のD列に○マーク
※1 指定範囲の複数セルを選択しデリートした場合は実行なし
※2 指定範囲の内容をデリートした場合は実行なし
※3 指定範囲に複数セルコピペの場合は実行
という処理をシート内で行いたいです。
下記が現在のコードですが拙い知識で既存のコードを継ぎ接ぎで作成している為おかしい点ばかりかと思います。
単純に同じ書き方の物を２つ重ねている状態なのでもっと簡略化できるのではないかという問題と
コピペ時は処理が実行できないという問題があります。
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:C100")) Is Nothing Then
Else
If IsArray(Target) Then Exit Sub
If Target.Value = "" Then Exit Sub
Cells(Target.Row, 1) = "○"
End If
If Intersect(Target, Range("E2:G100")) Is Nothing Then
Else
If IsArray(Target) Then Exit Sub
If Target.Value = "" Then Exit Sub
Cells(Target.Row, 4) = "○"
End If
End Sub
何卒ご教示くださいますよう、お願い致します。
＜ 使用 Excel：Excel2013、使用 OS：unknown ＞
If IsArray(Target) Then Exit Sub
＞コピペ時は処理が実行できないという問題があります。
コピペだろうと、値が書き換えられれば動くと思いますので、単純に↑により"複数セル"を一気に書き換えたためじゃないでしょうか？
ステップ実行して確かめてみましたか？
■２
今回は出力先が、A列とD列なので判定するセルから外れており問題は出ませんが、教科書的に考えれば、書き込む前に一時的にイベントを止めるのがセオリーかなとおもいます。
■３
＞単純に同じ書き方の物を２つ重ねている状態なのでもっと簡略化できるのではないか
規則性はありますから可能だとはおもいますが、監視？するセル範囲（の列数）や、出力先の列が異なりますので、かえってわかりづらくなりそうな気がします。
■４
興味本位で聞きますが、一旦○をつけたら、例えばB10:C10がクリアされたとしても、A10には○が入りっぱなしでよいのですか？
（それとも、クリアするときはA列も一緒にクリアするだろうから、マクロ側では""に変更されたときは処理しないだけでよいという考え方ですか？）
■５
複数セルに対応するように直してみました。
Private Sub Worksheet_Change(ByVal Target As Range)
Stop '← ブレークポイントの代わり
Dim MyRNG As Range
If Not Intersect(Target, Range("B2:C100")) Is Nothing Then
Application.EnableEvents = False
For Each MyRNG In Intersect(Target, Range("B2:C100"))
If MyRNG.Value <> "" Then Cells(MyRNG.Row, "A").Value = "○"
Next MyRNG
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("E2:G100")) Is Nothing Then
Application.EnableEvents = False
For Each MyRNG In Intersect(Target, Range("E2:G100"))
If MyRNG.Value <> "" Then Cells(MyRNG.Row, "D").Value = "○"
Next MyRNG
Application.EnableEvents = True
End If
End Sub
（もこな２） 2019/11/28(木) 16:03
もこな２さんのコード見てないけど、こういうの面倒くさいよ。
Ctrl + Enter か、コピペによる変更か判断する必要があるし、 ああ、これは今回必要ないのか？
同じ内容でも、書き変えればWorksheet_Changeは、変更したと反応するし。 （BJ） 2019/11/28(木) 17:15
Dim k As Range
If Intersect([B2:C100], Target) Is Nothing And Intersect([E2:G100], Target) Is Nothing Then Exit Sub
Do
For Each k In Target
If k.Value <> "" Then Exit Do
Next k
Exit Sub
Loop
For Each k In Target
Range("A" & k.Row).Value = "○"
Range("D" & k.Row).Value = "○"
Next k
End Sub
（mm） 2019/11/28(木) 18:09
Next k Exit Sub
逆です？
（ただ、その場合、無条件で Exit Sub を踏むようになりそうな…）
また、
For Each k In Target
Range("A" & k.Row).Value = "○"
Range("D" & k.Row).Value = "○"
Next k
Targetは2～100行目とは限らないので、範囲によっては必要のない行にも書き込むような気がするのと、TargetがB～C列でもD列に、TargetがE～G列でもA列に余分に書き込んでしまいませんか？
スマホで見てるのでテスト出来てませんし、私の見落としであれば余計な一言失礼しました。
（もこな２ ） 2019/11/28(木) 18:54
＞もっと簡略化できるのではないか
Private Sub Worksheet_Change(ByVal Target As Range)
Stop '← ブレークポイントの代わり
Dim MyRNG As Range
If Intersect(Target, Range("B2:C100,E2:G100")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each MyRNG In Intersect(Target, Range("B2:C100,E2:G100"))
If MyRNG.Value <> "" Then
Select Case MyRNG.Column
Case 2 To 3: Cells(MyRNG.Row, "A").Value = "○"
Case 5 To 7: Cells(MyRNG.Row, "D").Value = "○"
End Select
End If
Next MyRNG
Application.EnableEvents = True
End Sub
こんな感じではどうですか？
（もこな２） 2019/11/30(土) 09:20
＞もこな2様
複数セルクリアした際にもマクロが実行されてしまうので複数セルに反応しないようにしておりましたが
コピペの際は複数セルを一気に変更するので困っておりました…
■5で頂いたコードを試してみた所
「Stop '← ブレークポイントの代わり」の部分で止まってしまいましたが(これが■2の「一時的にイベントを止める」に当たる物でしょうか)
これを外してみましたら正に理想通りの動きとなりました。
また、簡略化するのがベストだと先入観で思っておりましたが
■3でもこな2様がおっしゃった通り、管理面での事を考えると無理に簡略化する必要も無いのかもしれないとハッとしました。
目から鱗です。
■4についてはご指摘の通り、A列も一緒にクリアするだろうという理由でマクロでの処理は不要かなぁと思っておりました。
とても助かりました。
アドバイス頂きありがとうございます。
＞mm様
ご回答ありがとうございます。
○の記入は範囲毎に別の場所に記入がしたいのです。
（ハム園） 2019/12/04(水) 16:39
■７
＞コピペの際は複数セルを一気に変更するので困っておりました…
もうおわかりかと思いますが、ChangeイベントはTargetが複数のセルになることがあり得ますので、その対策も考えておいたほうがよいです。
ちなみに、コピペやクリアはもちろんのこと、複数セルを選択した状態で入力して「Ctrl + Enter」で入力確定すると複数セルに一気に書き込むことになりますし、セルを削除してずれることによりセルの値が変わることでも、エクセル君的には"セルの値が書き換えられた"となるので、Targetが複数になります。
■８
＞(これが■2の「一時的にイベントを止める」に当たる物でしょうか)
違います。
そのように聞かれると言うことは、【ブレークポイント】自体が分からないのではないでしょうか？
で、ブレークポイントがわからないということは、【ステップ実行】というわれてもピンと来ていないのではありませんか？
【ステップ実行】【ブレークポイント】がよく分からない場合はこのまま読み進めてください。
（そこは分かってます。とのことであれば「■８－(3)」まで飛ばして下さい
■８－(1)
まず【ステップ実行】について理解する必要がありますのでその説明を。
https://www.239-programing.com/excel-vba/basic/basic023.html
http://plus1excel.web.fc2.com/learning/l301/t405.html
詳細な説明は↑のような解説サイトを参考にしていただくとして、ざっくり言ってしまうと、ステップ実行とは作ったプログラムを1行ずつ動かしてみて、おもったとおりの動きをしているかチェックする行為です。
例えば、当初のコードを整理すると
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:C100")) Is Nothing Then '【01】
'Then節がなにもない
Else '【02】
If IsArray(Target.Value) Then Exit Sub '【03】【04】
If Target.Value = "" Then Exit Sub '【05】【06】
Cells(Target.Row, 1) = "○" '【07】
End If '【08】
If Intersect(Target, Range("E2:G100")) Is Nothing Then '【09】
'Then節がなにもない
Else '【10】
If IsArray(Target.Value) Then Exit Sub '【11】【12】
If Target.Value = "" Then Exit Sub '【13】【14】
Cells(Target.Row, 4) = "○" '【15】
End If '【16】
End Sub '【17】
となります。
この状態で、F4セルに「４」と入力した場合どうなるかをステップ実行で追うと
(０) F4セルが書き換えられたことにより、Changeイベントが発生→Worksheet_Changeが発動！(Targetには"F4セル"が格納される) (１) 【01】は「F4セル」が「"B2:C100"」に含まれないから真（Nothingである）と判定されてThen節へ移行 (２) Then節には何も書いてないから、そのまま【08】に進んで分岐終了
(３) 【09】は「F4セル」が「"E2:G100"」に含まれるから偽（Nothingでない）と判定されて（ElseIFが無いから）Else節へ移行 (４) 【10】はそのままElse節の始まり (５) 【11】は「Target.Value」が配列ではないから偽と判定されてThen節【12】の実行はなし。 (６) 【13】は「Target.Value」が「""」ではないから偽と判定されてThen節【14】の実行はなし。 (７) 【15】でD4セルに「○」と書き込む (８) ★★★ ↑でセルの書き換えが発生!! ★★★ これにより【新たに】Changeイベントが発生
(９) Changeイベントが発生→【実行中のWorksheet_Changeを保留にして】Worksheet_Changeが発動!(Targetには"D4セル"が格納される)
(10) 【01】は「D4セル」が「"B2:C100"」に含まれないから真（Nothingである）と判定されてThen節へ移行 (11) Then節には何も書いてないから、そのまま【08】に進んで分岐終了
(10) 【09】は「D4セル」が「"B2:C100"」に含まれないから真（Nothingである）と判定されてThen節へ移行 (11) Then節には何も書いてないから、そのまま【16】に進んで分岐終了 (12) 【17】で (９)で発動したWorksheet_Changeは終了
(13) 保留にしていた処理に戻って(７)の次から実行を再開 (14) 【16】で分岐終了 (15) 【17】で (０)で発動したWorksheet_Changeが終了して一連の処理が完了
となっていることが確認出来るかとおもいます。
したがって、（■１）で「ステップ実行して確かめてみましたか？」と問いかけたのは、
＞コピペ時は処理が実行できない
と仰っていましたけど、複数セルを貼り付けてTarget.Valueが配列になっているから、正常に処理され「Exit Sub」しているだけではないかとおもったので指摘した次第ですです。
■８－(2)
【ブレークポイント】については、↓を参照
https://www.239-programing.com/excel-vba/basic/basic022.html
http://kabu-macro.com/word/ha-ho/breakpoint.html
「ブレークポイントの代わり」と書いたのはそのままの意味で、隣に行ってブレークポイントを設定してあげることはできないので、ブレークポイントを設定するかわりとしてSTOPステートメントで代用しただけです。
なので、見た目（実行結果）だけ見ているのではなく、【ブレークポイント】を設定して【ステップ実行】をして、1行ずつなにをやっているのかちゃんと確認（理解）してほしいということです。
■８－(3)
イベントを止めているのは↓です。
Application.EnableEvents = False
例えば↓のコードを適当なシートモジュールに書いて、何か入力してみて下さい。
Private Sub Worksheet_Change(ByVal Target As Range)
Stop
Range("A1").Value = Now
MsgBox "変更時間を記録しました"
End Sub
先に書いておきますが終わりません。（メモリがあふれて実行できないとなれば止まりますが・・・）
ステップ実行して追ってみればわかりますが
(1) シートのどこかに入力されて「値が書き換えられる」→Worksheet_Changeが発動 (2) Worksheet_ChangeによりA1セルの「値が書き換えられる」→Worksheet_Changeが発動 (3) Worksheet_ChangeによりA1セルの「値が書き換えられる」→Worksheet_Changeが発動 (4) Worksheet_ChangeによりA1セルの「値が書き換えられる」→Worksheet_Changeが発動 ・ ・ ・
のようになります。これはループしているのではありません。
(1)によりWrksheet_Changeが発動して値を書き換えたことによりWorksheet_Changeが発動
↑を保留して、新しくWrksheet_Changeを実行することにより、値が書き換えられるからWorksheet_Changeが発動
↑を保留して、新しくWrksheet_Changeを実行することにより、値が書き換えられるからWorksheet_Changeが発動
↑を保留して、新しくWrksheet_Changeを実行することにより、値が書き換えられるからWorksheet_Changeが発動
のように無限連鎖に陥っているのです。
実際にこれでは困りますので、【値を書き換える前】にApplication.EnableEvents = False とすることにより一時的にイベントの発生を検知しないようにする必要があります。
（もちろん、Trueに戻さないとマクロが終わった後でもイベント検知がされなくなっちゃいますので、戻すのを忘れないようにしないといけません）
したがって、↓のように直せばOKです
Private Sub Worksheet_Change(ByVal Target As Range)
Stop
Application.EnableEvents = False
Range("A1").Value = Now
MsgBox "変更時間を記録しました"
Application.EnableEvents = True
End Sub
なお、先に「今回は問題ない」といったのは、A列やD列は「Range("B2:C100,E2:G100"))」に含まれず、上記のような無限連鎖には陥らないためです。
また、「4」と入力しているセルに「4」と入力する、ブランクセルをクリアするなどであっても、エクセル君は同じ値に"書き換えられた"と判断しますからChangeイベントが発生します。
一方で、数式で他のシートを参照していて計算の結果値が変わるなどの場合は、数式そのものは書き換えられていないのでChangeイベントは発生しません。
長文になったので一旦ここまで。
（もこな２） 2019/12/06(金) 20:47
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.