エクセルVBAで他の仕事を止めずに時間を稼ぐ方法

質問

• エクセルVBAを使用してセル式の計算が完了するのを待つためのタイマーが正確に動作しない場合、別の方法を探しています。セル式の計算が完了するまで待ち、次のアクションを実行するための方法を知りたいです。
• 現在、セル式の計算が完了するまで待つためにタイマーを使用していますが、タイマーの動作が不正確であるため、他の効果的な方法を探しています。セルの変化を監視し、計算が完了したら次のジョブを実行する方法についてアドバイスをいただきたいです。
• エクセルVBAのタイマーを使用してセル式の計算が完了するまで待ちたいですが、タイマーの性能に問題があります。セル式の計算が完了するまで正確に待つ方法をご教示いただけないでしょうか。タイマー以外で効果的な待機方法があれば教えてください。

回答No.2

cj_mover

（１／２）
こんにちは。お邪魔します。
すみませんが、長くなるので連投します。

ニーズを正確に理解出来ている訳ではないので、それぞれのパーツの話、、、です。

> で、時間を稼ぎたいのですが、
> 今は、タイマーで
> act時刻 = Now
> ａｃｔ時刻 = DateAdd("n", 2, act時刻 )
> 待ち時間 = DateAdd("s", 20, act時刻 )
> Application.OnTime act時刻 , "ｎｅｘｔ-job", 待ち時間

> このタイマーが不正確（動作したりしなかったり）で　上の式は　2分待ちですが　1分以下だと　100％起動せず、
> 1分40秒以上でどうやら・・・

本題とは関係ないですが、OnTime メソッドの話から。
考え方として、
　×｜ EarliestTime ｜ ★ ｜ LatestTime ｜×
EarliestTime と LatestTime の間★の期間に、【タイミング】が合えば、Procedureは実行されます。
i)「OnTime メソッドを実行したプロシージャ」　を抜け（の処理が終り）、
ii)「OnTime メソッドを実行したプロシージャ、を呼び出した大元を含む実行中のすべてプロシージャ」　を抜け（の処理が終り）、
iii)「他に実行されるべきアプリケーションのタスク」　の処理が済んだ後
この【タイミング】が★の期間内に収まっていれば、Procedureが実行されます。
ですから、EarliestTimeを「早め」に設定し、LatestTime「遅め」に設定し、★の期間を「長く」取った方が、
Procedureは実行され易い、ということになります。
そういう意味では
　Application.OnTime Now, "proc", DateAdd("n", 20, Now)
「今、から、２０分後、まで」みたいに書いておけば、【タイミング】が来次第、Procedureは実行されます。
しかし、今回の課題の意味からすると、引数LatestTimeは指定しないで、
　Application.OnTime Now, "proc"
のようなことがなさりたいのではないでしょうか？
引数LatestTimeの意味、としては、指定した時刻を過ぎたら、Procedureを実行しない、です。
もし、この点で不明瞭なのでしたら、今一度、ＶＢＡのヘルプ等で確認してみて下さい・
Procedureが実行可能になる【タイミング】について補足しますが、
例えば、ActiveXのコマンドボタンで呼び出すような場合、
1: コマンドボタン押下げ
2:　Private Sub CommandButton1_Click()
3:　　　SubProc
8:　End Sub
4:　Sub SubProc()
5:　　　Application.OnTime Now, "proc"
6:　　　時間の掛かる処理
7:　End Sub
9:コマンドボタンの押下げ表示を元に戻す既定の処理
10:OnTime メソッドで設定したProcedureが実行可能になる
11: Sub Proc()
12: 　　処理
13: End Sub
のような順番に処理されることになります。
直接的には、この順番を替えたり、割り込んだりすることは出来ません。
また、上の順番の中で8:9:を4:より前に済ませておく必要があるような場面など
ある程度処理の順番を操作したい場合に、
有効な方法として用意されているのがOnTime メソッドでもあり、
この例では、3:の呼び出し部分をOnTime メソッドに書き換えるような使い方
が、重要度の高いOnTime メソッドの代表的な使用例でもあります。
たぶん、今回の課題は直接的にはOnTime メソッドとは無縁だと思います。
（5:6:の順が逆になるように書くことはあると思いますが、、、。）
ただ、処理の流れを時系列で把握することは重要ですから、
概念提示という意味では、比較的解り易い例だったと思います。
どのタイミングで End Sub を通るのか、ぐらいは把握していないと書けないものも多いですしね。

