【Excel】計算方法の設定は全ブックに効く|手動計算の事故

「計算方法の設定は全ブックに効く」のタイトルと、Application.Calculation = xlCalculationManual のコードを描いたアイキャッチ 実務設計

関数を大量に使ったブックで、作業が重くなるため、計算方法を手動にして使っていました。ところが、うっかり自動に戻すと、今度は重くて動かなくなりました。計算方法は、思っているより効き方が広く、扱いを間違えると作業が止まります。

「計算方法」は数式タブにあるので、ブックの設定に見えます。しかし実際は、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です。

操作B1B2C1(別シート)
何もしない1010100
Range("B1").Calculate2010100
Worksheet.Calculate(Sheet1)2020100
Application.Calculate2020200

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) を使用するか、自動計算モードに切り替えて、値を更新してください」と書かれている
  • ステータスバーに「再計算」と出る
手動計算でA1を2に変えたあとの画面。B1の値に取り消し線が付き、緑の通知「ブックに古い値があります」とステータスバーの「再計算」が出ている
手動計算で値が古くなったときの画面(画面の一部を切り出して縦に並べています)

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

再計算したあとの画面。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版、他のバージョンは未検証です。

コメント

タイトルとURLをコピーしました