マクロの処理が遅くなった

Question

野球の投打の成績がエクセルの集計表の形でまとめられています。４０～５０チームがあり、トーナメントですので、出場４５チームなら４４試合で、エクセル集計表が４４個になります。
大会が終わるとその総合成績、個人の投打成績やチーム成績を出しています。
その最初のエクセル操作の手続きとして、４４個の試合ごとエクセルを順番に開いて、総合成績を出すためのエクセルにコピペしています。
操作は同じことの繰り返しのため、マクロを作りました。
一つ目のファイルオープン→コピペなど→ファイルクローズ、
二つ目のファイルオープン→コピペなど→ファイルクローズ、
…４４個目のファイルオープン→コピペなど→ファイルクローズ
といった感じです。マクロに多少の項目の追加変更はあっても基本同じで、１０年以上前から運用していますが、パソコンを買い替えると、そのマクロが急に遅くなりました。感覚的ですが１～２分で終わっていたのが、２０分かかりました。ただ、２０分と言っても大会の途中で試合数（エクセル集計表の数は２０個）ですので４４個になったら単純に倍の４０分程度かかるのかなと推測です。
ネット検索で、マクロの処理が遅いのは、プログラムの作り方の問題との記載があり、それなりに経験もしました。
今のパソコンは起動、普通の入力操作は、決して遅くありません。

　cellに値を入れるのも、一つずつ入れるのではなく、配列を用意して配列から、cellに範囲を決めて入れること。

    Application.ScreenUpdating = False  '画面表示固定
    Application.ScreenUpdating = True  '画面表示固定解除
を命令の前後に入れることで画面の動きの手続きがなくなり早くなる。（これは既に組み込み済み）

    Sheets("Sheet1").Select
みたいな、シートを変える手続きを繰り返しの中に頻繁に入れないこと。

　今回、Excelファイルを閉じる際に、メモリ解放することのアドバイスが書かれた
https://qiita.com/AkiUnleash/items/fc8f783a9428211e91b2
のURLも見つけ、「DoEvents」というメモリ解放する命令を見つけ試行しました。
が、早くなりましたが、時間にすると半分ですが１０分かかりました。それでも遅いです。

　１～２分の処理時間が１０分も掛かるようになってしまったのは、エクセルの仕様が変わったのでしょうか。今のエクセルは、「Microsoft Office Home and Business 2021」でした。少なくともこれまで使ってきた１～２分で処理するパソコンはVista以降です。Windows8、Windows10です。エクセルのバージョンは分かりませんが。

　マクロの一部ですが、以下にコピペしました。

　他に何かプログラムを工夫すれば良いのか、パソコンの設定の問題なのか、少しでもわかる方、ご助言下さい。

Sub 集計表から取込作業()
    Worksheets("ファイル名").Cells(2, 8) = Time$
    Dim セル値(113, 85)
    Dim 背番号対応(2, 20, 20) '1:先攻､2:後攻
    Dim 登板(30)
    Application.ScreenUpdating = False  '画面表示固定
    p = 1
    j = 2
    j2 = 2

    Do Until Worksheets("ファイル名").Cells(p, 1) = ""
    Sheets("貼り付け").Select
    Range("E1:AY112").Select
    Range("E112").Activate
    Selection.ClearContents
    
    ドライブ元 = ThisWorkbook.Path
    ファイル名 = Worksheets("ファイル名").Cells(p, 1)
    読込ファイル名 = ドライブ元 & "\" & ファイル名
    Workbooks.Open Filename:=読込ファイル名
    For x = 1 To 113
        For y = 1 To 85
            セル値(x, y) = Cells(x, y)
        Next y
    Next x
    ActiveWorkbook.Close
    
    'Close待機
    DoEvents

    For x = 1 To 113
        For y = 1 To 85
            If セル値(x, y) <> "" Then
                Worksheets("貼り付け").Cells(x, y) = セル値(x, y)
            End If
        Next y
    Next x

（途中、だらだらと長いので省略）

    p = p + 1
    Loop
    Worksheets("ファイル名").Cells(3, 8) = Time$
    Application.ScreenUpdating = True  '画面表示固定解除
End Sub

fujillin · Accepted Answer

No2です。

＞ちなみに一試合のエクセルファイルの容量は56KBです
その程度の容量であれば、ごく一般的であろうと思われます。
ちなみに当方の環境であれば、そのくらいのファイルをOpenするのには1秒かからない程度の時間で済みます。
（環境依存なので一概には言えませんけれど）

＞ここもセル以外の配列を作って、その配列から一括して
＞代入するように書き換えれば早くなると思いますが
No1に書きましたけれど・・・
質問文にご提示の処理は1セルずつ処理する方式なので、配列を使ってはいても、処理速度の観点から言うと決して早くはありません。
むしろ配列を使わない方が速い可能性もあります。
（メモリ上でのアクセスなので、メモリ不足でない限りは、差は微々たるものですが・・）

