パソコン大好き | nekoca.com

パソコンを紹介するサイト

解決!Windows10になったらエクセルマクロが遅い、動かない

time 2019/12/24

解決!Windows10になったらエクセルマクロが遅い、動かない

Windows10になったらエクセルマクロが遅い、動かなくなった、RPAも注意

Windows7がサポート終了

 とても良い出来だったWindows7がサポート期限を迎えます。今でも十分に機能するWindows7がサポート期限がきてしまうなんて、なんだか惜しまれます。

Windows10にしたら、エクセルマクロが遅い、動かなくなった!

 職場でもじわじわと増えてきていたWindows10ですが、このところ「Windows10になったらエクセルマクロが動かなくなった!」「遅くなった!」「なんとかしてくれ!」などの問い合わせが激増しているような気がします。

 従来、マクロで自動で動いていた業務が突然動かなくなると 職場はパニックにおちいり、なんとか残業して処理を終えた人々もいるのではないでしょうか。

謎のオートメーションエラー発生

 自分の体験談ですが、ある日、従来動いていたエクセルマクロが

「オートメーションエラーです。起動されたオブジェクトはクライアントから切断されました。」

などと言う、恐ろしい内容のエラーが発生しました!ガーン!

 何度動かしても適当な小細工をしてようが、どうしてもエラーを回避できない。デバッグでエラー箇所を止めて確認するとマクロでのコピペが失敗しているのだが、何が悪いのか全く分からずじまい。

「どうにもならない・・・」この一言でした。

 このオートメーションエラーが出たエクセルマクロですが、以前は動作する時間が10分程度で終わっていたようなのですが、30分経っても終わらなく、挙句の果てにエラーとなる状況です。

解決!クリップボードの履歴をオフに!

 ネットで検索してもどうにもならなかったのだが、エラーが発生している箇所を調べると、コピペが失敗しているようでした。いろいろと検証を重ねた結果、Windows10の従来のバージョンにはない機能をオフにすると何事もなかったかのようにマクロが動き出しました。

それは・・・

クリップボードの履歴

 そう、この便利機能のおかげでエクセルマクロが実行途中で動かなくなるなどになるんです。唖然。

 クリップボードの履歴ですが、今までのクリップボードはコピーをおこなった最新の一回のコピーが保存されて、他の場所にペーストすると言う便利機能ですが、これが拡張されて一回のコピーだけではなく履歴として一覧に保存が可能になったものです。

 しかしながら、このすごく便利な機能が足を引っ張った形です。

 このクリップボードの履歴機能をオフにする事で、エクセルマクロが遅くなってしまう、落ちてしまうと言う現象を回避できる事がわかりました。

※回避可能な条件としては、今まで使っていた古いWindowsをWindows10にした場合となります。

クリップボードの履歴をオフにする

クリップボードの履歴をオフにする方法は簡単です。

1.デスクトップ左下の「Windows」マークをクリック

「Windows」マークをクリック

2.「設定」(歯車マーク)をクリック

「設定」をクリック

3.「システム」をクリック

システムをクリック

4.開いたウィンドウの左側のメニュー「クリップボード」をクリック

左側の「クリップボード」をクリック

5.「クリップボードの履歴」をオフにする

クリップボードの履歴を「オフ」にする。(画像の状態にする)

以上で、ほぼほぼ解決できると思います。

検証1:クリップボードの履歴をオンとオフでスピードの比較

 Windows10でのエクセルマクロが遅いとの事のお問い合わせも多くありました。筆者自身でもその事は感じておりましたので具体的に検証を行いました。

 テストはシート1を100シートコピーするサンプルで行います。

 実際に動作を検証したい方は、新規のエクセルファイルを作成してコードの編集画面に下記のコードを張り付けてください。

Option Explicit

Public Sub SheetCopy()
    Dim intLoop As Integer
    Dim strMsg As String
    Dim datStart As Date
    Dim datEnd As Date
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
   
    datStart = Now
    
    For intLoop = 1 To 100
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        
        With Worksheets(1)
            .Activate
            .Range("A1:AX20000").Copy
        End With
        
        With Worksheets(Worksheets.Count)
            .Activate
            .Range("A1").PasteSpecial Paste:=xlPasteValues
        End With
    Next
    
    datEnd = Now
    
    strMsg = "Start: " & Format$(datStart, "hh:nn:ss") & "   End: " & Format$(datEnd, "hh:nn:ss")
    
    Call MsgBox(strMsg)
    
    Debug.Print strMsg
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
End Sub

 100シートをコピーするマクロですが、2度の目実行の時にはコピーした100シートを消すか、新しいエクセルにコードをコピーしてください。

 ※当初、不要シートを削除するようにしておりましたが、事故防止のためにその部分は削除しました。

クリップボードの履歴をオン

一度目は「クリップボードの履歴」をオンにして実行します。

クリップボードの履歴オン
クリップボードの履歴オン

クリップボード履歴での結果

Start: 10:53:00 End: 10:54:03

1分3秒でした。

クリップボードの履歴をオフ

二度目はクリップボードの履歴をオフにします。