> シートにデータ送り込むＶＢＡ（15分毎のタイマー）の続きに　
> 計算完了、もしくは、セル式の仕事を止めないで、
> セル式計算完了を　まって、”ｎｅｘｔ-job”　の　仕事をさせたいのですが

> セル式の計算
これって「ワークシート（セル範囲）の再計算」のことでいいしょうか？

> セル式の仕事を止めない時間稼ぎは無いでしょうか
"セルの仕事を止め"る、とは、再計算を中断または終了させる？という意味に読めますけど、
何故そんなことが問題になるのか私には理解できないので教えて頂けませんか。
例えば、セルＡ１の値を変更（追加）するとして、
値を入力し、Enterキーを押しても、
直接・間接にＡ１を参照先に持つワークシート上のすべての再計算が終了するまで、
Ａ１セルは確定しませんよね。
ＶＢＡでも同じで、
　Cells(1, 1) = "hoge"
に追従する再計算が終了するまでは次の行の処理に進めません。
なので、次の行を実行する時には、前の行の処理に連動した再計算は終了しているのでは？
これが普通で、それ以外は特殊、というのが、これまでの私の常識でしたので、
何か自信無くしそうですが、少し補足説明を戴けたら助かります。
Sleep関数を使っていることも気になっているのですが、
再計算を非同期で実行させるような状況なのでしょうか？
私はマルチスレッドの再計算関連に詳しくないので、私が知らない何か、なのでしょうけれども、、、。

不躾ながら、
Application.CalculationState が使える、というのも、私の理解の外ですが、
それなら、Application_AfterCalculate イベントを使って、
ＶＢＡの処理に割り込ませることが有効なケースもあろうかと。
これは本題に近い話だと思いますが、、、。
/// ThisWorkbook モジュール
Option Explicit
Private WithEvents xlApp As Application
Private Sub Workbook_Open()
　　Set xlApp = Application
End Sub
Private Sub xlApp_AfterCalculate()
　　DoEvents
End Sub
///（Workbook_Open()を（が）実行すれ（されれ）ばxlApp_AfterCalculate()が有効に）
仮に、DoEventsだけを処理させていますが、
これは再計算時に、ひとつの参照チェーン毎に計算が終了する度に発生するイベントですので、
"セル式の仕事を止めない時間稼ぎ"という言葉の指す意味合いによっては有効な手段かも、です。
（再計算に掛かる時間が長過ぎて、ＰＣがスリープしちゃうとか？）
実際には、Publicなフラグ変数を用意しておいて、セル値の変更処理の度に
　フラグ=n → セル値変更 → 再計算
　→ _AfterCalculate() → フラグ>0ならば、n番めの再計算終了と判断して 必要な処理 → フラグ=0
のようにしてみれば使い道は色々ありそうですけれど。
　フラグ = n
　Cells(1, 1) = "hoge"
　　↓
　再計算
　　↓　←ここに_AfterCalculate()が割り込みます。
　フラグ = n + 1
　次の処理

（次の投稿に続きます）

参考URL：

http://msdn.microsoft.com/ja-jp/library/office/ff840621(v=office.15).aspx

お礼

恐縮です。
我流でコマンド書いている身としましては、赤面の限りです
さて、
　Application.OnTime Now, "proc", DateAdd("n", 20, Now)
「今、から、２０分後、まで」みたいに書いておけば、【タイミング】が来次第、Procedureは実行されます。
しかし、今回の課題の意味からすると、引数LatestTimeは指定しないで、
　Application.OnTime Now, "proc"
のようなことがなさりたいのではないでしょうか？

当方の理解では、
タイマー指定時間に実行できる環境でなければ、スルーされ、実行されない。その為のLatestTimeで　もう一度この時間にトライされる。よって、トライは2回。
開始時間から、LatestTimeまで　パルス的にトライしてくれるかどうか、調べたのですが、探せませんでした。
よって
Application.OnTime Now, "proc"　パルス的にトライして可能な時間に成ったら・・・
この概念は有りませんでした。これが可能なら、最速の可能な時間に処理が行われることに成り、当方としては、これで願ったり・・・です。

基本なロジックは
アドオンで、設定セル（5個ほど）にネット経由でデータの変更が有るたびに送り込まれます。設定セルだけですから、全て上書きです。
それを、Worksheet_Calculate()（これ以外不可）で変化を捉え、時系列に記録を伸ばします。
早い話、株価ですが、以上はコントロール不可能。まったく、相手任せ。いつ仕事がされるか不明（変化したら・・・ですから）

現状は

