【VBA】デバックモードと実行時で動作が違う

Question

お世話になっております。

Excel VBAでどうしても解決できない問題が発生しております。

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub enemy_move()


   Dim enemy(4) As Integer
   Dim gocnt As Integer
      
   For gocnt = 1 To 5
      enemy(0) = gocnt
      enemy(1) = enemy(0) + 1
      enemy(2) = enemy(0) + 2
      enemy(3) = enemy(0) + 3

      Cells(1, enemy(0)) = "V"
      Cells(1, enemy(1)) = "."
      Cells(1, enemy(2)) = "."
      Cells(1, enemy(3)) = "V"
   
      Sleep 500
      
      Cells(1, enemy(0)) = ""
      Cells(1, enemy(1)) = ""
      Cells(1, enemy(2)) = ""
      Cells(1, enemy(3)) = ""
   Next
            
End Sub

特に捻りもない「V..V」を右に移動させていくだけのマクロなのですが、
F5で実行すると「..V」の部分が表示されません。

しかし、F8で1行ずつ実行すると、期待の動作で動きます。
これはどのように解決すればよいでしょうか？
お分かりの方がいらっしゃればご教授お願いいたします。

Answer 1

解決済みですか、おもしろそうなので、自分なりに作成してみました。
Enemyのテキストは配列にして、Enemyのセル範囲はRange変数にして、
一気に代入します。
Offsetメソッドで移動させます。

Sub enemy_move3()
    Dim aryEnemy As Variant
    aryEnemy = Split("V . . V")
    
    Dim rngEnemy As Range
    Set rngEnemy = ActiveSheet.Cells(1, 1).Resize(, 4)
    
    rngEnemy.Value = aryEnemy
    
    Dim gocnt As Long
    For gocnt = 1 To 5
        Application.ScreenUpdating = True 'Excel側に画面強制更新の依頼。
        VBA.DoEvents 'Excelが画面更新できるように、処理を手放す。
        Sleep 500
        
        rngEnemy.ClearContents
        Set rngEnemy = rngEnemy.Offset(, 1) '右へ１つ移動
        rngEnemy.Value = aryEnemy
    Next gocnt
    
End Sub

Enemyを自由に動かしたいなら、クラスモジュールにするといいかも。

Comments

[Masalu_Kudou]

同じ目的でも、ソースの書き方が若干変わってきますね。
私は正直Offsetが得意でないので勉強になります。ありがとうございます。

[imihito]

実際書くのなら、自分もこちらの方が書き方としては好みです

徐々に移動する感じを出そうとすると、1個ずつ代入するのも有りな気はしますが

Answer 2

Excel内のVBAから処理を実行した場合、「enemy_moveというひとかたまりの処理を実行している」と判断され、enemy_moveの処理が優先となり、Excel側の画面の更新の頻度などが低下します。

画面の更新頻度が下がるため、セルに値を設定した結果が画面に反映されにくくなります。

対してデバッグモードでは、1行ずつ処理が止まるため、処理が止まっている間にExcelが画面を更新し、意図通りの表示が得られます。

解決策としては、DoEventsなどでVBA側から処理を一旦手放して、Excel側が画面を更新できるタイミングを作る方法がよく知られています。

私の環境(Win10 Excel Office 365 64bit)では、Sleepの前に以下の2行を入れることで、期待されている動作をしました。

Excel.Application.ScreenUpdating = True 'Excel側に画面強制更新の依頼。
VBA.DoEvents 'Excelが画面更新できるように、処理を手放す。

---

以下、処理内で気になった点です。

配列の大きさ

Dim enemy(4) As Integer

上記のように配列を宣言した場合、enemyの要素数は0から4の5個となります(C言語などとは異なる)。

個人的には、以下のように下限を明示した方がわかりやすいと感じています(VBScriptでは使えませんが)。

Dim enemy(0 To 3) As Integer

Integer

VBAに限定した話ではありますが、VBAのIntegerは扱える数値の範囲が小さく、Longに比べると処理速度が遅いです。

そのため、IntegerよりLongを使った方が良い場面が多いです。

---

その他いくつか気になった点も統合すると以下のようになります。

Sub enemy_move2()
    'Excel VBA 一般論としてセルを参照するときは親シートを明示しておいた方が良い。
    Dim ws As Excel.Worksheet
    Set ws = ActiveSheet
    
    Dim enemy(0 To 3) As Long
    Dim gocnt As Long
    
    With ws.Cells
        For gocnt = 1 To 5
            enemy(0) = gocnt
            enemy(1) = enemy(0) + 1
            enemy(2) = enemy(0) + 2
            enemy(3) = enemy(0) + 3
            
            .Item(1, enemy(0)).Value = "V"
            .Item(1, enemy(1)).Value = "."
            .Item(1, enemy(2)).Value = "."
            .Item(1, enemy(3)).Value = "V"
            
            .Application.ScreenUpdating = True 'Excel側に画面強制更新の依頼。
            VBA.DoEvents 'Excelが画面更新できるように、処理を手放す。
            Sleep 500
            
            .Item(1, enemy(0)).Value = ""
            .Item(1, enemy(1)).Value = ""
            .Item(1, enemy(2)).Value = ""
            .Item(1, enemy(3)).Value = ""
        Next gocnt
    End With
End Sub

Comments

[Masalu_Kudou]

丁寧でわかりやすい解説を含め、ありがとうございました。
目から鱗が落ちるばかりです。大変勉強になりました。