VBAのデバッグモードとボタンからの実行で処理速度が１０倍以上変わる？？？

結論だけ知りたい方はこちら

地味に沼にはまったので似たような方がいたときのためにナレッジを残しておきます

ある日のこと

私は社内の業務を効率化することが趣味である。
今回は上司の依頼により貸出物の管理をカレンダーとして可視化するマクロの作成を行っていた。
ボタン一つ押すだけで貸出対象が何日から何日まで貸し出されるかを読み取りその期間に色をつけたカレンダーを作成するというシンプルなものだ。

コード抜粋

Sub OptimizedMode(ByVal enable As Boolean)
    With Application
        .EnableEvents = Not enable
        .Calculation = IIf(enable, xlCalculationManual, xlCalculationAutomatic)
        .ScreenUpdating = Not enable
        .EnableAnimations = Not enable
        .DisplayStatusBar = Not enable
        .PrintCommunication = Not enable
    End With
End Sub

Sub カレンダー反映main()
    ' 最適化モードを有効にする
    OptimizedMode True
    ' カレンダーの色をリセット
    Call ResetCalendarColors
    
    Dim mainWs As Worksheet
    Set mainWs = ThisWorkbook.Worksheets("台帳")

    Dim reqNum As String
    Dim startDay As String
    Dim endDay As String

    Dim targetList() As Variant
    Dim oneDimTargetList() As String

    Dim i As Long
    Dim j As Long

    Dim lastRow As Long
    lastRow = mainWs.Cells(mainWs.Rows.Count, "A").End(xlUp).Row
    For i = 8 To lastRow
    '''
    'ここで配列に貸出物の情報をもろもろ配列に入れ込む
    '''
        Call ColoringDateRange(startDay, endDay, reqNum, filterdList)
        'ここは色塗り用の別関数
    Next i

    ' 最適化モードを無効にする
    OptimizedMode False
End Sub
''''
'色塗り処理の一部抜粋
    With targetSheet
        For Each strTarget In targetList
            pcRow = GetRowNum(targetSheet, strTarget)
            .Cells(pcRow, dayColumn).Interior.ColorIndex = colorNum
        Next
    End With

''''

問題発生

さて、完成しVBE上で実行して正常に動くことを確認できたマクロをボタンに登録し、改めて実行してみた。
なんと遅い！！遅すぎる！！ のだ。
VBE上で実行していた時は体感で押したら１秒もせずに終わっていたのに、ボタンを押してからの実行だと８倍くらいかかっている気がしたのだ。

は？？？ と思ったが何度試しても結果は変わらず。
検索に強いと話題のperplexity.aiを使用してかなりネットの大海を泳ぎまくっても似たような事象を見つけることが出来ない。
OptimizedModeはその試行錯誤の中で生まれた関数である。

VBEからの実行:0.56s
ボタンからの実行:7.52s

検証開始

さてこうなると原因が気になってくるのがエンジニアという生き物ではないだろうか。（少なくとも私は気になってしょうがなかった）

まず私は途中にブレイクポイントを設定し、一度改めてそこで配列の内容などを確認してから続きを実行した。
すると早くなったのである。なぜだ…。私は途方に暮れた。

ここで諦める私ではない。一度デバッグモードに入ってからだと早いし、VBE上でそのまま実行しても早い。ということは何かあるはずだとひたすらに実行を繰り返していた。

原因発見

試行錯誤を繰り返す中でふと気になる事象をまた発見した。
ボタンを押した後マウスを動かさなければVBE上で実行したときと同じくらいの速度で完了するのである。
はて？これはこれでおかしいぞと思ったわけである。
ではボタンの上にカーソルがあるときとボタン上にカーソルが無いときでよく見比べたところ、ボタンの上にカーソルがないときはやたらカーソルがチカチカしていることに気づいた。
ここまで読まれた方はなんとなくもう原因に気づいているだろう。

結論

Application.Cursor = xlWait

これを入れるだけで解決した。
どうやらボタンを押した後にマウスをセルの上に移動させていると、色塗り等の処理が発生するたびにマウスカーソルの変化が発生し、それにより処理が大幅に遅延していたというオチであった。
VBE上で実行するとアクティブウインドウがVBEなのでカーソルの変化はさほど発生せず通常通りのスピードで完了していたようだ。

最終的には

Sub OptimizedMode(ByVal enable As Boolean)
    With Application
        .EnableEvents = Not enable
        .Calculation = IIf(enable, xlCalculationManual, xlCalculationAutomatic)
        .ScreenUpdating = Not enable
        .EnableAnimations = Not enable
        .DisplayStatusBar = Not enable
        .PrintCommunication = Not enable
+        .Cusor = IIf(enable, xlWait, xlDefault)
    End With
End Sub

この一文を付け足すことで事象解決となった。

まとめ

これまで遭遇したことのない事象だったため苦戦したがこれからはこういうこともあると頭において自動化を進めていきたい。