今回はVBAユーザー必見、応答なしになったExcelでVBAを確実に中断させる方法(仮称:ちゅんちゅん奥義)を紹介します。
皆さんはVBA或いはマクロの実行中に、無限ループを起こしてしまい中断できないので「泣く泣くExcelを強制終了させた」なんて経験はありませんか?
実はWindowsの裏技的な方法で、VBAを止めることが可能です!
一般的に知られているVBAの中断方法
VBAを中断させたい時は 「ESCキーを押す」 というのが最もよく知られている方法だと思います。
読者の方の中には 「Ctrl+Pause/Breakを押す」 とか 「ESCを連打する」 とか 「ESCを押し続ける」 くらいは検索すると出てくるのでご存知の方も多いかと思います。
しかし、これらの方法は 応答なしになる前であれば止まりやすいのですが、 「応答なし」状態のExcelに対して成功率が低く、滅多に中断することができません。
マクロが中断出来なければExcelを強制終了させるしか無く、何時間も掛けて書いたプログラムを捨てて肉体的にも精神的にも大きな損失を被ることになります。
以前Twitterで「無限ループの時にESCで中断出来る条件が分からない」という話があった時に紹介したところ、フォロワーから大歓声を頂きまして「意外と知られてないのかな」と思い記事にすることにしました。
実はこれがブログを開設するキッカケとなった出来事でした。
@hajime_matsui
— ちゅん(・8・) (@KotorinChunChun) 2018年12月20日
これは私の秘奥義の一つですが、VBAの無限ループ中にデバッグを割り込ませるにするにはコツがあります。
とは言え、わーー!ってなった時に偶然気がついたものなので、他にも発見してる人が居てもおかしくないですが。 pic.twitter.com/CavbfOGBdq
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
このようなプログラムでは、短時間であれば応答なしにはなりませんが、ある程度の時間が経過すると応答なしになります。自然にならないにしても、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と喧嘩してマクロが止まるとか、意図しないデータが破壊されたりと極めて危険な状態となります。
また、プログラム全体の所要時間が大幅に伸びてしまうという問題があります。
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
また、 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
を使うのが望ましいと言えます。
応答が停止してしまったVBAを確実に中断させる方法
前置きが長くなりましたが、コードを最適化したりDoEventsを挟んだりして応答なしを防ぐ事はできますが、開発中に誤って無限ループさせてしまうミスが無くなるわけではありません。
また1回のテストに5分かかるマクロを、開始(F5)を押してから気がついて、すぐに中断させたいなんてことは珍しくもないでしょう。
そんな時に使うのがこの方法
VBAを確実に中断させる
ESCを押したままタスクバーでExcel・VBE・他のウィンドウをランダムにクリックしてください。
数回では止まらない事もありますが、10回もポチポチすれば中断できるはずです。
AccessなどESCが使えない環境では Ctrl+Break も有効です。 Ctrl+Break
はExcelを含む全ての環境で使えるので非常に強力です。
※Breakキー
とはキーボード右上端の Pause/Break
と印字されたキーです。
中断できるようにする条件
タスクバーの設定を変える
この方法を使うためには、タスクバーをクリックするだけでウィンドウが切り替わる必要があります。
最近のWindowsの標準設定では同じアプリがグルーピングされてしまうため、この方法は使いにくいかもしれません。
使う予定の人は以下のように設定を変えておきましょう。 (タスクバーを右クリック -> タスクバーの設定 -> 「タスクバー ボタンを結合する」を「結合しない」に変更)
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で知り合ったフォロワーさん方々のお陰です。
- 一人で過ごしていたら、これほど価値がある情報だとは気づきもしなかったと思います。
- 「よく発見できたな。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・)