可変するセルの値をMsgBoxで表示させる方法

質問

ご質問させていただきます。

Excel2007をwindows7で使用しております。
ソフト・OS共に64ビットです。

Q4～Q3723までDDEで更新されるリアルタイムデータが入っています。
リアルタイムデータは１ケタから７ケタの数字です。

R4～R3723までは、R4の=IF(AND(O4="BUY",Q4>H4),"BUY"&J4,IF(AND(P4="SELL",Q4<I4),"SELL"&J4,""))をオートフィルでR3723まで貼り付けてあります。
J列には異なる4ケタの数字が事前に入力されていて、R列のIF関数の条件を満たすと、例えば"BUY1301"とか"SELL7203"のように表示されます。

やりたいことは、DDEで更新されるQ列のリアルタイムデータの変化をうけてR列のIF関数が条件を満たして"BUY1301"とか"SELL7203"と表示された瞬間に、その文字（"BUY1301"とか"SELL7203"）をMsgBoxを使って表示させたいのです。
しかも、MsgBoxの表示と同時にビープ音で注意喚起もしたいのです。

ちなみに、R列のIF関数の条件判定が3720行もありますが、Excelを一日動かしても、条件を満たすのはせいぜい10件程度なので、MsgBoxが表示されたことによってOKをクリックするまでVBAが止まってしまっても問題はありません。

どなたか教えていただけないでしょうか？
よろしくお願いいたします。

回答No.3

nishi6 · ベストアンサー

DDEで更新されるのはQ列で、変更が知りたいR列は算式になっているので、Worksheet_ChangeイベントではR列の変更はつかめないはずです。

ここは、DDEでの更新前後でR列の内容をつかんで比較するのでしょうか。4000件程度で該当件数が少なければ時間的にも問題ないでしょう。

DDEでの更新がどのようにして行われているか分からないので、「DDEでの更新」はブラックボックスとして参考にコードを書いてみました。ほとんど何もしていません。こんな解答でいい？

VBAのメッセージボックスを使っているので、複数のメッセージを出す場合、１件目で待ちになります。この辺りはご容赦を。

Sub Syougou()
　　Dim OldData As Variant　　 '更新前データ
　　Dim NewData As Variant　　 '更新後データ
　　Dim r As Integer　　　　　　'行カウンタ

　　'更新前データを退避
　　OldData = Range("R4:R3723")

　　'**************************************
　　'**************************************

　　'処理　DDEでの更新のつもり
　　'Range("Q4") = 800
　　'Range("Q18") = 1300

　　'**************************************
　　'**************************************

　　'更新後データを取得
　　NewData = Range("R4:R3723")

　　'新旧の比較
　　Dim i As Integer　　　　　　'Beepカウンタ
　　Const Num = 3　　　　　　 'Beep回数（お好きに）
　　Dim startTime As Single　　 '開始時刻
　　Const waitSec = 1　　　　 '待ち秒数（お好きに）

　　For r = 1 To UBound(NewData)
　　　　If OldData(r, 1) <> NewData(r, 1) Then
　　　　　　'Beep音
　　　　　　For i = 1 To Num
　　　　　　　　startTime = Timer
　　　　　　　　While Timer < startTime + waitSec
　　　　　　　　　　DoEvents
　　　　　　　　Wend
　　　　　　　　Beep
　　　　　　Next

　　　　　　'メッセージ
　　　　　　MsgBox NewData(r, 1)
　　　　End If
　　Next
End Sub

お礼

nishi6様

ご回答ありがとうございます。
ご指摘の通り、worksheet changeではDDEでのデータ更新を捉える事ができませんでした。
また、ご回答頂きましたVBAは知らないコードばかりで大変勉強になりました。
この度は、ありがとうございました。

回答No.2

satoron666

ちょっと変更

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("R:R")) Is Nothing Then '範囲に含まれて無くない＝含まれてたら！
If Target <> "" Then
MsgBox Target.Address & vbCrLf & "「" & Target.Value & "」に変更されました。"
End If
End If
End Sub

お礼

satoron666様

早いご回答ありがとうございます。
worksheet changeですが、DDEでのデータ更新を捉えることができませんでした。
しかし、ratoron666様のご回答は大変勉強になりました。
この度はありがとうございました。

回答No.1

satoron666

シートのVBAに、

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("R:R")) Is Nothing Then '範囲に含まれて無くない(含まれてたら)
MsgBox Target.Value'変更した部分の内容を表示
End If
End Sub

当方Excel2003ですが、この方法ではどうでしょう？
ビープー音については調べてみます。