今回はVBAユーザー必見、応答なしになったExcelでVBAを確実に中断させる方法（仮称：ちゅんちゅん奥義）を紹介します。
皆さんはVBAやマクロの使用中に無限ループをさせてしまって、VBAを中断させることが出来なくて泣く泣くExcelを強制終了させた。なんて経験はありませんか？
実はWindowsの裏技的な方法でVBAを止められる方法があります。
一般的に知られているVBAの中断方法
VBAを中断させたい時は「ESCキーを押す」のが一般的に知られている方法だと思います。
読者の方の中には「Ctrl+Pause/Breakを押す」とか「連打する」とか「長押しする」くらいはネットで検索すると出てくるのでご存知の方も多いかと思います。
しかしこれらの方法は、必ずしも中断することはできないため、何時間もかけて作ったエクセルブックやプログラムを捨てざる負えず、肉体的にも精神的にも大きな損失を被る場合があります。
以前、Twitterで無限ループの時にESCで中断出来る条件が分からない。という話があったので紹介したところ、（当時のフォロワー数的には）大盛況でしたので、意外と知られてないのかな、と思い記事にすることにしました。
@hajime_matsui
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) December 20, 2018
これは私の秘奥義の一つですが、VBAの無限ループ中にデバッグを割り込ませるにするにはコツがあります。
とは言え、わーー！ってなった時に偶然気がついたものなので、他にも発見してる人が居てもおかしくないですが。 pic.twitter.com/CavbfOGBdq
Excelが応答なしになる原因
そもそもExcelが応答なしになる原因は、簡単に言うとExcelとVBAは同一のスレッドで動いているため、VBA実行中はExcelが一切の入力を受け付けなくなるためです。
例えば次のような書き方をすると発生します。
Sub 超長時間ループ1() Dim i As Long For i = 1 To 1000000 Cells(i, 10).ClearContents Next End Sub
短時間であれば応答なしにはなりませんが、Excelの画面をクリックすると数秒後に応答なし状態になります。
この応答なし状態はWindowsが判断して表示するので、自然に応答なし画面になるまでには環境によってかなりの個体差があります。
また、画面の状態も「タイトルバーが応答なしになるだけ」だったり「真っ白」になったり「貼り付けという文字が画面いっぱいに出現」したりと様々です。
応答なしになる前であれば単なるESCで止められますが、応答なしに変わったExcelは止められないことが多いです。
そこで、応答なしを防ぐためによく紹介されるのが、次のようにDoEventsをループの中に記述する方法です。
Sub 超長時間ループ2() Dim i As Long For i = 1 To 1000000 Cells(i, 10).ClearContents DoEvents Next End Sub
しかしDoEventsを高い頻度で実行すると、VBA実行中もExcelをユーザーが普通に操作出来るようになってしまうため、極めて危険な状態となります。
またプログラムの所要時間が大幅に伸びてしまいます。
そこで、適度に応答なしから回復するようにDoEventsの発生頻度をチューニングします。
例えば次のようにループカウンタを元に数回毎に実行したり、タイマで時間を計測して一定時間経過したらDoEventsを実行するようにしたりします。
カウンタ基準は簡単ですが環境によって処理速度が違いますから、不特定多数が使うならタイマを使いましょう。
Sub 超長時間ループ3() Dim i As Long For i = 1 To 1000000 Cells(i, 10).ClearContents If i Mod 30000 = 0 Then DoEvents Next End Sub
追記
APIが使える場合は`GetInputState関数`を使って判定すると良いみたいです。 Declare PtrSafe Function GetInputState Lib "USER32" () As Long` 呼び出し側スレッドのメッセージキューの中に、マウスボタンメッセージまたはキーボードメッセージが存在するかどうかを調べる。
If GetInputState() Then DoEvents
ちなみにベテランの方ならDoEevntsを使う前に、最適化出来る場所がないか模索します。
例えば今回の例で言えば、下記の様にワンステップで書くことが出来ます。
Sub 超長時間ループ4_なんていらなかった() Cells(1, 10).Resize(1000000, 1).ClearContents End Sub
これなら100万行の書き換えも数秒どころか、0.1秒もかかりません。
Excel VBAでは書き方を少し見直すだけで、処理時間が1/100、1/1000になることも珍しくありません。
従って、そもそも遅くなるような書き方は避けて、DoEventsに逃げるのは最終手段であるべきだと考えます。
応答が停止してしまったExcelのVBAを確実に中断させる方法
話しを戻します。
コードを最適化したり、DoEventsを挟んだりして応答なしを防ぐ事はできますが、開発中に誤って無限ループするミスが無くなるわけではありません。
また1回のテストに5分かかるが、開始30秒でミスに気がついて、すぐに中断させたい場合なんてのは非常に多いです。
そんな時に使うのがこの方法
ESCを押したままタスクバーでExcel・VBE・他のウィンドウをランダムにクリックしてください。
数回では止まらない事もありますが、大抵は10回もポチポチすればあっさりと中断できます。
Access、Word、Outlook、ExcelでESCが効かないような場合は、同じようにCtrl+Breakを押したままタスクバーからウィンドウを切り替えて下さい。 一応はこっちのほうが強力です。
まとめ
今回紹介した方法はVBAプログラマなら絶対に知っておくべき技術だと思います。
いつ何時トラブルに巻き込まれて、泣く泣くExcelを強制終了することのないように、いますぐ練習しておきましょう！
そして、上手くいかない事例があったら教えてください！
「上手く行ったよ！」というコメントでも大歓迎です。
私はExcel 2007／2010／2013／2016／Office 365で使っており、これまでに止められなかったループはありません。
ただし、外部プログラムと同期させるような仕組みでExcelが待ち状態の場合は、外部プロセスを強制終了させないことにはVBAの中断は無理ですのでご注意ください。
以上、ちゅんちゅん奥義の紹介でした！
追記
- Windowsの設定で「タスクバーボタンを結合する」に設定していると、タスクがグルーピングされてしまうため、この方法は使いにくいかもしれません。使う人は設定を変えておきましょう。
- Alt+TabからのESCでも出来るよ！と情報を頂きました。確かに中断出来ることもあるのですが、私の環境では安定して中断させるには至りませんでした。
コメント返し
たくさんの閲覧、コメントありがとうございます。
とうとう10000 PVを突破し、感無量で御座います。
さて、はてなブックマークの方で頂いたコメントに関して、一部では御座いますが回答させていただきます。
- 「五年前に知りたかった」「もっと早く知りたかった」
- 実は私、2010年頃にはこの手法に気がついていました。 お伝えするのが遅くなって申し訳ございません！ 今回この記事を書くことが出来たのは、偶然Twitterで知り合ったフォロワーさん方々のお陰です。 一人で過ごしていたら、これほど価値がある情報だとは気づきもしなかったと思います。
- 「よく発見できたな。MSDNのどっかに書いてあるのか？」
- 自力で発見したものなので、書いてあるかどうかは分かりません。少なくとも日本語のページでは見たことがないので、私が一番だったら嬉しいです。
- 「どういう理屈？」
追記 2019/1/14 21:00
改定 2019/1/15 20:30
何か御座いましたらコメント欄、またはTwitterからどうぞ♪
週1回の更新を目指して、頑張ってますので応援よろしくおねがいします！
それでは、また今度♪ ちゅんちゅん(・8・)
2019/1/16 勇猛なるVBA使い諸君へ。私がデバッグに苦労した高難易度のVBAクイズを作成したので、是非挑戦してみてください。