えくせるちゅんちゅん

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

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

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

VBA VBA開発支援 永久保存版

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

皆さんはVBA或いはマクロの実行中に、無限ループを起こしてしまい中断できないので「泣く泣くExcelを強制終了させた」なんて経験はありませんか？

実はWindowsの裏技的な方法で、VBAを止めることが可能です！

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

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

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

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

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

以前Twitterで「無限ループの時にESCで中断出来る条件が分からない」という話があった時に紹介したところ、フォロワーから大歓声を頂きまして「意外と知られてないのかな」と思い記事にすることにしました。

私がブログを開設することになったキッカケの一つかもしれません。



Excelが応答なしになる原因と防ぐ方法

そもそもExcelが応答なしになる原因は、ExcelとVBAは一連の流れの中で動いているため、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で応答なしを防ぐ

まず、応答なしを防ぐためによく紹介されるのが、次のようにDoEventsをループの中に記述する方法です。

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

f:id:Kotori-ChunChun:20190211110215g:plain
周回毎にDoEventsを実行して応答なしを防ぐ
しかし、DoEventsをループの度に実行すると、VBAが実行中にも関わらずユーザーがExcelを自由に操作出来るようになってしまうため、極めて危険な状態となります。

また、プログラムの所要時間が大幅に伸びてしまうことがあります。


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を実行してユーザーの操作と強制終了の防止を両立
カウンタ基準は簡単ですが環境によって処理速度が違いますから、不特定多数が使うマクロならタイマーを使って制御したほうが良いでしょう。


補足

ExcelVBAでゲームを作る人は、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

f:id:Kotori-ChunChun:20190211110416g:plain
ループを必要としない高速なコード
これなら100万行の書き換えも数秒どころか、0.1秒もかかりません。

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

従って、そもそも応答なしになるような書き方は避けて、どうしても必要な部分だけDoEventsを使うのが最良だと考えます。


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

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

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


確実に中断させる方法

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

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

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

Access、Word、Outlook、ExcelでESCが効かないような場合は、Ctrl+Breakを押したままタスクバーからウィンドウを切り替えて下さい。 一応はCtrl+Breakのほうが強力です。※Breakキーとはキーボード右上端のPause/Breakと印字されたキーです。

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

中断できなくなる条件

  • Windowsの標準設定のままだとタスクバーはグルーピングされてしまうため、この方法は使いにくいかもしれません。使う人は設定を変えておきましょう。（タスクバーを右クリック -> タスクバーの設定 -> 「タスクバー ボタンを結合する」を「結合しない」に変更）
  • Auto_OpenやThisWorkbook_Openイベント等でマクロが強制実行されてしまう場合は、Excelのオプションでマクロが勝手に有効化されないようにしてから開くと幸せになれます。
  • この方法で中断出来るのは、ちゃんとVBAのステップが進んでいる場合だけです。例えば以下のような例ではタイミングが非常にシビアとなるため、なかなか成功しません。

巨大なログファイルのデータを改行区切りで配列に分解

Dim S() As String
Do
    S = Split("とてつもなく長い文字列", vbLF)
Loop

巨大なセル範囲を二次元配列として代入

Dim Data As Variant
Do
    Data = Range("A1:IV10000").Value
Loop
  • 重たい写真を貼り付け
  • データベースとの接続（ADOを使っている場合など）
  • 自動操作のためのIEの自動操作（IEの応答を待つような場合など）
  • コマンドプロンプトやPowerShellの同期実行

尚、外部プロセスの応答待ちの場合は、タスクマネージャーからIEやCMD等、対象のプログラムを強制終了させることでVBAに復帰できる場合があります。


逆に言えば緊急時に中断出来るようにするためには、ある程度VBAが流れるように1回ごとの負荷を軽減しつつ、中途半端に重い処理を混ぜておいたほうが良いということになります。



はてブコメント返し

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

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


  • 「裏技みたい」
    • まさにWindowsの裏技ですね。
    • 他にも裏技と気づかずにやっている操作が色々とありそうなので、Twitterしながら良いアイディアが浮かんだら記事に残していきたいと思います。


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


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


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


  • Alt+TabからのESCでも出来るよ！
    • 確かに中断出来ることもあるのですが、私の環境では高確率で中断させるには至りませんでした。


追記　2019/1/14 21:00

改定　2019/1/15 20:30


まとめ

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

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

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

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

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

ただし、上記の通りVBAが流れていない場面では、VBAの中断は無理ですのでご注意ください。

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


なお、無事に停止が出来たらVBEの各種機能を使って徹底的にデバッグしましょう。

www.excel-chunchun.com

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

それではまた来週♪　ちゅんちゅん(・8・)

プライバシーポリシー