朝の起動時から9：00　から　15分ピッチで
反復時刻 = 反復時刻 + インターバル
待ち時間 = DateAdd("s", 20, 反復時刻)
Application.OnTime 反復時刻, "orgdata.15job", 待ち時間
タイマーを予約し、その時間に　集まったデータを15分データとして、集約します。集約の後、シートにデータを　飛ばします。

15job()
次の15分タイマーを予約
2分後に、セル計算後の”評価JOB”をタイマー予約
コピー開始
orgdata.Range("a4:b4").Copy Destination:=fun15.Range("A" & endsisu)
orgdata.Range("c4:f4").Copy Destination:=fun15.Range("d" & endsisu)
fun15.Range("c" & 100 & ":c" & 100).Copy
fun15.Range("c" & endsisu & ":c" & endsisu).PasteSpecial Paste:=xlPasteFormulas
fun15.Range("H" & 100 & ":AZ" & 100).Copy
fun15.Range("H"&endsisu&":AZ" & endsisu).PasteSpecial Paste:=xlPasteFormulas

fun60.Cells(end60, 1) = fun15.Cells(st15, 1)
省略
fun60.Cells(end60, 7) = fun15.Cells(end15, 7)

fun60.Range("c" & 100 & ":c" & 100).Copy
fun60.Range("c"&end60& ":c" & end60).PasteSpecial Paste:=xlPasteFormulas
fun60.Range("H" & 100 & ":Z" & 100).Copy
fun60.Range("H"&end60&":Z" & end60).PasteSpecial Paste:=xlPasteFormulas
15job　end

2分後の　評価JOB
シートfun15の特定の（4個ほど）セル変化をとらえてアクションを起こす、起こさないを決定します。セル変化を単純に　ＩＦ文で　比較するだけです。
その結果がTRUEなら　フォーム　および　ＩＥを起動して、証券会社にアクションが起きます。

ここが、お説のようなら（セル式の計算の完了を待って次のコードを実行する）　待ち時間など考慮せず（2分後のタイマー予約なしで）、15job　end　の続きに、当方は　IFの比較文（タイマー中身）を記述すればいいことに成ります。

タイマー有りの時が、時間を短く予約すると　タイマー不発
90秒でもやや不安定（アドオンのデータ更新が影響か）。2分でどうにか・・・

で、次のテストが　2分後タイマーを止めて
コピーのあと
Do
Sleep (10)
DoEvents
xxx = xxx + 1
If xxx > 10000 Then　’ CalculationState = xlDone使ったことないので、念のため
yy = orgdata.Cells(1000, 14).End(xlUp).Row + 1
orgdata.Cells(yy, 14) = "ループ-out" & Now
Exit Do
End If
If Application.CalculationState = xlDone Then
yy = orgdata.Cells(1000, 14).End(xlUp).Row + 1
orgdata.Cells(yy, 14) = "State = xlDone-out" & Now
しまった、ここに　xxx　の数値を　記録すれば、このループが必要か判明する（来週の話）
Exit Do
End If
Loop

Call　評価JOB

これで、不発は無いようですが
マクロin2014/09/13 1:00:12
マクロcopy-in2014/09/13 1:00:35
マクロfun　copy-in2014/09/13 1:00:41
マクロ　60fun　copy-out2014/09/13 1:00:51
nothing2014/09/13 1:00:52
State = xlDone-out2014/09/13 1:00:53
注文確認-in2014/09/13 1:00:54
注文確認-suru-out2014/09/13 1:00:55
マクロin2014/09/13 1:15:13
マクロcopy-in2014/09/13 1:15:36
マクロfun　copy-in2014/09/13 1:15:41
マクロ　60fun　copy-out2014/09/13 1:15:49
変化
買いから　売り
State = xlDone-out2014/09/13 1:15:52
注文確認-in2014/09/13 1:15:53
2014/09/13 1:16:00返済　doTradeAfterNoon　
2014/09/13 1:17:01新規売り　
2014/09/13 1:17:51返済注文　Morning2　

マクロin2014/09/13 1:00:12　のスタートから（12秒は無視してください）
State = xlDone-out2014/09/13 1:15:52　まで　40秒掛かっている計算。
（それ以降の時間はスルーしてください、ＩＥが絡みますので）

さて、頂いたご提案
xlApp_AfterCalculate()　について、
話がかなり戻るのですが、本来なら、Worksheet_Change(ByVal Target As Range)で　計算式完了後の変化を捉えることが出来るはずなんですが、これも、不発で
むしろ、これの方が　良いかも・・・テストさせていただきます。

時間短縮、あれや、これや、ご教示ありがとうございました。時間がかかりそうですが、検証させていただきたく。　感謝