Excelで参照のセル値の変化でVBAを実行する

質問

Excelで別シートの値を参照しているセル値の変化でVBA実行イベントを起こすにはどういう方法があるのでしょうか。
例えばSheet1のA１は、Sheet2のA1の最初の4文字を参照にしているとします。この場合、Sheet1のA1の中は『=LEFT(Sheet2!A1,4)』です。
これでSheet1のA１の参照結果が変化すればVBAが実行される方法のアドバイスを頂きたいです。

調べて見たらSheet3を作業シートにして、Sheet3の方でSheet1のA１を参照にしてCalculate（シートが再計算された時に発生）イベントで実行するという一つの手を見つけましたが、他にもスマートな良い方法があるのか気になったので質問させてもらいました。よろしくお願いします。

回答No.1

FEX2053 · ベストアンサー

発想だけ。

別に=LEFT()の結果がどうこうしたときに動く、と考えずに、Sheet2の
中身が変化したときに動く WorkSheet_Changeでコードを書いて

１．コードの最後に、Left(A1,4)の内容をPublic変数で保持しておく
２．コードの最初で、保持したPublic変数とLeft(A1,4)を比較する
３．比較した結果同じならExit Subで処理を終わり、違っていれば
　処理を継続する
４．起動時に問題ないよう、Public変数には適当な初期値を与えておく

こんな感じで大丈夫なんじゃ？

要は「ワークシートの内容が変わったら起動、ただし、指定した部分
の内容が変わってないなら、処理せず終了」って考え方です。結構
こういう発想って使えますよ。

お礼

なるほど。その発想はなかったです。勉強になります。

回答No.3

keithin

＞Sheet1のA１の参照結果が変化すればVBAが実行される
＞Sheet1のA1の中は『=LEFT(Sheet2!A1,4)』です。

冷静に分析すれば、それは「シート２でＡ１セルを編集した時」にイベントをトリガしたいという意味です。

シート２：
private sub Worksheet_Change(byval Target as excel.range)
if application.intersect(target, range("A1")) is nothing then exit sub
worksheets("Sheet1").range("C1") = "Sheet2!A1 changed"
end sub

御相談でも書かれている通り、calculateイベントでは「このセルが」計算されたという分析はできません。

お礼

丁寧な回答ありがとうございました

回答No.2

Brennan

セルの値が変更されたときに発生するイベントなら、ブックレベルではSheet_changeイベントですが、ご質問の場合はシートごとに別々に判断するでしょうからシートレベルでChangeイベントとして作成した方がいいと思います。

セルA1が変更されたときにイベントを発生させますが、コピー＆ペーストなどにも対処する場合はIntersectメソッドを使います。

Private Sub Worksheet_Change（ByVal Target As Excel.Range）
　　　……'変数の宣言など
　　　If Not Intersect(target,Range("A1")) Is Nothing Then
　　　……'イベントの発生が認められた時の処理
End Sub

お礼

Intersectメソッド勉強になりました。ありがとうございます。