パソコン大好き | nekoca.com

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

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

time 2019/12/24

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

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

Windows7がサポート終了

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

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

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

なかには実行はできるのに「マクロの実行速度が遅くなった!」などのような症状も見受けられます。

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

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

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

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

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

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

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

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

ネットで検索してもどうにもならなかったのだが、コピペが失敗している、と言う事で、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を使う必要が出てくる場面もあるかと思います。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Intel版

Lenovo IdeaPad S540 Intel

AMD版

Lenovo IdeaPad S540 AMD

アマゾン

管理猫について

茶トラ

茶トラ

管理猫の茶トラです!

アマゾン



アマゾン