EXCEL VBAでApplication.waitを使わずに一時停止させたい

質問

マクロの実行中に、その処理を、条件分岐によってある一定時間だけ止めて、また再開させるようなプログラムを作りたいと考えています。
Application.Waitを使えば、可能というとこまではわかったのですが、それだとマクロ停止中にEXCEL上での一切の操作（たとえばスクロール）ができなくなるので、マクロだけ停止させておいて、他の操作はできるようなやり方はありませんでしょうか？
ちなみに、当方の使用OSはXP、EXCELは2007です。VBAは、はじめて取り組む超初心者で、以下のコードもネットやこちらのサイトを探しまくって、ようやくここまでできました。
なお、本マクロで具体的にやりたいことは、楽天証券のRSSというアプリケーションから、EXCELの特定の列に、1行目から順番に、リアルタイムで株価が書き込まれていきます。その株価を監視して、ある一定以上になったら、音で知らせるということをやりたいのです。その際、株価が書き込まれていく間隔は、数秒～数分です。
長々と書きましたが、ご教授いただけると助かります。
＜参考：現在のマクロ＞
Sub Test()
Dim waitTime As Variant
Dim 繰り返し As Long
For 繰り返し = 1 To 10
If Cells(繰り返し, 1).Value >= 10000 Then
Shell "mplay32.exe /play /close C:\WINDOWS\Media\notify.wav"
ElseIf Cells(繰り返し, 1).Value = Empty Then
waitTime = Now + TimeValue("0:00:05")
Application.Wait waitTime
End If
Next 繰り返し
End Sub

回答No.3

Wendy02 · ベストアンサー

こんばんは。

