VBA技術解説
手動計算時の注意点と再計算方法

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日:2019-10-09 最終更新日:2020-03-13

手動計算時の注意点と再計算方法


セルを変更する度に再計算が動いてしまうと処理時間が遅くなってしまいます。
そこでマクロVBAの中で、計算方法を手動にすることで対処することになります。
このとき処理結果が期待した結果にならないといった事が起こらないように、
手動計算時の注意点と再計算方法について解説します。


計算方法の切り替え:自動⇔手動

マクロ VBA Calculate

計算方法をVBAで切り替えるには、
ApplicationのCalculationプロパティを設定します。

Application.Calculation = 設定値

設定値
xlCalculationAutomatic 自動
xlCalculationManual 手動
xlCalculationSemiautomatic データテーブル以外の自動

Application.Calculation = xlCalculationManual
この記述をすることで、
これ以降、手動計算となり、自動では再計算は行われなくなります。
この設定は、マクロ終了後も引き続き有効となりますので、
必要に応じて、xlCalculationAutomaticに戻してください。

計算方法はExcel全体の設定です
Applicationのプロパティですので、ブック単位ではなく起動しているExcel全体への設定になります。
従って、ブックごとに計算方法を変更することはできません。

計算式を入れると必ず計算される

計算方法が手動の場合は、
数式が参照している先のセル値が変更になっても再計算されません。
もちろん、計算方法を自動にした時点で再計算されますが、
マクロVBA実行中でxlCalculationManualになっている状態では、セル値は正しい値にはなっていません。

手動計算になっていても、
セルに計算式を入れた場合は、計算式を入れたセルについては必ず計算が行われます。

マクロ VBA Calculate

C1:C5にA列/B列の計算式を入れます。

Sub sample1()
  Application.Calculation = xlCalculationManual
  Range("C1:C5").Value = "=A1/B1"
  Call valuePrint
End Sub

マクロ VBA Calculate

Range("C2:C5").Value = "=A2/B2"
Valueプロパティは、Formulaプロパティでも同じです。
Range("C2:C5").Formula = "=A2/B2"

手動計算時であっても、
VBA内で計算式を入れたセルについては、特に意識的に再計算を行わなくても問題はありません。
しかし、これから説明するような場合には、VBAで再計算を行わない限り再計算されません。

ブック保存時は再計算される

手動計算になっていれば、ブックを開いたときには再計算されません。
しかし、ブック保存時は再計算されます。
このブック保存時の再計算を止めるには、Application.CalculateBeforeSaveプロパティをFalseにします。

Application.CalculateBeforeSave = False

ただし、手動計算になっていなければ、次に開いたときに再計算されてしまうので意味がなくなります。
従って、通常はCalculationプロパティとともに使用します。

Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = False

セルのコピーでは再計算されない

数式の入っているセルがあり、そのセルと同じ計算式を入れたい場合、普通はセルをコピーするでしょう。
以下のように、
C1セルに数式が入っているので、これと同じ数式をC2:C5に入れる場合です。

マクロ VBA Calculate

Sub sample2()
  Application.Calculation = xlCalculationManual
  Range("C1").Copy
  Range("C2:C5").PasteSpecial Paste:=xlPasteFormulas
  Application.CutCopyMode = False
End Sub

マクロ VBA Calculate

再計算が行われていません、コピー元のC1の値がそのまま表示されています。
では、コピー方法を変えてみましょう。

Sub sample3()
  Application.Calculation = xlCalculationManual
  Range("C1").Copy Destination:=Range("C2:C5")
End Sub

マクロ VBA サンプル画像

オートフィルでもやってみましょう。

Sub Macro4()
  Application.Calculation = xlCalculationManual
  Range("C1").AutoFill Destination:=Range("C1:C5")
End Sub

マクロ VBA Calculate

いずれの方法でも再計算されません。
セルをコピーした以降のVBAにおいて、これらの(計算式を入れた)セルの値を使うとしたら・・・
これでは当然正しい結果にならないことになります。

言うまでもありませんが、これはシートで手動操作した時と同じです。
シート操作で再計算されないものはVBAでも同じです。
このような場合に、ショートカットF9を使う事は頻繁にあるのではないでしょうか。
VBAでは、この再計算を行う手段がいくつも用意されています。

いろいろな再計算方法

計算方法を手動→自動→手動
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
いったん自動に戻した時点で再計算が行われます。
自動にした以降で、他のセル値を変更する時の為に手動にしたい場合は、再度手動にしておけば良いでしょう。
全ブックを再計算
ApplicationのCalculateメソッド
Application.Calculate
開かれている全てのブックの全てのセルが再計算されます。
ただし、再計算が必要ない(とエクセルが認識している)セルは再計算しません。
通常は、これを実行すれば全てのセルが正しい計算結果となります。
ApplicationのCalculateFullメソッド
Application.CalculateFull
開かれているすべてのブックの全ての計算式が再計算されます。
再計算の必要のないセルも再計算します。
通常は、Calculateを使用してください。
ApplicationのCalculateFullRebuildメソッド
Application.CalculateFullRebuild
開いている全てのシートで、すべて計算し、依存を再構築します。
これは、すべての数式を再入力する事と同じです。
通常は、使用することはありません。