クリップボードの履歴をオフ
クリップボードの履歴をオフ

クリップボードの履歴をオフでの結果

Start: 10:57:59 End: 10:58:08

9秒となりました!

クリップボード履歴をオンにするとマクロが遅くなるのが確定!

クリップボードの履歴がオン:63秒

クリップボードの履歴がオフ:9秒

比較にならないほどのスピード差となりました。

  • Copy
  • PasteSpecial

 ステップ実行すると、これらが遅い印象ですので、これらを使っているマクロは遅いと思って問題ないでしょう。

 PasteSpecialはもともと早い処理ではありませんので、スピードを速くしたいのであればRangeオブジェクト上でデータのコピーを行うべきなのかもしれませんけど、PesteSpecialを使う必要が出てくる場面もあるかと思います。

 その場合はクリップボードの履歴はオフとしましょう!

 ただ、処理が正常に終了された場合にクリップボードの履歴オン、オフでの違いはありません。マクロの専用パソコンで運用している場合はクリップボードの履歴はオフとしましょう。

 また、最近はRPAと言うロボットが流行っているのだが、そちらも例外ではなく、条件によってはRPAについても同様に動作速度が遅くなったり、場合によっては動作が異常停止してしまう可能性があるので、エクセルマクロと同様に気を付けていただきたい。

検証2: クリップボードの履歴をオンとオフでメモリ使用量の違い

 さらに気が付いた事がありました。

 クリップボードの履歴がオンになっている場合、スピードが遅いだけではなく、メモリの使用量がクリップボードの履歴がオフになっている場合とくらべてメモリの使用量が違いました。

 検証1で使用したマクロをクリップボードの履歴をオン、オフでそれぞれ実行しメモリの使用量を確認しました。

左がクリップボードの履歴オフ、右がオン

 左のクリップボードの履歴オフでメモリ使用量が一時減っているのは、シートの削除が動いてしまっているところですが、その後にメモリ使用量が増えることもなくすぐに処理がすぐに終わっています。

 右のクリップボードの履歴オンの場合ですが、メモリの使用量が一瞬増えて、減ってを繰り返している様子です。マクロでコピーした瞬間にクリップボードの履歴機能が働いているところになると思われます。

 しかも、オンの場合は全体的に右肩上がりにメモリ使用量が増えていっている雰囲気です。

 右肩上がりにメモリが増えているのは単にエクセルのシートが増えているからという可能性もあります。しかしながら、クリップボードの履歴がオフの場合にはそのようなメモリの増え方はしていない!

右肩上がりにメモリ使用量が増えている

 メモリを使用しているプロセスの状態です。

 ※メモリの使用量がはっきりとわかるようにコピーする範囲を広げて検証を行った結果です。

クリップボードユーザーサービスとサービスが動いている

 マクロ使用中はスクリーンショットが取れないので、カメラで画面を撮影したものになるのですが 、タスクマネージャーのプロセスを確認すると「サービスホスト クリップボードユーザーサービス」と言うプロセスが動いている。

 このサービスのメモリ使用量が、前述したメモリ使用量の増加になっていると考えられる。

 瞬間でメモリの使用量は変化していくのだが、1ギガバイト越えのメモリ使用量を記録した瞬間になります。

 しかも、CPUの使用率をみると、エクセルが3.9%のリソースを使っているのに対して、クリップボードユーザーサービスは23.6%と、エクセルの何倍ものCPUリソースを使っている。

 これは単にCPU、メモリが無駄に使われ、当然だが電力使用量も増えているのでエコではないという事です。

 世界中でこのように無駄にリソースが何倍も使われているんだと思ったら、絶対にクリップボードの履歴はオフにして動かす必要があるのではないでしょうか。

検証3(検証中):クリップボードの履歴をオンにしたときにマクロが落ちる

 クリップボードの履歴をオンにした場合にマクロが遅くなる事は間違いないという事が、検証1、検証2でハッキリとしたのだが、マクロが落ちる現象については検証結果が出ていない。実際に落ちて動かなくて困っていた人がいたわけですので、落ちる条件があるはずなのです。

 検証1、検証2でのコピーをする範囲を更に拡大してマクロを実行すると確かにメモリ使用量が増加し、OSがメモリスワップをしてしまいものすごい負荷になりパソコンが半端がないほどギリギリな状態になってしまう状態になる事はわかっています。

 しかしながら、実際に落ちてしまったエクセルマクロは、ここまでひどい条件のマクロではなかったため、この厳しい条件のマクロが落ちてしまうものとは条件が違いすぎる。

 落ちたエクセルマクロは、複数の区分があるデータで、その区分ごとにコピペを行うようなマクロでしたので、このような形のエクセルマクロを組んで試してみるしかないのではないかと考えています。

 ですので、この記事での課題として検証を進めていきたいと思っています。

コスパが凄い!お勧めのパソコン

Intel版

Lenovo IdeaPad S540 Intel

AMD版

Lenovo IdeaPad S540 AMD

管理猫について

茶トラ

茶トラ

管理猫の茶トラです!

アマゾン