VBAを使えば、手作業で行うと何時間もかかる大きな処理も、高速化の設定をすれば一瞬で終わらせてしまうことも可能です。
ただし、VBAがワークシートのセルに入力してある計算式を参照したりする場合は、計算がちゃんとできているかなど、注意しなければならない点があります。
それでは、ExcelにおけるVBAとセルの計算式の再計算方法について解説していきたいと思います。
はじめに
実は、当サイトで紹介した記事のサンプルご利用者様から、次のようなご相談いただいた際に、VBAの処理にワークシート上の計算が追い付かない事例がありました。
ご相談内容は、記事ではメールの宛先名が「会社名」だけでしたが、担当者名を追加表示できないかというものでした。ご案内させていただいた内容は、ワークシート上の計算式（VLOOKUP関数）で担当者名をリストから取り出すようにするものでしたが、これが引っかかってしまいました。
その後、問題は無事解決しましたが、今後同様の事例があった場合のために計算式の再計算について整理しておくこととします。
【この記事でわかること】
・Application.Calcurationプロパティで「計算方法」を制御する方法
・Worksheet.EnableCalculation プロパティでワークシートの計算設定する方法
・object.Calculate メソッドでオブジェクトの計算を実行する方法
VBA高速化に定番の計算方法切り替え
ExcelのVBA処理を高速化するために、実行中のワークシート上の計算を止めるため計算方法を「手動計算」に設定するという方法があります。そして、VBAの処理終了時に「自動計算」に戻し再計算を実行するようにします。
これは、重いVBAの処理を高速化するための定番の設定方法のひとつです。その他にも処理を制御する主な定番としては次のようなものがあります。先ほど紹介した記事のサンプルでもこのプロシージャを呼び出して動作を制御していました。
Sub マクロ開始()
With Application
.ScreenUpdating = False '画面描画を停止
.EnableEvents = False 'イベントを抑止
.DisplayAlerts = False '確認メッセージ抑止
.Calculation = xlCalculationManual '計算を手動に
End With
End Sub
Sub マクロ終了()
With Application
.Calculation = xlCalculationAutomatic '計算を自動
.DisplayAlerts = True '確認メッセージ開始
.EnableEvents = True 'イベントを開始
.ScreenUpdating = True '画面描画を開始
End With
End SubApplication.Calcurationプロパティで「計算方法」を制御
「計算方法」は、Applicationオブジェクトの CalcurationプロパティにXlCalculation列挙型名または値を設定して計算モードを制御します。
【書式】Application.Calcuration = 設定値（計算モード）
| 型名 | 値 | 計算モード |
|---|---|---|
| xlCalculationAutomatic | -4105 | 自動 |
| xlCalculationManual | -4135 | 手動 |
| xlCalculationSemiautomatic | 2 | データテーブル以外自動 |
手動で設定する場合はこちらのリボンで選択します。
【手動にする場合】Application.Calculation = xlCalculationManual と指定します。
この指定がされると、設定を解除するまではすべて「手動」となり再計算されなくなります。
VBAの処理が終了するタイミングで、Application.Calculation = xlCalculationAutomatic と指定して計算方法の設定を「自動」に戻すようにします。
計算方法の設定は、その時に起動しているExcel全体への設定になるということに注意してください。
再計算が必要なケース
ワークシート上の計算式の入ったセルからデータを取得する場合は、高速化のために計算方法の設定を「手動」にしている状態のままでは、誤ったデータが使われてしまいトラブルの原因となります。
セルの計算結果がVBAの中で使われたり、VBAがセルに入力した値などを別セルの計算式が参照していたりする場合などには再計算が必須です。
では、計算方法が「手動」に設定されている状態で、セルの数式を再計算させたい場合どうしたらよいでしょうか。必要に応じて再計算を行う方法にはどのようなものがあるか見ていきましょう。
ワークシートの計算を制御する
前段の Application.Calculation は、その時に起動しているExcel全体への「計算方法の設定」の制御でしたが、ワークシートを制御できる方法があります。
Worksheet.EnableCalculation プロパティ
指定したワークシートの再計算を実行するかどうかを設定できます。プロパティにブール型(Boolean)の値で機能を切り替えます。計算方法の設定にかかわらずシートの再計算モードを指定できます。
【書式】Worksheet.EnableCalcuration = Boolen（ブール型）
| 設定値 | 計算モード |
|---|---|
| True | 必要に応じてシートが自動的に再計算されます |
| False | シートが自動的に再計算しないようにします |
このプロパティが False の場合、シートの再計算を要求できなくなります。 False から True に値を変更すると、シートの再計算を実行します。
このプロパティの規定値は「True」です。「False」のままブックを保存終了しても、次に起動する際は規定値の「True」になっています。ただし「True」なのに再計算されない状態で開くので注意が必要です。
ブック単位でシートを制御する
次のようなコードを使えば、ブック内の複数シートに設定することもできます。
Sub Sample01()
Dim ws As Worksheet 'Worksheetオブジェクト変数を宣言
'ブック内の全シートを再計算しないようにする
For Each ws In Workbooks("Book1.xlsx").Worksheets
ws.EnableCalculation = False
Next ws
'～～～処理～～～
'処理終了後にブック内の全シートを再計算する
For Each ws In Workbooks("Book1.xlsx").Worksheets
ws.EnableCalculation = True
Next ws
End SubFor Each Nextステートメントを使い「Workbook1.xlsx」内のシート数分をループして「.EnableCalculation」を設定しています。
初めに「False」を設定して再計算させないようにし、処理後に「True」で再計算させています。
指定するオブジェクトを再計算する
特定のタイミングで再計算を実行するには「Calculate メソッド」を使います。
.Calculate メソッド
計算対象とするオブジェクトの計算を実行します。
【書式】object.Calcurate
| 対象オブジェクト | 計算対象 | 指定例 |
|---|---|---|
| Application | 開いているすべてのブック | Application.Calculate または Calculate |
| Worksheet | 特定のワークシート | Worksheets(1).Calculate |
| Range | 指定されたセル範囲 | Worksheets(1).Rows(5).Calculate |
【セル範囲を指定する使用例】
'セル B2～F10 の範囲だけ計算する
Worksheets("Sheet1").Range("B2:F10").CalculateExcel の自動の設定が「手動」の場合に再計算を行うには、.Calculate メソッドを実行します。
指定するオブジェクトによって、再計算を行う範囲を指定・変更することができます。
再計算可能な計算設定について確認する
計算の設定が「自動／手動」とワークシートの計算が「True／False」それぞれの状況に応じて「再計算」されるかどうかをテストして確認した結果を表にしました。
| Application .Calcuration 「計算の設定」 | Worksheet .EnableCalculation 「ワークシート計算」 | Range .Calculate 「セル範囲」 | Worksheet .Calculate 「ワークシート」 | Application .Calculate 「全ブック」 |
|---|---|---|---|---|
| 自動 | True | 〇 | 〇 | 〇 |
| 手動 | True | 〇 | 〇 | 〇 |
| 自動 | False | × | × | × |
| 手動 | False | 〇 | × | × |
Worksheet.EnableCalculation = True なら .Calculate は全てOKです。
Range.Calculate だけ「手動」「False」でも再計算することが確認できました。
一番上はデフォルトの設定です。.Calculate しなくても再計算してくれます。
対象オブジェクトや各プロパティーの設定値を切り替えながら、このコードを使ってテストしました。
Sub CalcTest()
Dim Calcmode As String
'xlCalculationAutomatic を入れ替える'
Application.Calculation = xlCalculationManual
Select Case Application.Calculation
Case xlCalculationAutomatic: Calcmode = "「自動」"
Case xlCalculationManual: Calcmode = "「手動」"
End Select
With ActiveSheet
'初めにデータと合計の数式をセット
.Range("A1:A5").Value = 1
.Range("A6").Formula = "=sum(A1:A5)"
MsgBox "EnableCalculation は:" & .EnableCalculation & _
vbCrLf & "ブック全体の計算設定は:" & Calcmode & _
vbCrLf & "データと数式の結果を確認してください"
'ワークシートの計算設定をオフ
.EnableCalculation = False
.Range("A1:A5").Value = 100
MsgBox "EnableCalculation は:" & .EnableCalculation & _
vbCrLf & "ブック全体の計算設定は:" & Calcmode & _
vbCrLf & "再計算されているか確認してください"
'オブジェクトの計算をテスト
'ActiveSheet.Calculate 'Application.Calculate '
.Range("A6").Calculate
MsgBox ".Range(""A6"").Calculateメソッドを実行しました" & _
vbCrLf & "EnableCalculation は:" & .EnableCalculation & _
vbCrLf & "ブック全体の計算設定は:" & Calcmode & _
vbCrLf & "再計算されているか確認してください"
'ワークシート計算をオンに戻す
.EnableCalculation = True
MsgBox "EnableCalculation は:" & .EnableCalculation & _
vbCrLf & "ブック全体の計算設定は:" & Calcmode & _
vbCrLf & "再計算されているか確認してください"
End With
End Sub
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
まとめ（おわりに）
「再計算」を実行する方法については、
・Application.Calcurationプロパティで計算方法を「手動」にした状態で
・Worksheet.EnableCalculation プロパティを「False」にしていると
・Range.Calculate メソッドだけ再計算を実行できることがわかりました。
・Worksheet.EnableCalculation プロパティが「True」なら
・.Calculate メソッドで対象オブジェクト全部で再計算できることがわかりました。
次回の記事について
今回の記事はいかがだったでしょうか。お役に立てたなら幸いです(^^;
「再計算」を実行する方法はわかりましたが、計算処理が完了しているかどうかについてはまだ解説できていません。
次回の記事ではこの部分を解説するようにしたいと思います。
サンプルファイルをダウンロードできます（下記リンク先へ）
この記事のサンプルはありません。
過去の記事で使用したサンプルファイルがダウンロードできるページを用意しています
こちら（このリンク先）からご利用ください
