数式の計算を自動/手動に切り替える方法を解説します。
Application.Calculationプロパティに計算方法を指定することで、
マクロ実行中の数式計算を停止・再開できます。
「手動」設定中に途中で一度再計算を挟みたい場合は、
Application/Worksheet/Range いずれかのCalculateメソッドを使用して下さい。
主にマクロを高速化するために使用するプロパティです。
Applicationによるマクロ高速化シリーズのまとめ記事はこちらをどうぞ
基本構文
' 数式の計算を「手動」に設定 Application.Calculation = xlCalculationManual ' 数式の計算を「自動」に設定 Application.Calculation = xlCalculationAutomatic ' 設定を変えずに一旦再計算 Application.Calculate ' すべてのブックを再計算 対象シート.Calculate ' 対象シートだけ再計算 対象セル範囲.Calculate ' 対象のセルだけ再計算
サンプルコード
Sub サンプルマクロ() ' マクロ実行冒頭で数式の計算を「手動」に設定 Application.Calculation = xlCalculationManual ' マクロのメインコード ' ※ B列に「=A1」という数式が入っていたとしても、 ' その値は更新されません。 Dim R As Long For R = 1 To 1000 Worksheets(1).Cells(R, 1) = R Next ' マクロ終了時に数式の計算を「自動」に設定 Application.Calculation = xlCalculationAutomatic ' ここで今まで保留されていたすべての数式が再計算されます End Sub
Sub サンプルマクロ() ' マクロ実行冒頭で数式の計算を「手動」に設定 Application.Calculation = xlCalculationManual 処理① ' 処理②に必要な数式を更新するために一旦再計算 Worksheets("○○").Calculate ' ※ 設定は変わらないため「手動」のまま 処理② ' マクロ終了時に数式の計算を「自動」に設定 Application.Calculation = xlCalculationAutomatic End Sub
解説
Application.Calculationプロパティに計算方法を指定することで、
Excelの数式計算を自動/手動に切り替えることができます。
主にマクロの高速化を目的に使用され、
以下のApplicationシリーズの中では圧倒的に効力が大きいです。
Application.ScreenUpdating = False ' 描画の停止 Application.Calculation = xlCalculationManual ' 自動計算の停止（高速化効果最大） Application.EnableEvents = False ' イベントマクロの停止 Application.Cursor = xlWait ' マウスカーソル描画の停止
例えばB1～B10000セルに「=A1」という数式が入っているブックで、
以下のコードを実行して検証した結果がこちらです。
Dim R As Long For R = 1 To 10000 Cells(R, 1) = R Next
| 使用した機能 | 結果 |
|---|---|
| そのまま実行 | 4.96秒 |
| ScreenUpdating | 4.66秒 |
| Calculation | 0.50秒 |
| EnableEvents | 4.60秒 |
| Cursor | 5.01秒 |
このように描画やイベントの停止と比較して、
計算の停止は圧倒的な高速化効果を示します。
ただのセル参照ですらこれなので、
LOOKUP系列やSUMIF/COUNTIF系列を使っていると、
数千～数万倍の速度差になる可能性もあります。
積極的に使用していきましょう。
ただし、当然ながら数式の更新を止めてしまっているため、
数式が入ったセルの値を参照するには注意が必要になります。
数式を利用したいが、かといって「自動」にしてはマクロが遅いというときは、
「Calculateメソッド」を使用して部分的に再計算を都度行ってください。
※ 詳細は後述
なお、これらApplication高速化シリーズですが、
このコードを毎回書くのは面倒なので汎用関数にするのがおすすめです。
Sub サンプルマクロ() Call Excelの自動更新を停止する ' マクロのメインコード Call Excelの自動更新を再開する End Sub ' マクロ高速化用汎用関数 Sub Excelの自動更新を停止する() Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Application.Cursor = xlWait End Sub Sub Excelの自動更新を再開する() Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.Cursor = xlDefault End Sub
この関数を用意しておけば、以降はCall1行で済むようになります。
個人用マクロブックなどにこの関数を用意しておきましょう。
詳しくはこちらの記事をどうぞ。
計算が手動のままになったときの対処法
Application.Calculationは、ScreenUpdatingとは違い、
マクロ終了時に自動で元に戻るプロパティではありません。
そのため、最後に自動計算へ戻すコードを実行し忘れると、
マクロ終了後もExcelが手動計算のままになってしまいます。
特に「マクロがエラーで終了した場合」は自動計算に戻らないため、
以下の方法で対応してください。
ExcelのUI上でブックの計算を「自動」に戻す
「数式タブ」 → 「計算方法の設定」 → 「自動」
または
「ファイルタブ」 → 「オプション」 → 「数式」 → 「ブックの計算」
の手順で計算を「自動」に戻すことができます。
またもう一つおすすめの方法として、
クイックアクセスツールバーに登録することもできます。
登録時は、数式タブの「計算方法の設定」本体ツールバーに登録するのではなく、
選択肢にある「自動(A)」「手動(M)」の方を登録しましょう。
こうすると「今どっちにチェックが入っているか」もわかるようになるため、
気づかず手動になっていた問題にも対策することができます。
イミディエイトウィンドウで再開を指示
Application.Calculationはイミディエイトウィンドウでも設定可能です。
よって計算が手動のままになってしまったときは、
Application.Calculation = xlCalculationAutomatic
をイミディエイトウィンドウで実行すれば、
自動計算へ戻すことができます。
汎用関数をリボンやツールバーから実行
先ほど紹介したExcelの自動更新を再開するこちらの汎用関数↓
Sub Excelの自動更新を再開する() Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.Cursor = xlDefault End Sub
このコードは先ほどCallして使っていましたが、
Subプロシージャですのでリボンやツールバーにも設定できます。
これを設置しておけばワンボタンで自動計算に戻せる上、
描画・イベントマクロ・マウスカーソルの復元もセットで行えます。
関数としてももちろん便利ですので、ぜひとも活用していきましょう。
セルの数式を利用するマクロにおける注意点
Calculationプロパティはかなりの高速化効果を持っていますが、
数式を利用したマクロを組む場合は以下の点に注意する必要があります。
部分的な再計算を利用する - Calculate
例えばワークシートの構成が、
- A列にデータがある
- B列にA列を使った数式がある
- C列はマクロで計算する
こんな構成だったとします。
このとき、
Application.Calculation = xlCalculationManual ' 計算を手動に A列にデータを追加する処理 ' ここでA列の変更がB列に反映されていてほしいが。。。 C列を計算する処理 ' ← B列更新前に処理されてしまう。 Application.Calculation = xlCalculationAutomatic ' ここでB列が更新されるが手遅れ
こんな処理を書いてしまうと、
C列の計算に使うB列が、A列の更新を反映していない問題が発生します。
とはいえこの問題に対応するために常時計算を自動にしてしまうと、
マクロがかなり遅くなってしまう恐れもあります。
高速化効果を維持しながらこれに対応するには、
数式を部分的に再計算する「Calculateメソッド」を使用して下さい。
Application.Calculation = xlCalculationManual ' 計算を手動に A列にデータを追加する処理 Worksheets("○○").Calculate ' ← ここで一旦該当シートだけ再計算 C列を計算する処理 ' ← B列更新後に処理 Application.Calculation = xlCalculationAutomatic
なお、Calculateメソッドは「全ブック」「シート」「セル範囲」で実行できます。
Application.Calculate ' すべてのブックを再計算 Worksheets("○○").Calculate ' ← 該当シートだけ再計算 Columns("A").Calculate ' ← A列だけ再計算
なぜかWorkbookオブジェクトにだけCalculateメソッドがありませんので、
「あるブックだけ再計算」したい場合は以下のコードを利用してください。
Dim シート As Worksheet For Each シート In 対象ブック.Worksheets シート.Calculate Next
Formulaとの併用
Formulaプロパティを利用してセルを処理する際、
計算が「手動」になっているとどうなるかというと、
Application.Calculation = xlCalculationManual Range("B1:B100").Formula = "=A1" Range("B1:B100").Value = Range("B1:B100").Value Application.Calculation = xlCalculationAutomatic
このコードは意外にも正しく処理されます。
しかし、こちらのコード↓
Application.Calculation = xlCalculationManual Range("B1").Formula = "=A1" Range("B1").Copy Range("B1:B100") Range("B1:B100").Value = Range("B1:B100").Value Application.Calculation = xlCalculationAutomatic
こちらは正しく動かず、B列すべてのセル値が1となります。
セル範囲.Formula による一括入力はすべてのセルが初回計算されますが、
セル.Copy セル範囲 による数式のコピーでは計算が実行されないということですね。
この仕様を知っていれば手動計算下でFormulaを使ってもよいのですが、
万が一に備えて、値貼り付け前にCalculateしておきましょう。
Application.Calculation = xlCalculationManual Range("B1:B100").Formula = "=A1" Range("B1:B100").Calculate Range("B1:B100").Value = Range("B1:B100").Value Application.Calculation = xlCalculationAutomatic
これだけなら極端に遅くなるということは少ないでしょうし、
Excelの何らかの仕様や不具合に左右されにくくなります。
よほどマクロの速度を気にしたい場合を除き、
Calculateを挟んでおくことをおすすめします。
余談ですが同じセル範囲が何度も出てきますので、
しっかり変数に入れておくとコードが読みやすくなりますね。
Application.Calculation = xlCalculationManual Dim 計算エリア As Range Set 計算エリア = Range("B1:B100") 計算エリア.Formula = "=A1" 計算エリア.Calculate 計算エリア.Value = 計算エリア.Value Application.Calculation = xlCalculationAutomatic