再計算したいシートだけ再計算
WorksheetオブジェクトのCalculateメソッド
ActiveSheet.Calculate
Worksheets("Sheet1").Calculate
指定のワークシートの全てのセルが再計算されます。
再計算が必要ない(とエクセルが認識している)セルは再計算しません。
再計算したいセルだけ再計算
RangeオブジェクトのCalculateメソッド
Range("C2:C5").Calculate
Rangeオブジェクトとして指定されたセルだけが再計算されます。
再計算が必要ない(とエクセルが認識している)セルは再計算しません。

Sub sample5()
  Application.Calculation = xlCalculationManual
  Range("C1").Copy Destination:=Range("C2:C5")
  Range("C2:C5").Calculate
End Sub

マクロ VBA Calculate

RangeオブジェクトのDirtyメソッドについて
少し別件になりますが、RangeオブジェクトのDirtyメソッドについて記載しておきます。
Dirtyメソッドは、次の再計算が発生したときに再計算するよう指定するものであり、その場では再計算は行われません。
VBAリファレンスの翻訳が曖昧なこともあり、勘違いされる事もありそうです。
エクセルは再計算が必要なセルを自動で認識しているので、通常はこのメソッドを使う必要性はありません。

他のセルと同じ数式を入れたい時に最も簡単な方法

今回のC1セルの数式と同じ数式をC2:C5に入れる場合、
再計算をわざわざ実行することなく、その場で再計算されるようにVBAを記述できます。
以下の方法ならとても簡単に実現できます。

Sub sample6()
  Application.Calculation = xlCalculationManual
  Range("C2:C5").FormulaR1C1 = Range("C1").FormulaR1C1
End Sub

セルに計算式を入れる方法についての詳細は以下を参照してください。
第38回.セルに計算式を設定(Formula)|VBA入門
・計算式を設定できるプロパティ ・Valueプロパティ ・Formulaプロパティ , FormulaLocalプロパティ ・FormulaR1C1プロパティ , FormulaR1C1Localプロパティ ・R1C1参照形式 ・Localが付くプロパティについて ・それぞれの違い(Localは除く) ・何故、こんなに多くのプロパティが存在しているのか ・R1C1形式を使うメリット ・たった1行のVBAで複数のセルに計算式を入れる

Formulaプロパティで取得される数式は、
"=A1/B1"
このようにセル参照がセル番地になっているため、他のセルのFormulaに入れても意図した数式にはなりません。
しかし、
FormulaR1C1プロパティで取得される数式は、
"=RC[-2]/RC[-1]"
このようにR1C1形式として、数式の入っているセルからの相対位置指定になっています。
したがって、他のセルに入れれば、数式の参照するセルも相対参照としてずれてくれます。

少なくとも今回の例であれば、これが最も簡単な方法になるでしょう。



同じテーマ「マクロVBA技術解説」の記事

VBAこれだけは覚えておきたい必須基本例文10

・1.最終行まで処理 ・2.後ろから逆順ループ ・3.コレクション処理 ・4.分岐処理 ・5.セルのコピー ・6.VBA関数とワークシート関数 ・7.オブジェクト変数とWith ・8.ブックを開く・閉じる ・9.ファイル一覧 ・10.テキスト読み書き ・VBA必須基本例文10を覚えた後は
エクセルVBAでのシート指定方法
・シートのインデックス番号で指定 ・シートの名称で指定 ・シートのオブジェクト名で指定 ・ブックの保護 ・VBAでのシート指定方法
文字列結合&でコンパイルエラーになる理由
・&の記述がエラーになる入力とは ・&の前後にはスペースが必要な時がある理由 ・&の他では^(キャレット)でも同様の事がありえます ・最後に
手動計算時の注意点と再計算方法
VBAの用語について:ステートメントとは
・キーワード ・予約語 ・演算子 ・識別子 ・ステートメント ・VBA用語の最後に
オブジェクト変数とは何か
・そもそも変数とは何か ・変数の入れ物としての大きさは ・オブジェクト変数にオブジェクトは入っていない ・オブジェクト変数のメモリアドレス ・Is演算子によるオブジェクトの比較 ・TypeOf演算子 ・オブジェクト変数を使う時の注意点 ・オブジェクト変数の最後に
VBAの小数以下の演算誤差について
・小数以下の演算誤差とは ・小数以下の演算誤差を実際のVBAで確認 ・データ型で小数以下の演算誤差に対応 ・小数以下の演算誤差にデータ型以外で対応する ・VBAの小数以下の演算誤差の最後に
スピルでVBAの何が変わったか
・Rangeオブジェクトに追加されたプロパティ ・セルに数式を設定する場合 ・1次元配列を返すユーザー定義関数 ・2次元配列を返すユーザー定義関数 ・JAG配列を返すユーザー定義関数 ・スピルのVBAでの活用について
CharactersプロパティとCharactersオブジェクト
・Charactersプロパティ ・Charactersオブジェクト ・CharactersプロパティとCharactersオブジェクトの使用例
ユーザーに絶対に停止させたくない場合のVBA設定
ユーザー操作によりマクロVBAが実行中に停止させられてしまう場合があります。再実行可能であれば問題ありませんが、途中で強制停止された場合のデータ保証が出来ないと言った場合もあるでしよう。マクロ実行でも、以下のキーボード操作でVBAは停止できてしまいます。
印刷範囲の設定・印刷範囲のクリア
・印刷範囲の設定 ・印刷範囲の取得 ・印刷範囲のクリア


新着記事NEW ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