【Excel】VBAを実行すると”応答なし”が表示される場合の対処

2021年8月6日2024年9月24日

Excelマクロ実行時の”応答なし”を解除する方法

単体テストでは、問題なかったがモジュールを組合わせて連続で実行すると、よく出現する現象でプログラムが正常終了すると、またExcelに制御が戻り復活するが、あまり気持ちの良いもんではないのと、ホワイトアウト中は、マクロを{ESCキー}で終了させることが出来ない。

修正手順

使用しているVBAモジュール内に一番処理時間が掛かっていそうなLoop処理を探す
DoEvents 関数を1行挿入する（ループ内でしか利用できないので注意！）

VBAサンプルで説明「DoEvents 関数」挿入箇所

sub loop_test
Dim i as integer,j as integer
    for i=1 to 12
        for j=1 to 1000
            Cells(j + 1, i) = (i - 1) * 1000 + j
            '''処理省略

        next j
    next i
end sub

上記サンプルを単体実行するとエラー表示はしないが、複数のモジュールを繋げて実行するようなモジュールを作成しそれを実行すると、 Excelタグに”応答なし”と表示されホワイトアウトする。この場合繋げたモジュールを１つ１つ確認した中で、Loop処理が一番長いこのモジュールでエラーになっていると考えるのが妥当。

DoEvents 関数を挿入するだけだが、どこに入れるか？

sub loop_test
Dim i as integer,j as integer
    for i=1 to 12
　　　　　➀
        for j=1 to 1000
　　　　　➁
            Cells(j + 1, i) = (i - 1) * 1000 + j
            '''処理省略

        next j
    next i
end sub

「DoEvents関数」はLoop内でなければ効果がない！

ので、上記サンプルだと➀か➁ということになる。

➀に入れてテスト
これでもまだエラーが出るようなら➁に入れてテスト
これでもまだエラーが出る場合はこのモジュールではないと判断
違うLoop処理を使っているモジュールを探し出し、同じ手順でエラーがなくなるまで探す

といった具合で該当するモジュールを発見し「DoEvents関数」を埋め込み対処する。

自分でVBAを使うだけならExcelのホワイトアウトは大きな問題ではないが、

対処の仕方を知っているのと知らないのとでは、仕事する上では信頼度が大きく変化する。

2024.8月下旬から起きているExcelVBA遅延問題はMSO (バージョン 2409 ビルド 16.0.18025.20030) 64 ビットに更新すると直る！

Ms-officeの不具合「特にExcelVBA・AccessVBA」の遅延

最新バージョンに更新すると、2024.7月時点の速さに戻るので、最近やけにExcelVBAやAccessVBAが遅くなったよなぁ～って人は、コレで解決するはず！

自作競馬DBをVBAで作っている人は少ないかも知れないが、業務で大量のExcelシートをExcelVBAやAccessVBAで集計したりワークシートをマージしたりしている人は、まだまだ多いと思う。

昨日までは普通に使えていたのに、今日はやたらとExcelVBAの処理時間が長く、グルグルが回りっぱなし…とか、異常停止してしまうって時は、最近MS-Officeの更新があったかどうか？の確認は必要である。

今回だと、「バージョン 2408」にバグがあって、このバージョンだとVBAがやたらと遅くなり、動作がもっさりしてプログラムごとパクってるの？的な遅さに確実に出くわすことになるが、最新の「バージョン 2409」にすれば元に戻る。

但し、最近ではExcelVBAを業務で使っている人も激減しているようで、昔ほど騒ぎにはならず、情報が出て修正パッチが出るまでに2週間は掛かるので、今後はもっと遅くなると予想される。

Microsoftの回避策としてバージョンを以前のバージョンにダウングレードすれば良い。。。的なことを書かれてはあるものの・・・Microsoft365だとまぁ成功しない！カスと思うのが精々なところ。

気長に修正パッチバージョンが出るのを待つか？GAS+スプシやPython＋LibreOfficeで遅い部分だけを組み直す方が速いかな～とさえ思えるし、こういうMicrosoftのチョンボ対策もしておかないとなぁ～と考えさせられる場面である。（昔からよくあるんだよｗ）

簡単な対応策としては、

Excel2019とか2016とか2013とか昔のインストールディスクを残しておく
Microsoft365の自動更新してなさそうな使ってないパソコンを見つけて、自動更新を切る
Microsoft365以外にOffice2021をヤフオクで安価に買い通常稼働していないパソコンに入れておく

などが考えられる。

今回私の場合は、ノートパソコンにかなり前のバージョンがあったことと、処理速度が5倍以上遅くなってはいたが処理が止まることがなかったので、データだけチェックしてそのまま使い、最新バージョンの「バージョン 2409」に更新した際に気になる部分は再度プログラムを実行し上書きした。といった感じで対応した。

編集後記

ExcelVBA本（プログラミング本）の選び方のコツ

Amazon-Primeで物色
Kindle-Unlimitedで物色
巻末の索引で「自分の探している命令」が載っている
目次で当たりを付ける
大型ブックオフ店で探す
大型書店で探す
Amazonの★を気にしない

Excel VBA 逆引き大全 600の極意 Microsoft 365/Office 2021/2019/2016/2013対応

Excel VBA 逆引き大全 600の極意 Microsoft 365

※私が持っているのが2016年版（以下は2016）

ExcelVBAの基本構文
セルの操作
関数
ワークシート、ウィンドウの操作
ブック操作
データ操作
テーブル・ピボットテーブル
図形
グラフ
ユーザーフォーム
印刷
ファイルとフォルダ
データ連携
イベント
バージョン・トラブルシューティング・エラー処理
高度なテクニック
索引

目次が巻頭で検索が巻末。昔のタイプの本の構成だが、索引がページでなくTipsという見出しNo.になっていて慣れるまでは検索しづらい。
Amazonレビューは★3.9

私のKindle-Unlimited評価は★4

パソコン、データ処理の半自動化

2024.09.24時点
なんだかんだ言ってもUWSC+ExcelVBA+Accessマクロが私には最強だな～と考える。勿論、金を掛けて専門的なツールを利用するのが一番なんだが、出来るだけ安価にデータを集めるとしたら…という前提で、まだまだ使える？使えてる最強ツールであることは確かだね(^_-)-☆