【追加質問】複数シートを集計したい(別シートへ集計結果を表示させたい)

Question

前回、下記質問にて№8でtatsu99様にご教授頂き無事解決しました。
その節は、ご教授いただきましてありがとうございました。
↓
https://oshiete.goo.ne.jp/qa/9705836.html

その後、集計元のシートを増やしたり、行数を増やしたりしたことが原因なのか
それまで遅くとも5分以内には処理が終わっていたのですが
10分以上集計処理に時間がかかってしまっております。
恐れ入りますが、高速化する方法はありますでしょうか？
何度も申し訳ありませんがご教授頂けますと幸いです。

tatsu99 · Accepted Answer

>ちなみに、マクロについて知識不足の為ご教授頂きたいのですが
>どの部分(どの式のあとに)挿入すれば良い等、ルールはありますか？

シートの更新を行う操作等の前後に入れれば良いです。
基本的には、
マクロ開始のプロシージャは、
Public Sub XXX()
・・・・・何らかの処理
end sub

のようになっています。
このプロシージャの先頭と終わりに入れれば、問題ないです。

Public Sub XXX()
Application.ScreenUpdating = False '追加①
Application.Calculation = xlCalculationManual '追加②
・・・・・何らかの処理
Application.Calculation = xlCalculationAutomatic '追加③
Application.ScreenUpdating = True '追加④
end sub

提示した例では、
MsgBox ("処理終了")の前に追加③④を入れていますが、
後でも、問題はありません。
（個人的には、画面に”処理終了”と表示されるときには、シートの更新された状態が見えた方が気分が多少良いかと思うので、前に表示しています）

また、
Public Sub XXX()
if 何がしかの判断で、then 
exit sub
end if
Application.ScreenUpdating = False '追加①
Application.Calculation = xlCalculationManual '追加②
・・・・・何らかの処理
Application.Calculation = xlCalculationAutomatic '追加③
Application.ScreenUpdating = True '追加④
end sub

のように、処理に入る前に、終了してしまうケースがあるなら、確実に処理を
行うことが保障された箇所で、追加①②を行った方が良いでしょう。
何がしかの判断は、例として
１．”処理をおこないますか？”のメッセージボックスの応答でCANCELを選択した場合
２．実行前のチェックで、NGになり、そのマクロを終了する場合
などのケースです。

tatsu99 · Answer

多分、前の回答は以下のマクロかと思います。
いかのように追加①～追加④を追加してください。
追加①④が画面表示の抑止と戻し
追加②③が自働再計算の抑止と戻しです。
これは、どのようなマクロにも適用できます。
これで、期待した結果が得られない場合は、基本的にロジックの見直し等が必要になります。
-----------------------------------------------------
Public Sub 合計シート集計()
    Dim gs As Worksheet
    Dim rowg As Long
    Dim row, rowstart, rowE, rowW, maxrow As Long
    Application.ScreenUpdating = False              '追加①
    Application.Calculation = xlCalculationManual   '追加②
    Set gs = Worksheets("合計")
    maxrow = gs.Cells(Rows.Count, "A").End(xlUp).row    '合計sheet A列の最大行取得
    'データクリア
    For row = 4 To maxrow
        gs.Rows(row).ClearContents
        gs.Range("A" & row & ":B" & row).UnMerge        'A,Bセルの結合を解除
    Next
    rowg = 4
    rowstart = rowg
    If Goukei(gs, 1, rowg) = False Then Exit Sub '東日本集計
    rowE = rowg
    Call AreaGoukei("東日本合計", gs, rowstart, rowg)
    rowstart = rowg
    If Goukei(gs, 2, rowg) = False Then Exit Sub '西日本集計
    rowW = rowg
    Call AreaGoukei("西日本合計", gs, rowstart, rowg)
    Call AllGoukei("全体計", gs, rowE, rowW, rowg)
    Application.Calculation = xlCalculationAutomatic    '追加③
    Application.ScreenUpdating = True                   '追加④
    MsgBox ("処理終了")
End Sub

tatsu99 · Answer

1点、使用時の注意事項があります。
マクロが正常に動作し最後まで実行できた場合は、特に問題ありません。
問題は、（通常は発生しませんが）データ不正などの為に、マクロを途中で打ち切った場合です。
そうすると、
Application.Calculation = xlCalculationAutomatic '追加③
Application.ScreenUpdating = True '追加④
の部分が実行されません。
私の調査した範囲では
追加④の部分、画面の自動表示については、マクロが終了した時に、自動的に
Application.ScreenUpdating = Trueの状態になるので、これは問題ありません。
問題は、追加③の部分です。
Application.Calculation = xlCalculationAutomatic　これが、実行されないと、
自動再計算が手動計算の状態になったままになります。

その場合は、
「数式」のタブをクリックし、計算方法の設定で、自動を選択してください。

試験時に上記のような状態が、頻繁に発生するなら以下のどちらかの対策をすれば良いでしょう。
対策１：予めもとの状態に戻すマクロを作成しておき、それを実行する。
Public Sub 通常状態に戻す()
    Application.Calculation = xlCalculationAutomatic    '追加③
    Application.ScreenUpdating = True                   '追加④（実質的にはこれはなくても良い）
End Sub

対策２：異常が発生時、元の状態に戻してから、終了する。
if エラー発生時 then
　　msgbox("異常発生")
　　end　・・・ここでマクロが強制終了する
end if

上記を
if エラー発生時 then
　msgbox("異常発生")
　Application.Calculation = xlCalculationAutomatic    '追加③
　Application.ScreenUpdating = True                   '追加④（実質的にはこれはなくても良い）
　end　・・・ここでマクロが強制終了する
end if
のようにします。

以上は、試験時に上記のような事態が発生したケースです。通常は最後までマクロが実行されるので
特に気にする必要はありませんが、念の為、記憶にとどめておいてください。

【追加質問】複数シートを集計したい(別シートへ集計結果を表示させたい)

多分、前の回答は以下のマクロかと思います。

>ちなみに、マクロについて知識不足の為ご教授頂きたいのですが

1点、使用時の注意事項があります。

似たような質問が見つかりました

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

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

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