ExcelでVBAを使って、セルの計算結果を利用する場合、処理に計算が追い付かずに更新前のデータを取得してしまう事故が発生したりします。
計算自体をVBAで処理するのが一番早いのですが、どうしてもワークシート上で計算を行わなくてはならない場合は考慮する必要があります。
はじめに
それでは、ExcelのVBA処理中にセルの計算式が再計算を完了したことを確認するまで次の処理に移らないようにする方法について解説したいと思います。
【この記事でわかること】
・Application.CalcurationState プロパティで「計算状態」を調べる方法
・VBAで実行した再計算の処理を確実に完了させる方法
・再計算の方法による実行速度の違いがわかります
Application.CalculationState プロパティ
起動中のExcelで実行されるすべての計算について、Applicationの計算状態を示す XlCalculationState 定数を返します。読み取り専用です。
【書式】Application.CalcurationState = 定数（値）
※ 列挙型 XlCalculationState の定数一覧です。
| 定数名 | 値 | 説明 |
|---|---|---|
| xlCalculating | 1 | 計算の処理中 |
| xlDone | ０ | 計算完了 |
| xlPending | 2 | 計算すべき変更が発生したが、計算は未実行 |
次の例は、Excelで計算に実行状況を確認します。計算がすでに完了している状況なら「完了」のメッセージを表示します。それ以外（計算待ちや計算中）の場合は、計算状態が「未完了」のメッセージを表示します。
Sub CalculationStatus()
If Application.CalculationState = xlDone Then
MsgBox "完了"
Else
MsgBox "未完了"
End If
End Subこれを利用して、計算が「未完了」のままにならないようにしていきます。
計算完了を確認する方法
先ほどの例では、「完了」と「未完了」で判定を分けましたが、「未完了」には「計算中」と「計算待ち」の2種類あります。
やってはいけない例
まずはこちらの【やってはいけない】例です。
Sub Sample01()
'計算を「手動」にする
Application.Calculation = xlCalculationManual
Range("I4") = 7 'トリガーのセルにデータ書き込み
ActiveSheet.Calculate 'シート再計算
'計算状況を確認
Do While Application.CalculationState <> xlDone
DoEvents
Loop
'計算を「自動」に戻す
Application.Calculation = xlCalculationAutomatic
End Sub・Application.CalculationState が xlDone（完了）でない場合、ループするようにしています。
・もし、Application.CalculationState が xlPending（計算待機）だった場合、無限ループとなってしまいます。（DoEvents を入れて中断可能にしています）
・Application.CalculationState が xlPending（計算待機）になる例は、別シートや別ブックを参照している場合に発生します。
・何故なら、ActiveSheet.Calculate で再計算を命令しているから、シートの計算は完了しているけど参照元で「計算待ち」の状況となっているからです。（Excelのステータスバーを見ると「再計算」と表示されている状態です）
・こうならないようにする方法としては、再計算を Application.Calculate にするか、xlPending（計算待機）を考慮するように対策します。
xlPending だった場合の処理を入れた例
シート再計算では xlPending となる場合、Application.Calculate で再計算するようにします。
Sub Sample02()
'計算を「手動」にする
Application.Calculation = xlCalculationManual
Range("I4") = 7 'トリガーのセルにデータ書き込み
ActiveSheet.Calculate 'シート再計算
'計算状況を確認
Do While Application.CalculationState <> xlDone
'計算状況が xlPending の場合
If Application.CalculationState = xlPending Then
Application.Calculate
End If
DoEvents
Loop
'計算を「自動」に戻す
Application.Calculation = xlCalculationAutomatic
End Sub・安全のため「DoEvents」を入れています。
・このようにすれば xlPending だった場合には Application.Calculate を実行して計算を完了させるようにすることができます。
実行速度を比較
ランダムなデータをつくる100万行×7列の表がアクティブシートです。その他3枚のシートにあるデータを計算式で参照しているブックを使って計測します。
次のコードを使って12行目から14行目の計算方法を入れ替えながら、各3回計測してみました。
Sub CalculationJudgement()
Dim lcnt As Long
Dim t As Variant
'計算を「手動」にする
Application.Calculation = xlCalculationManual
'Loop回数カウンターセット
lcnt = 0
Range("I4") = 7 'トリガーのセルにデータ書き込み
'タイマースタート
t = Timer
Application.Calculate '全ブック再計算
' ActiveSheet.Calculate 'シート再計算
' Range("H1").Calculate 'セル範囲再計算
'計算状況を確認
Do While Application.CalculationState <> xlDone
'計算状況が xlPending の場合
If Application.CalculationState = xlPending Then
Application.Calculate
End If
DoEvents
count = count + 1
Loop
MsgBox "Loop回数は、" & count & "回でした！" & _
vbCrLf & "計測時間は" & Timer - t & "でした！"
'計算を「自動」に戻す
Application.Calculation = xlCalculationAutomatic
End Sub結果は次の表のとおりでした。
| Application | Worksheet | Range | |
|---|---|---|---|
| 1回目 | 4.273488 | 8.144531 | 4.722656 |
| 2回目 | 4.179688 | 7.870313 | 4.519531 |
| 3回目 | 4.214844 | 7.863281 | 4.535156 |
| 平均 | 4.222673 | 7.959375 | 4.592448 |
| Loop回数 | 0回 | 1回 | 1回 |
・一緒に開いているExcelブックの数やセル範囲の大きさなどでデータは変わると思います。
・Worksheet が一番遅い結果です。Range が速いのはセル一つだけの再計算だったからです。
・Loopが1回なのは、最初の判定ですでに xlPending（計算待機）だったということです。
次に Application.Calculate が実行された結果になっているということです。
・Applocation で何度もテストを繰り返してみましたが、Loopすることはありませんでした。
・Applocation なら Application.CalculationStat で検証する必要がないのかもしれませんが確定ではありません。計算は完了される必要がありますので安全策は必要だと思います。
・その時の計算量などによって使い分ければよいと思います。でも、安全のためにLoop処理はあった方がよいのかもしれません。
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
おわりに
今回の記事はいかがだったでしょうか。お役に立てたなら幸いです(^^;
「再計算」を実行後に、計算処理が完了しているかどうかを確認する方法について解説しました。
実際に検証してみた結果としては、最後の計測データが参考になるかと思います。
Application.Calculation = xlCalculationManual を
Application.Calculation = xlCalculationAutomatic に切り替えてしまう方法もありますが、その場合は再度 xlCalculationManual に切り替える必要があります。
VBAで処理を行う場合は、誤った結果とならないように検証が必要ですね！
サンプルファイルをダウンロードできます（下記リンク先へ）
この記事のサンプルファイルはありません。記事内のコードをご利用ください。
過去の記事で使用したサンプルファイルがダウンロードできるページを用意しています
こちら（このリンク先）からご利用ください
