エクセルＶＢＡ 他の仕事を止めない時間稼ぎ

質問

早速ですが、
シート1　行　3.000から4000.列　セル式　Ｈ-AM　まで
シート2　行　3.000から4000.列　セル式　Ｈ-W　まで
シート3　行　3.000から4000.列　セル式　Ｈ-AO　まで
このような、BOOKが有ります。
データの追加（各シートのＡ-Ｇ　1行追加）に伴い　シート1から　3まで　セル式の計算が行われます。

で、計算が済んだあと、セルの変化をとらえて、次のアクションを起こすのですが、
セル式が完了するまで、時間を稼ぎたいのですが、
今は、タイマーで
act時刻 = Now
ａｃｔ時刻 = DateAdd("n", 2, act時刻 )
待ち時間 = DateAdd("s", 20, act時刻 )
Application.OnTime act時刻 , "ｎｅｘｔ-job", 待ち時間

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

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

セル式の仕事を止めない時間稼ぎは無いでしょうか

回答No.4

cj_mover · ベストアンサー

＃２-３、ｃｊです。＃２お礼欄拝見しました。

> アドオンで、設定セル（5個ほど）にネット経由でデータの変更が有るたびに送り込まれます。
お返事頂いてありがとうございます。
そういうことなら、理解できます。
ただ、それはそれで、"普通"ではない"特殊"な条件な訳ですから、
"セル式の計算"という簡単な説明で済まされるものではないですし、
当初の質問文から、明示しておいた方が、より有用な回答が得られ易かったのだと思います。

まぁ、なんとなくDDE絡みの話かも？とか想像できなくもなかったのですが。
とりあえず、
Worksheet_Calculate と Application_AfterCalculate との違いは
ヘルプで確認してみてください。
これが役に立つかどうか、こちらでは未確認ですが、
＃２-３で、役に立つことがもしあるとすれば、_AfterCalculate とリンクを張ったページぐらい
だと思います。

テスト環境を整えて、きちんとレスしようと思ったのですが、
株価RSSは私の環境（Excel2010x64）で使えるものがない（あるにはあるけど他の理由でNG）
ので、断念しました。

１、２年前にRSSに同期をとるVBAサンプルコードが紹介された書籍が出ていたように思いますが、書名が思い出せません。

たぶん、情報をお持ちの方は多数いらっしゃる筈ですから、
要件を整理して、質問建て直した方が、解決が近づくかも知れませんね。
RSSの具体名をタイトルにも掲げるとかして、重要度の高い情報（条件）が
目立つように質問すれば、いいことあるかも、です。

OnTime メソッドについての現状説明については、正直、よく解りませんでした。
あらためて考えてみましたが、
OnTime メソッドって、
１．予定した時刻に指定のプロシージャを実行する。
２．待機中のキューを先に実行させる。（＃２での説明主旨）
３．非同期処理との連携で時刻が過ぎたら処理をキャンセルする
という使い方がメインなんだと思います。
この内、関連ありそうなのは２.３．ですが、
非同期・同期を問わず、外部オブジェクトを扱う際には、決定的な解決策になる場合も多いので、
この際、色々試してみるのも、無駄になることはないと思います。
ただ、RSSの処理の完了のタイミングをとったり、ワークシートの再計算終了のタイミングをとったり、
ということには、やはり役には立たないかな、とは思います。

あらためて、返信ありがとうございました。
解決に届くことを、そして、ご健闘をお祈りいたします。

お礼

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

補足

取り急ぎ

計算完了の件。
お説の通り、当方は　セル式の計算完了を意識せず、コードを書いて良さそうです。

あと、実行速度を上げる件、挑戦してみます

回答No.3

cj_mover

（２／２）

ここからは、提案、のようなものです。

> 1分40秒
えっと、再計算されるセルの数を心得た上での所見ですが、
ちょっと遅過ぎるように思います。
最適化を究めたとしてもそれ位掛かる複雑な計算なのかも知れませんが、
いずれにしても重い計算になっていることは確かなので、
手当て出来ることは色々ありますし、まだ手を付けていないこともあるかも知れませんから、
再計算に要する（正確には再計算が終了して次のＶＢＡステートメントに進む迄に要する）時間
を短縮することについて、優先度を高めた方が、全体として苦労を減らせるような気がします。

まずＶＢＡ側単独で出来ることとして、例えば、
（＃１さんご紹介の前スレでも示唆されている）
　　Application.Calculation = xlCalculationManual
　　処理
　　Application.Calculation = xlCalculationAutomatic
とか、
　　Application.EnableEvents = False
　　処理
　　Application.EnableEvents = True
（AfterCalculate() イベントを使う場合は×。その他のイベントだけをキャンセルするように工夫）
単セル毎に値の変更をして（その度に再計算して）いるのを止めて
代りに.Calculationを一時中止した上で、矩形範囲毎に纏めて配列出力して
すべての参照先の値が確定してから再計算させるように替えるとか、
　　Application.Calculation = xlCalculationManual
　　処理
　　Range(再計算が必要な範囲に限定).Calculate
　　Application.Calculation = xlCalculationAutomatic
とか、
マルチスレッドじゃない場合で、その他の条件があえば、
　　Application.Calculation = xlCalculationManual
　　処理
　　Range(再計算が必要な範囲に限定).CalculateRowMajorOrder
　　Application.Calculation = xlCalculationAutomatic
とか。

