えくせるちゅんちゅん

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

MENU
トップ > 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を押す」 とか 「ESCを連打する」 とか 「ESCを押し続ける」 くらいは検索すると出てくるのでご存知の方も多いかと思います。

しかし、これらの方法は 応答なしになる前であれば止まりやすいのですが、 「応答なし」状態のExcelに対して成功率が低く、滅多に中断することができません。

マクロが中断出来なければExcelを強制終了させるしか無く、何時間も掛けて書いたプログラムを捨てて肉体的にも精神的にも大きな損失を被ることになります。

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

実はこれがブログを開設するキッカケとなった出来事でした。



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

そもそもExcelが応答なしになる原因は、VBA実行中はExcelが入力を受け付けられなくなるためです。

Excelというプログラムの中でVBAが動いているため、VBAがExcelへ処理を返さないとOSから見て応答がないとなってしまうのです。


応答なしの再現プログラム

例えば次のような書き方をすると「応答なし」を再現できます。

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が判断して表示するので、自然に応答なし画面になるまでには環境によって差があります。

また、状態も「タイトルバーが応答なしになる」だったり、「画面が真っ白」になったり、「貼り付けという文字が画面に大量に出現」したりと様々です。

こうして 応答なしに変わったExcelは、一般的に知られる方法では止められない可能性が高いです。


そこで、「応答なしになりにくいプログラミング」を出来るようになることが非常に重要です。


DoEventsで応答なしを防ぐ

まず、応答なしを防ぐ直接的な方法として、DoEvents をループの中に記述する方法があります。

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

しかし、この方法には大きな問題があります。

ループする度にDoEventsを実行すると、VBAが実行中にも関わらずユーザーがExcelを自由に操作出来るようになってしまいます。

裏でVBAが走っていることが実感出来ないため、VBAと喧嘩してマクロが止まるとか、意図しないデータが破壊されたりと極めて危険な状態となります。

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

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


DoEventsの発生頻度を調整する

そこで、適度に応答なしから回復するようにDoEventsの発生頻度をチューニングするのが良いでしょう。

例えば、次のように数回ごとに飛ばしたり、Timer で時間を計測して一定時間経過したときだけにすると、操作感と安定性が両立できます。

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を実行してユーザーの操作と強制終了の防止を両立


また、 GetInputState関数を使って判定する方法もあります。（ExcelVBAでゲームを作る人がよく使うそうです）

| 呼び出し側スレッドのメッセージキューの中に、マウスボタンメッセージまたはキーボードメッセージが存在するかどうかを調べる。

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 を使うのが望ましいと言えます。

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


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

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

また1回のテストに5分かかるマクロを、開始(F5)を押してから気がついて、すぐに中断させたいなんてことは珍しくもないでしょう。


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

VBAを確実に中断させる

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

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

AccessなどESCが使えない環境では Ctrl+Break も有効です。 Ctrl+Break はExcelを含む全ての環境で使えるので非常に強力です。

※Breakキー とはキーボード右上端の Pause/Break と印字されたキーです。

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

中断できるようにする条件

タスクバーの設定を変える

この方法を使うためには、タスクバーをクリックするだけでウィンドウが切り替わる必要があります。

最近のWindowsの標準設定では同じアプリがグルーピングされてしまうため、この方法は使いにくいかもしれません。

使う予定の人は以下のように設定を変えておきましょう。 （タスクバーを右クリック -> タスクバーの設定 -> 「タスクバー ボタンを結合する」を「結合しない」に変更）

f:id:Kotori-ChunChun:20210607215623p:plain

1ステップに負荷をかけすぎない

VBAが割り込みで中断出来るのは、ちゃんとVBAのステップが進んでいる場合に限った話です。

例えば、以下のような1行に数秒かかかるような処理では、ESCキー割り込みのタイミングがシビアとなるため、なかなか成功しません。

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

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回ごとの負荷を軽減しつつ、中途半端に重い処理を混ぜておいたほうが良いということになります。

自動的なマクロの有効化を止める

Auto_OpenやThisWorkbook_Openイベント等を使っている場合、有無を言わさずで無限ループするマクロが実行されてしまう場合があります。

こういうときはExcelのオプションでマクロのセキュリティを上げて、勝手に有効化されないようにしてから開くと幸せになれます。



はてブコメント返し

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

  • 「五年前に知りたかった」「もっと早く知りたかった」
    • 実は私、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・)

プライバシーポリシー