「配列を使う」ことが速度向上の本質ではありません。
速度向上に関して、このあたりの意味を理解なさっていないのではないかと懸念されます。
具体的な方策についてはNo1に述べた通りなので、省略します。

googoo900 · Answer

パソコンを再起動してはいかがでしょうか？
「高速スタートアップ」が有効になっている可能性があります。
※再起動時には「高速スタートアップ」になりません。

Windowsの設定で「高速スタートアップ」が有効になっていると、実際にはシャットダウンされずメモリ上の一時ファイルや不要なプロセスがクリアされておらず、遅くなる原因になります。
どちらかというと、メモリの内容がストレージに保存される『休止状態』に近い状態で電源オフになります。

◆「高速スタートアップ」の無効化方法
スタート
⇒設定
⇒システム
⇒「電源とスリープ」
⇒「関連設定」欄から「電源の追加設定」
⇒「電源ボタンの動作の選択」
⇒「現在利用可能ではない設定を変更します」をクリック
⇒「シャットダウン設定」欄の「高速スタートアップを有効にする」のチェックを外して、「変更の保存」をクリック

tatsumaru77 · Answer

シート内に関数式が埋め込まれているなら、シートの更新ごとに自動計算を行い、その結果遅くなります。

以下を試してみてはいかがでしょうか。
①マクロの先頭に
    Application.Calculation = xlCalculationManual
を追加し
②最後に
    Application.Calculation = xlCalculationAutomatic
を追加します。

①で自動計算を手動計算にし（自動計算を行わないようにする）
②で自動計算に戻しています。

マクロ実行中に、異常停止などがあると、手動計算のままになります。
その場合は、自動計算に戻しておいてください。
自動計算／手動計算については、下記URLが参考になるかと思います。
https://offisu-soft.com/post/detail/1336
https://www.isa-school.net/blog/office-excel20/

シート内に関数式が埋め込まれていない場合は、無視してください。

fujillin · Answer

No1です。

連投すみません。

書き忘れましたが、データファイルを順にOpenしてゆく処理なので、これにかかる時間の分は短縮できません。
手動で1ファイルを開く際にどのくらいの時間がかかっているでしょうか？

例えば、重いファイルで開くのに1分かかるのなら、概ね「1分×ファイル数」だけの時間は最低でもかかってしまうことになるので、マクロの処理を効率化しても全体としての効果はあまり期待できないことになってしまいます。

ご質問文を読むと、旧い時代から継続的に利用なさっているようですが、旧いエクセルでは、使用しているうちにゴミが溜まって肥大化するという欠点がありました。
（現在は改善されているようですが）
もしも、ずっと継続的に利用なさっているデータファイルの場合は、一度ファイルの容量を調べてみてください。
肥大化している様なら、必要部分だけを新しいファイルにコピペすることでスリム化が可能と思います。
データの内容にもよりますので一概には言えませんけれど、A1:CG113に固定値が入っている程度のファイルなら、せいぜい100kB前後であろうと推測できます。

fujillin · Answer

こんにちは

遅くなった理由はハッキリとはわかりませんけれど、環境の違いでしょう。
CPUの能力差や、エクセルで使える空きメモリの差などの影響であろうと考えられます。

一方で、
＞cellに値を入れるのも、一つずつ入れるのではなく、
＞配列を用意して配列から、cellに範囲を決めて入れること。
ご提示の方法では、結局1セルずつ処理しているので配列を使っても使わなくても同じことですね。
エクセルマクロの場合、時間がかかるのはセル（シート）へのアクセスです。
この回数を減ずることで速度向上を期待できます。

ただし、ご提示の処理だと同じシートに順にコピペして行っているので、シートに関数等が設定されていて、データの値に基づいてその計算結果を更にどこかに集計しているのかと想像しました。
実際のシートの仕組みがどのようになっているのか不明ですけれど、データをコピペしているA1:CG113の範囲内の一部である、E1:AY112だけを毎回クリアしている理由も不明ですね・・
（ご提示のコードだけからだと、不要な処理のように思えます）

また、値を転記する際に
　＞If セル値(x, y) <> "" Then
として、空白値をペーストしないようにしているのは、そこに関数等が設定されているからなのでしょうか？
（その割には、元データに依存と言うのも不可思議ですが・・）
もしそうなら、関数等はA1:CG113の範囲外に設定しておく仕組みにしておく方が賢い方法と考えられます。
そうすることで、A1:CG113をまとめてペーストできるようになりますので。

ご提示の部分の処理に関して言うなら、上記のような仕組みにしておくことで、
　コピー先シート.Range("A1:CG113").Value = _
　コピー元シート.Range("A1:CG113").Value
の1センテンスで処理できるようになりますし、シートへのアクセスも2回で済むので、大幅に速度向上ができるのではと考えらえます。

マクロの処理が遅くなった

No2です。

パソコンを再起動してはいかがでしょうか？

シート内に関数式が埋め込まれているなら、シートの更新ごとに自動計算を行い、その結果遅くなります。

No1です。

こんにちは

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング