VBAでExcelの「VLOOKUP」や「SUMIF(S)」「COUNTIFS」などのように、大量のデータを処理すると動作が重くなり、実行時間がかかってしまったり、フリーズしてしまう場合があります
Excel VBA高速化の一つに「Application.Calculation = xlCalculationManual ‘ 手動計算にする」を入れて処理中に計算を自動実行させない方法をよく使います。でも、全く計算させないでおくわけにはいきませんよね。計算させないと結果は出ませんから(^^;
マクロ実行中だけでなく、一般のデータ入力などの処理中でも重い計算式がたくさん設定されている場合、再計算が頻繁に繰り返されてしまい、思うように作業が進まないことって無いですか？
今回の記事は、そんな「重い」悩みを解決する方法をご紹介します！
【この記事でわかること】
・「重い」計算式は「値」に変換して「計算式」をなくしてしまい「軽く」します
・「計算式」などは設定シートに保存しておくので再入力の必要はありません
・【汎用】で使えるツールとして解説します
記事で使用したサンプルファイルはこのリンク先からダウンロードできます
Excelの「VLOOKUP」など重い関数について
今後の記事内容について【高速化した「VlookUp関数」のVBAでの活用法を再検討してみたいと思います】としていました。今回の記事はその関連でもあります
Excel2016で劇的に変わった
Excel のパフォーマンス: 計算パフォーマンスの強化でExcelはワークシートの拡大と「計算速度の強化」等が図られたおかげで、Excelのシート上での関数処理の高速化が図られました。でも、VBAでの高速化は置き去りにされているのです
実際にテストしてみればわかりますが、VBA上での関数処理は、Excelワークシート上で実行した速度に負けてしまいます
その他にも、色々な点で変更が加えられている「Excel のパフォーマンス: 計算パフォーマンスの強化」は一度見ておくことをおすすめします
ワークシート上での関数処理の問題点
Excelワークシート上での関数処理速度については、それ以前のバージョンのExcelと比較してみればその速度の差は明らかです。では、いままでVBAコード内で処理していた部分を、ワークシートでの計算に変更したら良いのではと単純に考えますよね
でも、問題点がまだあります。それは、ブックのファイルサイズが肥大化する問題です。ワークシート上に「重い計算式の関数」を書いた場合、その文字数はファイルサイズに大きく影響します
また、ワークシートの計算式の「再計算処理」も作業能率上の大きな問題点と言えます
Excel VBAで計算の終わった数式は値に変換しよう
問題解決には計算式を無くすこと
そうです！「ファイル肥大化」「再計算」などの問題を解決するには「計算式を無くす」のが良いのではないでしょうか(^^;
でも、計算はしないといけません(^^ゞ
じゃあ、計算してから「計算式」を消しちゃいましょう！
では、どうしたらよいかロジックを考えます
設定シートを用意します
・上記画像のとおり「setting」シートを用意します
・画像のメッセージボックスは50万行のデータから10万件を取得した結果です
・2秒で完了しています
・Sampleでは実際に計算するシートと集計元のデータシートがセットされています
・計算式は範囲一番上の実際の「計算式」を文字列でコピーしてセットしておきます
【汎用】Excel VBAで数式一括入力し値に変換するコード
コードはこれだけです
Option Explicit
'計算式一括貼付け後値に変換
Sub SetFomulaToValue()
Dim OutputRange As Range '出力範囲セット用
Dim i As Long, n As Long 'ループ用
Dim shName As String 'シート名用
Dim strKey As String 'KEY列用
Dim startCells As String '開始セル用
Dim endCells As String '最終セル用
Dim setFormula As String '計算式用
Dim tgsh As Worksheet 'シートオブジェクト
Dim rnKey As Range 'KEY開始セルRange
Dim tgCol As Long '列値保存用
Dim tgRow As Long '行値保存用
Dim endRow As Long '最終行保存用
Dim starttime, endtime '実行時間計測用
starttime = Time '開始時刻計測
'設定数の最終行取得
i = Sheets("setting").Cells(Rows.Count, 1).End(xlUp).Row
For n = 3 To i '設定数分のループ処理
With Sheets("setting")
shName = .Cells(n, 1) '対象シート名
strKey = .Cells(n, 2) '検索対象KEY列
startCells = .Cells(n, 3) '開始セル
endCells = .Cells(n, 4) '最終セル
setFormula = .Cells(n, 5) '計算式文字列を変数に格納
End With
Set tgsh = ThisWorkbook.Worksheets(shName)
Set rnKey = tgsh.Range(startCells)
tgCol = rnKey.Column '開始セルの列番号
tgRow = rnKey.Row '開始セルの行番号
If endCells <> "" Then '最終セルの判定
endRow = Range(endCells).Row '指定がある場合はその行
Else '指定がない場合はKEY列の最終行
endRow = tgsh.Cells(Rows.Count, strKey).End(xlUp).Row
End If
With tgsh '計算式貼付け範囲をセット
Set OutputRange = .Range(.Cells(tgRow, tgCol), .Cells(endRow, tgCol))
End With
OutputRange.Formula = setFormula '計算式を一括貼り付け
OutputRange.Value = OutputRange.Value '計算結果を値にする
Next
endtime = Time '終了時刻計測
endtime = endtime - starttime '処理時間計算
MsgBox "計算が終わりました！処理時間は：" & _
Minute(endtime) & "分" & Second(endtime) & "秒でした！"
End Sub【コード内にコメントを入れているので参照願います】
・「20~21行目」計算式の設定数分ループさせるためカウントしています
・「33~37行目」で最終行の指定があるかどうか確認して、指定がない場合は最終行を自動取得するようにしています
・「39行目」で計算式をセットする範囲を「OutputRange」オブジェクトをセット
・「41行目」で計算式を一括書き込みしています
・「42行目」が計算式の計算結果「Value」を上書きで書き込んでいます
・上書きされた結果「計算式」は消えてしまいます
シートの計算式を全部値にしてしまうコード
Sheet1.Cells.Value = Sheet1.Cells.Value・計算式を値に置き換えるには、手動で行う場合「コピー」→「値の貼り付け」で行います
・マクロで行う場合は計算式の結果の値を取り出して、その取り出した値をそのまま上書きしてしまえば良いので「***.Value = ***.Value」としています
・ この際に注意しなければいけないのが、「自動計算」のままにしておくか、「手動計算」にしている場合は「再計算」を行なう必要があります
まとめ（おわりに）
まとめと感想など
・Excel2016からワークシート上の「計算速度の強化」が図られて速くなっています
・だが、VBAでApplication.WorksheetFunctionで実行する「計算速度」は従来どおり
・重い計算式の問題点として「サイズ肥大化」と「再計算」による低速化があります
・その解決策は「ワークシート上で計算実行」させ「値に変換」すれば良いのです
・ExcelVBAで「計算式の一括貼付け」「計算結果を値貼付け」の処理を汎用で使えるように設計してみました。是非試しに使ってみてください(^^)/
・記事内で紹介したコードの実行は、必ずバックアップを取ってから行ってください
・VBAは実行後にファイルを保存してしまうと元に戻すことはできません！
・実行後にファイルを保存せず終了すれば、実行前に戻すことができます！
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
今後の記事について
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
是非！サンプルファイルをダウンロード出来ますのでそのまま使ってみてください(^^)/
※今回のサンプルにはダミーのサンプルデータが入っています
【今後の記事内容はどうしようかなぁ・・・】
・実務に役立つものを提供できるよう現在検討中です
・その他雑記的に「小ネタなどいろいろ」・・・・・
・今後の記事にご期待ください(^^)/