Microsoft のサポートには、そのものズバリの内容がありますが、少し古い内容です。実際、以下のプロパティで、楽天RSSの前バージョン(約２年前）では私は成功していますが、現行ツールでは知りません。RSSは、バージョンがあがりました。

http://support.microsoft.com/support/excel/content/onevent/onevent.asp?SD=gn&LN=ja&gssnb=1#ondata

それから、外部ツールを使って、音を鳴らすのはあまり賛成しません。一応、書いておきますが、本来は、内部コマンドのBeep やパターンの色付けでよいと思います。

1番目の場合は、最初に、Auto_Open を実行してください。一旦、ファイルを閉じて、再び開いても自動的に設定されています。

なお、Microsoft サポートの注にも書かれていますが、DDEやOLEではない場合に、ハンドラーが取れないこともあります。その場合は、OnEntry や　Change イベントを使用してください、と書かれています。つまり、１番のマクロは、データがインポートされたときにのみ、起動するマクロです。ともかく、最初のマクロだけでも試してみてください。まだ、そういう古い方式が可能なのか、こちらでも知りたいのです。

-----------------------------------------
'標準モジュール
'Option Explicit

Private Declare Function mciSendString Lib "winmm.dll" Alias "mciSendStringA" _
(ByVal lpstrCommand As String, ByVal lpstrReturnString As String, _
ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long

'一旦、閉じて開くか、最初に実行してください。
Sub Auto_Open()
　'実際のシート名を入れてください。
　Worksheets("Sheet1").OnData = "Test1"
End Sub

Sub Test1()
　　Dim i As Long
　　Dim rt As Long
　　Static j As Long
　　Const sSOUND As String = "C:\WINDOWS\Media\notify.wav"

　　i = Cells(Rows.Count, 1).End(xlUp).Row
　　If i <> j Then
　　　　If Cells(i, 1).End(xlUp).Value > 10000 Then
　　　　　　rt = mciSendString("Play " & sSOUND, "", 0, 0)
　　　　　 'Beep　'ビープ
　　　　　 'Cells(Rows.Count, 1).End(xlUp).Interior.ColorIndex = 34 '色づけ
　　　　End If
　　End If
　　j = i
End Sub

-------------------------------------

OnTime メソッドを使う場合は、LastTime を数秒の間の設定をしたほうがよいです。LastTime を入れないと、待機状態の後に、立て続けに、マクロの起動をしてしまい意味のないものになってしまいます。また、行数が単にインクリースしても、そこにデータがなければ、意味のないチェックになってしまいますから、必ず、実際に存在するデータに連動していなければなりません。また、データが増えなければ、チェックはしません。

以下の場合は、ワークシートの作業中とマクロのデータチェックがぶつかった場合は、作業のほうが必ず優先しますが、その待機状態は、数秒間しかありません。その時に無視したデータは、次のマクロで必ずフォローするように出来ています。なお、色づけとBeep という方式になっていますから、色づけが嫌いなら、コメントブロック(')を入れてください。

一列の最後尾に、文字「Q(大文字・小文字共通）」を入れれば、それで終了します。

------------------------------------------
'標準モジュール
'Option Explicit

Sub TimerMacro()
　　Dim i As Long '行
　　Dim n As Long
　　Dim myTime As Date
Static j As Long
　　'----------------------------------
　　'チェックする値を以下に入れてください。
　　Const MYLIMIT As Long = 10000
　　'----------------------------------
　　i = Cells(Rows.Count, 1).End(xlUp).Row
　　'終了は、Q を入れる
　　If StrComp(Cells(i, 1).Value, "Q", 1) = 0 Then
　　　MsgBox "終了しました。", 64
　　　Exit Sub
　　End If
　　If i > j And j > 0 Then
　　　　For n = j To i
　　　　　　'上限を超える場合
　　　　　　If Cells(n, 1).Value > MYLIMIT Then
　　　　　　　　Cells(n, 1).Interior.ColorIndex = 34 '水色
　　　　　　　　Beep '音
　　　　　　End If
　　　　Next n
　　ElseIf j = 0 Then
　　　　　　　If Cells(i, 1).Value > MYLIMIT Then
　　　　　　　　Cells(i, 1).Interior.ColorIndex = 34 '水色
　　　　　　　　Beep '音
　　　　　　　 End If
　　End If
　　j = i
　　myTime = Now + TimeSerial(0, 0, 5)　'5秒
　　Application.OnTime myTime, "TimerMacro", myTime + TimeSerial(0, 0, 2) '待機２秒
End Sub
----------------------------------------

お礼

Wendy02様

ご丁寧なアドバイス、ありがとうございます。
Wendy02様も以前同様なことを、楽天RSSでなされていたのですね。

さて、頂いたアドバイスをもとに、RSSにはつながずテスト環境で試そうと試みたのですが、この場合はDDEではないため、OnEntryやChangeイベントを使用とのことで、恥ずかしながら、まだそこまでの知識が不足しており、上手くテストできておりません。
幸い週末ですので、VBAの学習しつつ、頂きましたサンプルマクロをテストさせて頂きます。うまく進めば、楽天RSSが使用できる月曜以降に本番環境でテストしてみますので、またその結果をご報告させていただきたいと存じます。

取り急ぎお礼まで。
ありがとうございました。

回答No.2

fujillin

ユーザに一旦制御を返すということのようなので、マクロを一度中断させる（終了させる）ようなコードにしておく必要があります。一定時間経過後に（続きを）実行させれば可能です。

ごく簡単な、サンプルを・・
5秒おきにA1セルの値を1増加します。（中断するには、A1セルに文字を入れてエラーを起こさせるか、Ctr+Breakなど）

マクロを一度抜け出しますので、実際の計算では（もっと複雑でしょうから）、その間にセルの内容が変わったり（ユーザが変える）することも考慮して、また、独立で実行可能なマクロを作成する必要がありますので、ご注意ください。

Sub sample()
Cells(1, 1) = Cells(1, 1) + 1
Application.OnTime Now + TimeValue("00:00:05"), "ThisWorkbook.sample"
End Sub

お礼

fujillin様、

早速のアドバイスありがとうございます。
このやり方ですと、私の場合は条件式以下に、
Application.OnTime～
以下を挿入するということだと理解いたしました（違ってますでしょうか？）。
早速試してみます。
誠にありがとうございました。

回答No.1

cj_mover

こんにちは
他にもありますが、比較的簡単な方法のサンプルです。
お使いのアプリケーションとの兼ね合いでお望み通り機能するか、
私にはわかりませんので、試しに、ということで。

Sub TEST()
Dim tm As Single
tm = Timer() + 5
Do
DoEvents
Loop While Timer() < tm
MsgBox "５秒経過"
End Sub

お礼

cj_mover様、

早速のアドバイスありがとうございました。
助かりました。
ただ、このマクロを組み込んで実行したところ、メッセージボックスで毎回ボタンを押さないと、マクロはずっと止まったままになるのですね。
不在（監視していない）の時は、自動的にメッセージボックスが閉じて、マクロが再開されるような方法を、ネットで調べてみます。
今回はありがとうございました。