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

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

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

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

解決策１：Form Control ボタンを使う

フォームコントロールのボタンからマクロを実行するとなぜか改善されます。詳細は下記のサイトに掲載されています。

Excelにて図のリンク貼り付けされているブックが開かれているとマクロが遅くなる

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

解決策２：リンクを無効にする

マクロ実行中だけ一時的にリンクを無効化する方法です。

Performance of linked pictures – Daily Dose of Excel

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

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

Excel2007で、図のリンク貼り付けを使用したブックを開いた状態でマクロを実行すると処理が遅くなる – マイクロソフトコミュニティ

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

比較

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

検証

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

Option Explicit

Dim 画像 As Picture
Dim シート As Worksheet

Private Sub CommandButton1_Click()
    Call メイン("ActiveX Control")
End Sub

Private Sub 前処理()
    Set シート = Sheet1
    Set 画像 = シート.Pictures("リンクされた画像")
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
End Sub

Private Sub 後処理()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Sub 実行()
    If TypeName(Application.Caller) = "String" Then
        メイン ("Form Control")
    Else
        メイン ("直接実行")
    End If
End Sub

Sub メイン(ByVal 呼び出し元 As String)
    Debug.Print ("Caller: " & 呼び出し元)

    Call 前処理
    On Error GoTo 異常終了

    Call リンクありなし比較
    
    Call 後処理
    Exit Sub
異常終了:
    Call 後処理
    MsgBox Err.Description
End Sub

Private Sub リンクありなし比較()
    ' リンク解除
    画像.Formula = ""
    Call テスト("リンクなし")
    
    ' リンク設定
    画像.Formula = "$B$2"
    Call テスト("リンクあり")
End Sub

Sub テスト(ByVal テスト名 As String)
    Dim 開始時刻 As Double
    開始時刻 = Timer
    
    Dim i As Integer
    For i = 1 To 100
        シート.Cells(1, 1).Value = "1"
    Next
    
    Debug.Print (テスト名 & ": " & ((Timer - 開始時刻) * 1000) & " msec")
End Sub

ちょっとコードが長くなってしまいましたが、実行した結果が以下になります。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）

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

Sub テスト(ByVal テスト名 As String)
    Dim 開始時刻 As Double
    開始時刻 = Timer
    
    シート.Cells(2, 1).Value = ""

    Dim i As Integer
    For i = 1 To 100
        シート.Cells(1, 1).Value = "1"
    Next
    
    Debug.Print (テスト名 & ": " & ((Timer - 開始時刻) * 1000) & " msec")
End Sub

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