マクロ処理速度Excel10とExcel19では８倍異なる

Question

Excel 10（win7)で作成したマクロをExcel 19(win10)で実行すると従来２分だった処理時間が１６分もかかりました。マクロにリンクしたフォームボタンからではなくてコード画面から実行すると従来通り２分でした。試みにExcel 10(win7)で再度動かしても変わらず２分です。

解決方法ご教示ください。

Answer 1

もう少し詳細なバージョン情報を提示できますか?

Excel 19 という製品はまだ無いはずです。2019 のことでしょうか。
もしそうだとすると、Excel 10 というのも 2002 なのか 2010 なのか曖昧です。

• Excel 9.0 → Excel 2000
• Excel 10.0 → Excel 2002
• Excel 11.0 → Excel 2003
• Excel 12.0 → Excel 2007
• Excel 14.0 → Excel 2010
• Excel 15.0 → Excel 2013
• Excel 16.0 → Excel 2016/Excel 2019/Office 365

また、漠然と「マクロ」と言われても、マクロシートなのか VBA なのか不明ですし、
そもそもどのようなコードが呼び出されているのかも示されていないため、
見直すべき点や調査すべき点を予測することが困難です。

まずはマクロ内のどの処理が特にボトルネックになっているのかを
探るところから始めてみては如何でしょうか。

Answer 2

ご検討いただけるのに正確さに欠けて申し訳ありません。Excelを起動したときの表示はExcel 2010とOffice2019です。

プログラムはＡ，Ｂ２つの商品売上を記載したシートから売上シートに合計するだけです。

行方向（縦軸）に販売店、列方向（横軸）に前年度売上、予算、今年度売上、その他２つの項目が５列ごと１２か月分並んでいる表にて前年度売上、予算、今年度売上の商品Ａ、Ｂを足している単純なものです。商品のカテゴリーが沢山あるのでシートは５０枚ほどあります。

コードの一例は以下の通りです

Sub 計算()

Application.ScreenUpdating = False

e = Sheets("売上").Cells(Rows.Count, １).End(xlUp).Row

  For i = 3 To e

   For h = 3 To 58 Step 5

     Sheets("売上").Cells(i, h) = Sheets("A売上").Cells(i, h) + Sheets("B売上").Cells(i, h)

     Sheets("売上").Cells(i, h + 1) = Sheets("A売上").Cells(i, h + 1) + Sheets("B売上").Cells(i, h + 1)

     Sheets("売上").Cells(i, h + 2) = Sheets("A売上").Cells(i, h + 2) + Sheets("B売上").Cells(i, h + 2)

     Sheets("売上").Cells(i, h + 3) = Sheets("A売上").Cells(i, h + 3) + Sheets("B売上").Cells(i, h + 3)

   Next h

 Next i

End Sub

お願いはExcel 2010では２分で完了するのに同じプログラムでOffice 2019では１６分もかかるのをOffice 2019でも２分になる処理時間短縮方法をお教えいただきたいことです。

ちなみにマクロ登録した図形ボタンで動作させていますが、Office2019でもコード画面（エディター画面）にてSub/ユーザーホームの実行では２分で完了します。

説明が不十分であればご指摘ください。よろしくご教示お願いいたします。

Answer 3

処理の最初に

Application.Calculation = xlCalculationManual

を、最後に

Application.Calculation = xlCalculationAutomatic

を入れると改善しませんか？

もっと高速化したいなら、値をメモリ配列に格納すればよいと思います。

Answer 4

ご教示ありがとうございます。

Application.Calculation = xlCalculationManualとApplication.Calculation = xlCalculationAutomaticを入れて速度を見ました。

Office 2019では入れる前は平均２０分でしたが入れると平均１４分と確かに処理速度は速くなりました。

ただしこれをExcel 2010で処理すると入れる前は平均１分３０秒、入れると平均３８秒で終了します。

問題にしているOffice 2019とExcel 2010では処理速度に大きな違いは相変わらず残っています。

何故なのかを時間をかけて調べていきます。思い当たる原因などありましたらご教示よろしくお願いいたします。

ありがとうございました。

Answer 5

「プログラムの処理にかかる時間の80%はコード全体の20%の部分が占める。」パレートの法則という考え方があります。時間のかかっていない部分をどんなに削減しても効果はほとんどありません。最初に魔界の仮面弁士さんが提案されていますが、時間を要している部分を特定しない限り、時間を削減することはできないと考えるべきです。

Answer 6

ご検討いただけるのに正確さに欠けて申し訳ありません。
Excelを起動したときの表示はExcel 2010とOffice2019です。

2019 のどのバージョンになるでしょうか。月次の Update によって処理時間が変化するような事象もありえるので、もう少し突っ込んだ情報が欲しいです。（Excel 2019 なら [ファイル]-[アカウント] の所から、より細かいバージョン情報を得られます）

コードの一例は以下の通りです

たとえば冒頭の『e = Sheets("売上").Cells(Rows.Count, 1).End(xlUp).Row』ですが：

1) 「Rows.Count」は、どのシートの行数を表しているのかが曖昧になっています。このコードだと、売上シートの行数となることもあれば、それ以外のシートの行数を返すこともあるわけですが…それは意図した動作でしょうか?
いずれにせよ、アクティブなシートは変動することがあるので、コード上ではどのシートのセルであるのかを常に明示することをお奨めします。

2) 変数宣言がされていないように見えます。
原因箇所の切り分けを正しく行うためにも、Option Explicit を付与して、適切なデータ型の変数を事前に設けておくことをお奨めします。

お願いはExcel 2010では２分で完了するのに同じプログラムでOffice 2019では１６分もかかるのをOffice 2019でも２分になる処理時間短縮方法をお教えいただきたいことです。

高速化として思いつくのは：

• Cells プロパティが返す情報は Excel.Range オブジェクトです。既定のプロパティに頼らず、.Value / .Value2 プロパティ等を明示した方が良いかと思います。
• 配列を使って、複数のセル範囲を一括して処理するようにします。

当方の Excel 2016 では、今回提示頂いたコードでは低速化する症状は見られませんでした。
(2019 が手元になかったので、現段階では、本当にエクセルバージョンのみに起因しているのか判断がつきませんが…)

• 第三者が検証できるよう、実験用に単純化したサンプルを提供していただくことはできますか? (再現性のある情報が提示されると、第三者が追加検証しやすくなります)
• 既存ファイルを用いず、投稿いただいたコードを新規ブックに貼って実験した場合にも低速化が再現することを確認できていますか? (再現しないなら、先の投稿に書かれていない別の部分に原因があるのかもしれません)
• フォーラムに投稿したコードではなく、問題が発生しているブックのコードを新規ブックで試した場合はどうですか? (特定のブックでのみ起きる事象なのかの検証です。たとえば、想定していない外部シートへの参照が残っていたりとか、コピペを繰り返しているうちに、条件付き書式が断片化していたりとか…)
• Excel 2019 を利用している他の PC で実行させた場合も、同様に低速化する事象が見られますか? (たとえば印刷設定などがあるシートにおいて、使用しているプリンタードライバーの差によって、速度差が生じるケースを経験したことがあります)
• ループ内の特定の処理だけが、異常に時間がかかってしまっている状態なのでしょうか? それとも、極端に遅い処理は無いけれども、それぞれの実行が 2010 よりわずかに長いことで、ループ回数が増えるほど、その時間差が蓄積されて遅くなってしまっているのでしょうか? (たとえば、0.02 秒程度の僅かな差でも、それが 1万回のループ内で行われていれば、累積で 3 分以上の差となります)
• ループ回数を変化させずに、未使用のワークシートを多数配備すると、処理時間が伸びたりしますか? (シート数が特定の枚数を超えた途端、急激に遅くなる事象を経験したことがあります)
• シートの総数を減らした実験用のブックにて、ループ回数が変わらないようにして処理させた場合、処理時間が短くなることはなく、やはり同じ程度の時間がかかってしまいますか? (ループ回数の問題なのか、シート数やデータ数の量が問題なのかの切り分け)

Answer 7

それほど時間のかかる処理には見えないのですが・・

例えば、Worksheet_Change イベントプロシージャにもコードを書いていますか？

処理の最初に
Application.EnableEvents = False
を､最後に
Application.EnableEvents = True
を入れてみてはどうでしょうか。

Answer 8

詳細に具体的にトライ事項をお教えお頂きありがとうございます。なぜかは不明ですが時間のかかっている処理部分がわかってきましたのでお教えいただいた事項について一つひとつ時間をかけて分析してまいります。

なお２０１９のバージョンは2019　MSO (16.12827.20200)32ビットです。

e = Sheets("売上").Cells(Rows.Count, 1).End(xlUp).Rowは何枚もあるシートの最下段（行）は全て同じなので代表のシートで出していたものです。これも調べる事項とします。

分かった事項内容をもとにまたお尋ねします。お手数をおかけします。ありがとうございました。

Answer 9

時間がかかる原因がわかりました。

頂いたご指摘を参考にどの部分で時間がかかるかを調べているうちに、セルの値の取り方に間違いがあるとわかりました。

すなわち値の入っていないセルを空白「””」と判断し、また計算結果の「０」は表示しないようセルに「””」を入れていました。次が問題の時間がかかった部分です。

e = Cells(Rows.Count, 2).End(xlUp).Row
For h = 6 To 61 Step 5
  For i = 3 To e
   If Cells(i, h - 1) = "" Then
     Cells(i, h)=""
   Else: Cells(i, h) = Cells(i, h - 1) - Cells(i, h - 2)
   End If
 Next i
Next h

それを次のように上記の演算部分の「””」を「０」に変え、および結果が「０」を表示しないようClearContentsにしました。

If Cells(i, h - 1) = 0 Then
     Cells(i, h).ClearContents

結果飛躍的に処理時間が短くなりました。初心者の私を辛抱強くご指導いただいたことに感謝申し上げます。まことにありがとうございました。