Excel数式側で出来ることとして、例えば、
マルチスレッド計算のオプションが適正に設定されているか確認する。
数珠つなぎ的な（チェーン）参照（A1=const B1=A1+1 C1=B1+1 D1=C1+1 ... みたいな）を減らす。
INDIRECT()関数は止め、なるべくINDEX()関数に（次点としてOFFSET()関数）に振り返る。
【マルチスレッド再計算、適用外の関数】
｜　PHONETIC
｜　CELL(format引数またはaddress引数を使用する場合)
｜　INDIRECT
｜　GETPIVOTDATA
｜　CUBE関連の各関数
｜　ADDRESS(5番目のパラメーsheet_namが提供されている場合)
｜　PivotTableを参照する任意のデータベース関(DSUM、DAVERAGE など)
｜　ERROR.TYPE
｜　HYPERLINK
｜　VBAおよびCOMアドインによるユーザー定義関数
以上はマルチスレッドによる再計算の恩恵を受け易くする為の話です。
数式の書き方として、参照する回数を減らす。
（同じ数式を書くのにも、同じセル範囲への参照を減らす書き方を心掛ける）
参照先、が、上、左にある（下、右にない）ようにシートデザインする。
COUNTIF SUMIF SUMPRODUCT 単セルで定義した配列数式 など、いわゆる重い数式を、
他の関数に振り返る。
フィルドラッグ等でコピー可能な連続した数式を、単セル毎に設定している代りに、
複数セル範囲を選択した上でCtrl+Shift+Enterで確定する配列数式（FormulaArray）にする。

とか。
もっと出来ること色々ありますが、条件によっては劇的に処理が速くなる、
なんてことも、経験された方多い、というか、ここで回答付けてる方の殆どが、
一度経験されたことと思います。
実物を見た訳じゃないので、こちらが想像するより遥かに最適化が進んでいるのかも知れませんが、
やり残したことがあれば、参考になれば嬉しいです。

また、前スレでは完全に否定されて一蹴されていますが、
再計算のすべてをＶＢＡで処理する、というのも、（再計算が必要なセル数を心得た上で）
処理を速める正しい方向である場合が多いです。
（シートに数式を置かない（減らす）設計って、直接的な処理速度以外の面でも幾つか恩恵がありますね。）
ＶＢＡの記述ひとつひとつにも遅い速いある訳で、下手すれば、ワークシートの再計算の方がマシ、
うまくすれば、あっという間、なんて、色々な状況や環境変数的なものを考える必要はありますが。

参考URLは、私が最近よく参照（ＶＢＡというよりシート設計の最適化に役立つ）ぺージです。
『Excel 2010 におけるパフォーマンス: パフォーマンスの向上と制限の改善』
このページで、以下のキーワード検索すると役にたつことあるかも？です。
マルチスレッド計算　INDIRECT　Workbook.ForceFullCalculation　名前付きテーブルと構造化参照　Range.CalculateRowMajorOrder

大変恐縮ながら、私の疑問にもお応え頂けると有難いです。
お願いしますm(__)m

長、失礼しました。

（以上です）

参考URL：

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

回答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)で　計算式完了後の変化を捉えることが出来るはずなんですが、これも、不発で
むしろ、これの方が　良いかも・・・テストさせていただきます。

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

回答No.1

NotFound404

application.CalculationState の xlCalculating 、xlDone 、 xlPending
使えませんかね？
半年前のあなたとワタシ。
http://okwave.jp/qa/q8523503.html

お礼

再度、恐縮です。

今、タイマーやめて　それで　実験中。うまくゆきそうです。
Do
Sleep (10)
DoEvents
x = x + 1
If x > 10000 Then
　　yy = orgdata.Cells(1000, 14).End(xlUp).Row + 1
　　　　　orgdata.Cells(yy, 14) = "loop-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
Exit Do
End If
Loop
Call nextjob

データ転送から　LOOP　抜ける時間差は　50秒以下なのですが、

分からないのが、タイマー使って　時間差（90秒前後）で　仕事をさせると、タイマーが動作しない。
時間を120秒くらい遅らせて　タイマー使うと何とか・・・
それでも、動作し無い時も有り、不安定。

タイマーは仕事が忙しいとサボるらしいのですが、（その為に次の時間があるはずなんだけど）
どうも、このああたりが・・・・