今回はVBAユーザー必見、応答なしになったExcelでVBAを確実に中断させる方法(仮称:ちゅんちゅん奥義)を紹介します。
皆さんはVBA或いはマクロの実行中に、無限ループを起こしてしまい中断できないので「泣く泣くExcelを強制終了させた」なんて経験はありませんか?
実はWindowsの裏技的な方法で、VBAを止めることが可能です!
一般的に知られているVBAの中断方法
VBAを中断させたい時は「ESCキーを押す」のが一般的に知られている方法だと思います。
読者の方の中には「Ctrl+Pause/Breakを押す」とか「連打する」とか「長押しする」くらいはネットで検索すると出てくるのでご存知の方も多いかと思います。
しかしこれらの方法は、必ずしも中断することはできないため、何時間もかけて作ったエクセルブックやプログラムを捨てざるをえず、肉体的にも精神的にも大きな損失を被る場合があります。
以前Twitterで「無限ループの時にESCで中断出来る条件が分からない」という話があった時に紹介したところ、フォロワーから大歓声を頂きまして「意外と知られてないのかな」と思い記事にすることにしました。
私がブログを開設することになったキッカケの一つかもしれません。
@hajime_matsui
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2018年12月20日
これは私の秘奥義の一つですが、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で応答なしを防ぐ
まず、応答なしを防ぐためによく紹介されるのが、次のようにDoEvents
をループの中に記述する方法です。
Sub 超長時間ループ2() Dim i As Long For i = 1 To 1000000 Cells(i, 10).ClearContents DoEvents Next End Sub
また、プログラムの所要時間が大幅に伸びてしまうことがあります。
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
補足
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
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
と印字されたキーです。
中断できなくなる条件
- 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で知り合ったフォロワーさん方々のお陰です。
- 一人で過ごしていたら、これほど価値がある情報だとは気づきもしなかったと思います。
- 「よく発見できたな。MSDNのどっかに書いてあるのか?」
- 自力で発見したものなので、書いてあるかどうかは分かりません。
- 私の知る限り見たことがありません。
- 私が一番だったら嬉しい限りです。
- 「どういう理屈?」
- 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の各種機能を使って徹底的にデバッグしましょう。
何か御座いましたらコメント欄、またはTwitterからどうぞ♪
それではまた来週♪ ちゅんちゅん(・8・)