「リンクされた図」があるとマクロが遅くなる問題の対策

Excel でマクロを実行した際、日によって遅くなる時があるなあとぼんやりと考えていたのですが、いい加減時の流れのせいにするのはやめて本腰を入れて調べたところ、どうやらリンクされた図が貼ってあるブックを開いていると遅くなることが分かりました。

検証したところ「リンクされた図」は、マクロと同一ブック内にあっても全然関係ない別ブックにあっても実行速度が遅くなります。倍くらいになるなら我慢できるのですが、普段なら数秒のマクロが分単位になるので対策を立てたいところです。
Application.ScreenUpdating = False を書き忘れたのかと思うくらい遅くなります。

Excel に関して調べる際、情報量が多すぎて検索結果が荒れがちです(内容の重複が多い、重要な情報が下位に行ってしまう)。結構手間がかかりましたが、3つほど良い情報を見つけたのでまとめます。

解決策1:Form Control ボタンを使う

Excelにて図のリンク貼り付けされているブックが開かれているとマクロが遅くなる
https://social.msdn.microsoft.com/Forums/ja-JP/92acf5ce-fc66-404a-82c4-f59d7e5a0a93/excel123951239022259123981252212531124633602812426201841236912373?forum=vbajp

VBA を直接実行したり、ActiveX コントロールのボタンから実行すると遅くなるのですが、フォームコントロールだと改善されます。

解決策2:リンクを無効にする

Performance of linked pictures – Daily Dose of Excel
http://dailydoseofexcel.com/archives/2010/05/12/performance-of-linked-pictures/

VBA で 図.Formula = "" を設定するとリンクを無効化できるのですが、上記サイトでは数式と名前の定義を上手く組み合わせて ON OFF が簡単に切り替えできるよう考えられています。PicsOnという名前を用意し、数式のIFを使ってそれが1なら範囲を返してリンクされた図に、0なら空を返してリンクを切るという仕組みです。図の Formula には直接 IF などの数式を指定できないので、IF(~) を名前の定義で登録してから指定しているのも重要です。

解決策3:警告を表示し、ブックを閉じてもらう

Excel2007で、図のリンク貼り付けを使用したブックを開いた状態でマクロを実行すると処理が遅くなる – マイクロソフト コミュニティ
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_excel-mso_other-mso_2007/excel2007%E3%81%A7%E5%9B%B3%E3%81%AE%E3%83%AA/57195b92-3185-490e-9431-fb27002e4fac

上記ページの回答にコードがあるのですが、ユーザーに警告を出すパターンです。自動化できないなら手動で対応してもらいましょう。

比較

解決策1が簡単で確実ですが、フォームコントロールのボタンを使えない場合は解決策2か3になると思います。解決策2の場合、開いているブックすべてに適用する必要があるので、他のブックを書き換えたくない場合は採用できません。解決策3を使用して、ユーザーにリンクされた図があるブックを閉じてもらいましょう。

検証

検証のため、簡単にリンクされた図を配置したシートと、100回ひたすらA1に1を書き込むプログラムを書きます。

ちょっとコードが長くなってしまいましたが、実行した結果が以下になります。Form Control が圧倒的好成績です。

Caller: 直接実行
リンクなし: 7.8125 msec
リンクあり: 1644.53125 msec

Caller: Form Control
リンクなし: 3.90625 msec
リンクあり: 3.90625 msec

Caller: ActiveX Control
リンクなし: 3.90625 msec
リンクあり: 1636.71875 msec

その他

ちなみに、以下はこの問題に対して効果がありませんでした。 ScreenUpdating が効かないのはバグな気がします。また、シートを非表示にしても効果ありません。

  • ThisWorkbook.DisplayDrawingObjects = xlHide
  • Application.ScreenUpdating = False
  • Application.Interactive = False
  • Application.Calculation = xlCalculationManual
  • Application.PrintCommunication = False

リンクされた図について英語で検索する場合は linked picture, picture link, camera tool で関連記事が出てきます。

Form か ActiveX か、呼び出し方で結果が変わるので、以下のような関数を文字列で引数に渡して実行する方法も試しましたが効果ありませんでした。

  • Application.OnTime
  • Application.Run
  • Evaluate

リンクされた画像をリンク元の範囲と重ねると video feedback ループ状態になるかと思って試したら、1回分しかループしませんでした。ただし再描画が走ったタイミングで繰り返し反映されるので、その状態で上記の検証ソースを走らせると遅い理由が視覚的にわかります。

検証に使用した Office は Office 365 のバージョン 1906 (32bit 版) です。

追記(2019/7/23)

解決策1の Form ボタンの方法ですが、以下のパターンだと無効になることが分かりました。上記検証コードの「テスト」関数に、空文字を代入するコード シート.Cells(2, 1).Value = "" を入れると、 Form ボタン無しと同じ速度になってしまいます。

この問題は、空文字を入れるセルの書式を「文字列」にすると回避可能です。

One Reply to “「リンクされた図」があるとマクロが遅くなる問題の対策”

  1. 助かりました。
    VBAの処理速度が、使い物にならないくらい遅くなってしまい、原因が分からず、いろいろ探したところ、ここにたどり着きました。
    ドンピシャ!あたりーでした。
    「リンクされた図」を消したら、瞬時で処理が終了しました。
    感動!!!!!
    ありがとうございました。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください