えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

応答が停止したExcelで、VBAを確実に中断させる方法

今回はVBAユーザー必見、応答なしになったExcelVBAを確実に中断させる方法(仮称:ちゅんちゅん奥義)を紹介します。

皆さんはVBAやマクロの使用中に無限ループをさせてしまって、VBAを中断させることが出来なくて泣く泣くExcelを強制終了させた。なんて経験はありませんか?

実はWindowsの裏技的な方法でVBAを止められる方法があります。

f:id:Kotori-ChunChun:20190211111740g:plain

※2019/2/10 検索からの流入が増えてきたため、本文と動画を分かりやすいように更新しました。


一般的に知られているVBAの中断方法

VBAを中断させたい時は「ESCキーを押す」のが一般的に知られている方法だと思います。

読者の方の中には「Ctrl+Pause/Breakを押す」とか「連打する」とか「長押しする」くらいはネットで検索すると出てくるのでご存知の方も多いかと思います。

しかしこれらの方法は、必ずしも中断することはできないため、何時間もかけて作ったエクセルブックやプログラムを捨てざる負えず、肉体的にも精神的にも大きな損失を被る場合があります。

以前、Twitterで無限ループの時にESCで中断出来る条件が分からない。という話があったので紹介したところ、(当時のフォロワー数的には)大盛況でしたので、意外と知られてないのかな、と思い記事にすることにしました。

Excelが応答なしになる原因

そもそもExcel応答なしになる原因は、簡単に言うとExcelVBAは同一のスレッドで動いているため、VBA実行中はExcelが一切の入力を受け付けなくなるためです。

例えば次のような書き方をすると発生します。

Sub 超長時間ループ1()
    Dim i As Long
    For i = 1 To 1000000
        Cells(i, 10).ClearContents
    Next
End Sub

f:id:Kotori-ChunChun:20190211110039g:plain
応答なしからの強制終了の再現

短時間であれば応答なしにはなりませんが、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をユーザーが普通に操作出来るようになってしまうため、極めて危険な状態となります。

またプログラムの所要時間が大幅に伸びてしまいます。

f:id:Kotori-ChunChun:20190211110215g:plain
周回毎にDoEventsを実行して応答なしを防ぐ

そこで、適度に応答なしから回復するように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

f:id:Kotori-ChunChun:20190211110323g:plain
適度にDoEventsを実行してユーザーの操作と強制終了の防止を両立

ちなみにベテランの方ならDoEevntsを使う前に、最適化出来る場所がないか模索します。

例えば今回の例で言えば、下記の様にワンステップで書くことが出来ます。

Sub 超長時間ループ4_なんていらなかった()
    Cells(1, 10).Resize(1000000, 1).ClearContents
End Sub

f:id:Kotori-ChunChun:20190211110416g:plain
ループを必要としない高速なコード

これなら100万行の書き換えも数秒どころか、0.1秒もかかりません。

Excel VBAでは書き方を少し見直すだけで、処理時間が1/100、1/1000になることも珍しくありません。

従って、そもそも遅くなるような書き方は避けて、DoEventsに逃げるのは最終手段であるべきだと考えます。

応答が停止してしまったExcelVBAを確実に中断させる方法

話しを戻します。

コードを最適化したり、DoEventsを挟んだりして応答なしを防ぐ事はできますが、開発中に誤って無限ループするミスが無くなるわけではありません。

また1回のテストに5分かかるが、開始30秒でミスに気がついて、すぐに中断させたい場合なんてのは非常に多いです。

そんな時に使うのがこの方法

ESCを押したままタスクバーでExcel・VBE・他のウィンドウをランダムにクリックしてください。

数回では止まらない事もありますが、大抵は10回もポチポチすればあっさりと中断できます。

Access、Word、OutlookExcelでESCが効かないような場合は、同じようにCtrl+Breakを押したままタスクバーからウィンドウを切り替えて下さい。 一応はこっちのほうが強力です。

f:id:Kotori-ChunChun:20190211110442g:plain
応答なしになったExcelVBAを確実に中断させる

まとめ

今回紹介した方法はVBAプログラマなら絶対に知っておくべき技術だと思います。

いつ何時トラブルに巻き込まれて、泣く泣くExcelを強制終了することのないように、いますぐ練習しておきましょう!

そして、上手くいかない事例があったら教えてください!

「上手く行ったよ!」というコメントでも大歓迎です。

私はExcel 2007/2010/2013/2016/Office 365で使っており、これまでに止められなかったループはありません。

ただし、外部プログラムと同期させるような仕組みでExcelが待ち状態の場合は、外部プロセスを強制終了させないことにはVBAの中断は無理ですのでご注意ください。

以上、ちゅんちゅん奥義の紹介でした!


追記

  • Windowsの設定で「タスクバーボタンを結合する」に設定していると、タスクがグルーピングされてしまうため、この方法は使いにくいかもしれません。使う人は設定を変えておきましょう。

  • Alt+TabからのESCでも出来るよ!と情報を頂きました。確かに中断出来ることもあるのですが、私の環境では安定して中断させるには至りませんでした。

コメント返し

たくさんの閲覧、コメントありがとうございます。

とうとう10000 PVを突破し、感無量で御座います。

さて、はてなブックマークの方で頂いたコメントに関して、一部では御座いますが回答させていただきます。

  • 「五年前に知りたかった」「もっと早く知りたかった」
    • 実は私、2010年頃にはこの手法に気がついていました。  お伝えするのが遅くなって申し訳ございません!  今回この記事を書くことが出来たのは、偶然Twitterで知り合ったフォロワーさん方々のお陰です。  一人で過ごしていたら、これほど価値がある情報だとは気づきもしなかったと思います。


  • 「裏技みたい」
    • まさにWindowsの裏技って奴ですね。  他にも裏技と気づかずにやっている操作が色々とありそうなので、Twitterでレビューしながら記事に残していきたいと思います。


  • 「よく発見できたな。MSDNのどっかに書いてあるのか?」
    • 自力で発見したものなので、書いてあるかどうかは分かりません。少なくとも日本語のページでは見たことがないので、私が一番だったら嬉しいです。


  • 「どういう理屈?」
    • 専門じゃないので正確なことは分かりませんが、タスクバーからのクリックには一瞬だけプログラムの方を中断させる力があって、その一瞬だけウィンドウメッセージがExcelまで到達するのではないかと想像しています。  Windowsのアプリケーションは、タイトルバークリック中や最大化アニメーション中などのウィンドウメッセージの処理中は、メインの処理が中断されて外部から入力できるスキが出来ることがよくありますね。


  • VBA関係なくフリーズするExcelの回復法を誰か教えて(Excel2013 以降ひどくなった)
    • Excelのオプションの「詳細設定」の「表示」で「ハードウェア グラフィック アクセラレータを無効にする」のチェックを入れることで、経験上8割のPCで安定しています。  この機能は止めてもExcelの操作には全く影響がないので切っても大丈夫です。  他にも色々解決策があるので気になる場合はGoogleで「Excel 2013 不安定」などと検索されることをお勧めします。


追記 2019/1/14 21:00

改定 2019/1/15 20:30


何か御座いましたらコメント欄、またはTwitterからどうぞ♪

週1回の更新を目指して、頑張ってますので応援よろしくおねがいします!

それでは、また今度♪ ちゅんちゅん(・8・)


2019/1/16 勇猛なるVBA使い諸君へ。私がデバッグに苦労した高難易度のVBAクイズを作成したので、是非挑戦してみてください。

kotori-chunchun.hatenablog.com