関数を大量に使ったブックで、作業が重くなるため、計算方法を手動にして使っていました。ところが、うっかり自動に戻すと、今度は重くて動かなくなりました。計算方法は、思っているより効き方が広く、扱いを間違えると作業が止まります。
「計算方法」は数式タブにあるので、ブックの設定に見えます。しかし実際は、Excel全体の設定です。別のブックを開いただけで、数字が更新されなくなることがあります。この記事では、手動計算で数字が古いままになる事故の仕組みと、再計算の範囲、タイムスタンプ用途の注意点を、手元のExcelで確かめた結果でまとめます。中級者向けに、事故がどこで起きて、どう確かめるかに絞ります。
先に結論
- 計算方法はブック単位でなく、Excel全体の設定。最初に開いたブックで決まる
- 手動の間は、シート単位の再計算では他のシートが古いまま。全体は
Application.Calculate NOW・RANDなどの揮発性関数は、再計算のたびに変わる。値で残したいときは値にする- マクロで変えたら、エラー時も含めて元に戻す
計算方法はブック単位ではなく、Excel全体の設定
手元の環境では、計算方法を決めるのは最初に開いたブックでした。
- 手動で保存したファイルを先に開き、そのあと自動で保存したファイルを開く → 手動のまま
- 自動で保存したファイルを先に開き、そのあと手動で保存したファイルを開く → 自動のまま
- ブックを全部閉じてから開き直す → 次に開いた1本目で決まる
- 手動にしたあとで新規ブックを追加する → 手動のまま
アクティブなブックを切り替えても、設定は変わりません。設定は1つだけで、どのブックからでも変えられます。つまり、手動で保存されたブックを開いたまま、別のブックで作業すると、そのブックの数式も更新されません。
別のExcelプロセス(別のインスタンス)は別の設定でした。片方を手動にしても、もう片方は自動のままです。計算モードの決まり方は、Excel が現在の計算モードを決定する方法(Microsoft Learn)にもあります。
なお、ブックを1冊も開いていない状態でApplication.Calculationを設定すると、実行時エラー1004になりました。マクロで設定するときは、ブックを開いてからにします。
手動のときに再計算される範囲
手動のまま、A1とA2を1から2に変更し、再計算の方法を変えて、何が更新されるかを調べました。B1は=A1*10、B2は=A2*10、別シートのC1は=Sheet1!A1*100です。
| 操作 | B1 | B2 | C1(別シート) |
|---|---|---|---|
| 何もしない | 10 | 10 | 100 |
Range("B1").Calculate | 20 | 10 | 100 |
Worksheet.Calculate(Sheet1) | 20 | 20 | 100 |
Application.Calculate | 20 | 20 | 200 |
Sheet1だけを再計算すると、別シートのC1は古いままです。A1とA2を3にして、Sheet2だけをCalculateすると、C1は300になり、Sheet1のB1・B2は20のままでした。Application.CalculateFullを呼ぶと、すべてが最新になりました。
同じExcelで2つのブックを開いて試すと、片方のシートだけCalculateしたときは、もう片方のブックは古いままでした。Application.Calculateでは、両方が更新されました。
古い値が残っている間は、Application.CalculationStateが未計算あり(xlPending)を返し、Application.Calculateのあとで完了(xlDone)になりました。シート単位の再計算を済ませて「終わった」と思い込むのが、典型的な事故です。
最近のExcelは、古い値を知らせてくれる
手元のMicrosoft 365版のExcelでは、手動計算のまま値が古くなると、次の表示が出ました。
- 古いセルの値に取り消し線が付く
- 画面左下に緑の通知「ブックに古い値があります」が出る。「[今すぐ計算] (F9) を使用するか、自動計算モードに切り替えて、値を更新してください」と書かれている
- ステータスバーに「再計算」と出る

再計算すると、B1は4に更新され、取り消し線も通知も消えました。

この表示は「古い値の書式設定」で、数式タブの「計算オプション」で切り替えます。気づける場面は増えました。それでも、次の場面は気づきにくいままです。
- 古い値のまま、コピーして別の資料に貼る、印刷する、PDFにする
- マクロや他のツールが、古い値のセルをそのまま読む
- この表示がオフの設定、またはMicrosoft 365版以外のExcel(買い切り版では未検証)
知らせてくれることと、自動で直ることは別です。気づいたあとに、どの範囲を再計算するかを決める材料として、上の表を使います。
揮発性関数は、再計算のたびに動く
RAND・NOWは、手動でもApplication.Calculateで値が変わりました。自動計算のときは、関係のないセルを編集しただけでRANDが変わりました。注意したいのは、タイムスタンプのつもりでNOWを入れた列です。再計算のたびに更新されるので、「入力した時刻」にはなりません。値として残したいときは、Range.Value2 = Range.Value2で数式を値に置き換えます。置き換えたセルは、数式がなくなり、以後は編集しても変わりませんでした。
マクロで計算方法を変えたら、元に戻す
大量に書き込む前にApplication.Calculation = xlCalculationManualにするのは定番です。ただし、設定は全ブックに効くので、戻し忘れると他のブックにも残ります。後片付けしないSubが終わっても、設定は手動のままでした。
Sub WriteWithManualCalc()
Dim oldMode As Long
oldMode = Application.Calculation
On Error GoTo CleanUp
Application.Calculation = xlCalculationManual
' ここで大量に書き込む
CleanUp:
Application.Calculation = oldMode
End Sub
エラーで途中終了しても戻るように、元の値を先に控えて、後片付けの行に必ず通します。この考え方は、【VBA】エラー時も設定を元に戻す|ScreenUpdating等の後片付けで詳しく書きました。処理が遅い原因の切り分けは、【VBA】処理が遅い本当の原因|シート操作が重くなる仕組みにあります。
手動のまま保存すると、古い値が保存されることがある
手動計算のまま、B1に =A1*2 を入れ、A1を1から5に変えて保存しました。「保存前にブックを再計算する」(Application.CalculateBeforeSave)が True のときは、保存時に再計算され、B1は10になりました。False のときは、B1は2のまま保存され、開き直しても2でした(A1は5)。引き継いだブックでは、計算方法と、この保存前の設定の両方を確認項目に入れておくと安心です。ほかの確認項目は、【Excel】引き継ぎブックで先に見る外部リンク・名前・接続にまとめました。
まとめ
- 計算方法はExcel全体の設定。手元では、最初に開いたブックで決まった
- 手動の間は、
Worksheet.Calculateでは他のシートが古いまま。全体はApplication.Calculate - 古い値は取り消し線で知らせてくれるが、コピーや印刷、他のツールの読み取りでは気づきにくい
NOW・RANDは再計算で変わる。残したい値は値にする- マクロで変えたら、エラー時も含めて元に戻す
確認した環境は、Excel 16.0(Microsoft 365)・日本語版です。F9などのキー操作、Excel 2021未満、Mac版、他のバージョンは未検証です。


コメント