For reference when creating a website
MK-BLOG
ホーム > MK-BLOG:ホームページ制作の参考に > ディレクターとAIの奮闘記!~デザインでお金を~ > マクロが黄色い行で止まった。慌てずに原因を見つけて直す、Excelマクロのエラー対処法

マクロが黄色い行で止まった。慌てずに原因を見つけて直す、Excelマクロのエラー対処法

社長からの内線と「実行時エラー」に焦惑するディレクター

前回の#023で、毎月の手作業が、ボタン一つで終わるようになりました。ディレクターは、毎月の15分が3秒になったことに、すっかり気を良くしています。ところが、ある朝、社長がボタンを押した直後に、内線が鳴ります。

「ディレクター君、画面が止まった」

ディレクターディレクター:
「社長がボタンを押したら、『実行時エラー』と書かれた小さな窓が出て、何も起きなくなったそうです。」

AIAI:
「#023で言いましたね。『その「たぶん」が、次回のテーマです』と。」

ディレクターディレクター:
「予言だったんですか。ともかく、私のマクロが壊れたということですよね。」

AIAI:
「壊れたのではありません。『ここで困りました』と、報告してくれただけです。」

エラーは、融通の利かない部下からの「報告」

AIAI:
「#023で、マクロは『手順書に書いていないことは、一切やらない部下』だとお話ししました。その部下が、手順書の途中で、想定外の事態にぶつかると、どうなると思いますか。」

ディレクターディレクター:
「勝手に判断せず、作業を止めて、報告に来る。……意外と、優秀じゃないですか。」

AIAI:
「ええ。黙って間違った結果を出す部下より、ずっと安全です。エラーの窓は、その報告書です。読めば、どの行で、何に困ったかが分かります。」

ディレクターディレクター:
「ただ、その報告書、見慣れない言葉ばかりで、読む気が失せます。」

本質パート:止まったら、まずこの3つ

エラーの窓が出たら、慌てて閉じたり、何度も押し直したりせず、次の3つを順に行います。

①エラーの窓を、そのまま読む

実行時エラー '9':
インデックスが有効範囲にありません。

[終了] [デバッグ] [ヘルプ]

窓には、エラー番号と、短い説明が書かれています。この番号と説明が、原因を探すときの、いちばんの手がかりです。画面を写真かスクリーンショットで残しておくと、誰かに相談するときも役に立ちます。

②「デバッグ」を押して、黄色い行を見る

窓の「デバッグ」を押すと、Visual Basicの画面が開いて、コードの一行が黄色く光ります。

VBE画面で「黄色い行」を見つめる現場検証シーン

黄色い行は、「ここで止まりました」という、現場の目印です。犯人そのものとは限りませんが、犯人は、たいていその行か、その少し前にいます。

ディレクターディレクター:
「刑事ドラマの、現場検証の黄色いテープみたいですね。」

AIAI:
「その例えで、いきましょう。そして、現場を見終わったら、必ず『リセット』を押します。」

③見終わったら、「リセット」で止める

Visual Basicの画面の上のほうにある、四角い「■」ボタン(リセット)を押す

デバッグの状態のままだと、マクロは「止まっているだけで、終わっていない」状態です。そのまま放置すると、ほかの操作ができなくなったり、次にボタンを押したときに、おかしな動きをしたりします。原因の確認が済んだら、「■」を押して、いったん終わらせます。

窓のボタン意味使いどき
終了マクロを止めて、窓を閉じる原因は後で調べる、または分かっている
デバッグ止まった行を、黄色く表示する原因を調べたいとき(基本はこちら)
ヘルプエラーの説明ページを開く説明を読んでも分からないとき

#023のマクロで起きやすい、4つのエラー

#023で作ったマクロで、とくに起きやすいものを、まとめます。エラーの番号や表示は、Excelのバージョンによって、少し異なることがあります。

状況主な原因直し方
黄色い行が Worksheets("集計画面") で、「インデックスが有効範囲にありません」と出る(エラー9)シート名を変えた、または、書いたシート名が違うコードのシート名を、実際のシート名にそろえる
黄色い行が ExportAsFixedFormat で止まる同じ名前のPDFを、開いたままにしているPDFを閉じてから、もう一度押す
同じ行で止まる(保存先がURLになっている)ブックがクラウド上にあり、保存先が使えない#023の通り、保存先を、パソコン内のフォルダに指定する
ボタンを押しても、「マクロを実行できません」と出るマクロが無効になっている、または、マクロの名前を変えた「コンテンツの有効化」を押す。ボタンのマクロの登録を、し直す
AIAI:
「社長のエラーは、2行目です。PDFを開いたまま、もう一度ボタンを押したのではないでしょうか。」

ディレクターディレクター:
「確認しました。社長、できあがったPDFを、パソコンの画面にずっと開いていました。」

AIAI:
「#023の連打の件も、これで解決です。連打すると、上書きされるだけではなく、ときどき、開いたPDFにぶつかります。」

ディレクターディレクター:
「『大丈夫です。……たぶん』と言った、私の『たぶん』が、ほぼ当たりましたね。」

原因が分からないときは、1行ずつ動かす

F8キーのステップ実行で原因(半角スペース等)を突き止めスッキリした二人

黄色い行を見ても、原因が分からないときは、マクロを「1行ずつ」実行して、どこで何が起きているかを確かめます。

1. Visual Basicの画面で、マクロの中(Subから始まる部分)をクリック
2. F8キーを押す
 → 黄色い行が1行進む(その行が実行される)
3. F8を押すたびに、1行ずつ進む。画面のExcelの動きも、あわせて確認する
4. 最後まで進めるか、「■」(リセット)を押して止める

F8を押しながら見ていくと、「更新が終わった」「スライサーが解除された」と、手作業の順番どおりに、マクロが動いていく様子が分かります。エラーが出るまでの動きを、自分の目で追えるのが、この方法の強みです。

変数の中身を、のぞいてみる

1. Visual Basicの画面で、Ctrl + G を押す(下に「イミディエイトウィンドウ」が出る)
2. 止まっている状態で、次のように入力して、Enterを押す
 ?pdfPath
 → PDFの保存先として、コードが作った文字が、そのまま表示される

保存先が、思っていた場所と違っていないかを、確認できます。シート名が合っているか分からないときは、次のように入力すると、ブックのシート名の一覧が、正しい綴りで並びます。

For Each ws In ThisWorkbook.Worksheets: Debug.Print ws.Name: Next
ディレクターディレクター:
「シート名の末尾に、半角スペースが入っていました。見た目では、まったく分かりません。」

AIAI:
「融通の利かない部下は、半角スペース一つの違いも、別人として扱います。」

次に止まったときのために、「止まり方」を整える

社長が、エラーの窓を見て驚かないように、マクロの側に、「止まったときの案内」を入れておくことができます。#023のコードを、次のように書き換えます。

Sub 月次レポート出力()

    On Error GoTo ErrHandler

    ' 1. すべて更新(ピボットテーブルとグラフ)
    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone

    ' 2. スライサーをすべてクリア
    Dim sc As SlicerCache
    For Each sc In ThisWorkbook.SlicerCaches
        sc.ClearManualFilter
    Next sc

    ' 3. PDFで出力(ファイル名に年月を入れる)
    Dim pdfPath As String
    pdfPath = ThisWorkbook.Path & Application.PathSeparator & _
              "集計画面_" & Format(Date, "yyyy-mm") & ".pdf"

    ThisWorkbook.Worksheets("集計画面").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=pdfPath, _
        Quality:=xlQualityStandard, _
        OpenAfterPublish:=False

    MsgBox "PDFを出力しました。" & vbCrLf & pdfPath
    Exit Sub

ErrHandler:
    MsgBox "途中で止まりました。" & vbCrLf & _
           "PDFを開いたままではありませんか?" & vbCrLf & vbCrLf & _
           "エラー番号:" & Err.Number & vbCrLf & _
           "内容:" & Err.Description

End Sub

変更したのは、2か所です。最初の On Error GoTo ErrHandler で、「エラーが起きたら、下の ErrHandler の位置へ移動する」と、手順書に書き足しました。そして、最後に、止まったときの案内(エラー番号と内容を表示する窓)を付けました。Exit Sub は、問題なく最後まで進んだときに、案内の部分を読まずに終わるための印です。

ひとつだけ、注意があります。この書き方にすると、エラーが起きても黄色い行が出なくなり、「どの行で止まったか」が分かりにくくなります。コードを書き換えて試しているあいだは、On Error GoTo ErrHandler の行の先頭に「'」を付けて、メモ扱いにしておくと、黄色い行で確認できます。完成して、社長に渡すときに、「'」を外します。

今日のまとめ(AIと一緒に)

AIAI:
  1. 1.エラーの窓は、マクロからの報告。番号と内容を読み、「デバッグ」で黄色い行を見て、確認が済んだら「■」(リセット)で止める。
  2. 2.原因が分からないときは、F8で1行ずつ動かし、イミディエイトウィンドウ(Ctrl + G)で、変数の中身やシート名を確かめる。
  3. 3.よくある原因は、シート名の違い、PDFの開きっぱなし、クラウドの保存先。止まったときの案内は、On Error GoTo で書いておける。
ディレクターディレクター:
「これで、次にエラーが出ても、慌てずに、黄色い行から見に行けそうです。」

AIAI:
「ただ、エラーを直しても、社長が押し間違えたり、連打したりする心配は、まだ残っています。」

ディレクターディレクター:
「予言の続きですか。」

AIAI:
「いいえ、ただの経験則です。」

次回#025では、ボタンを押す前に「本当に出力しますか?」と確認を出して、押し間違いや連打から、集計画面を守る方法を扱う予定です。

ディレクターとAIの奮闘記 #024 おわり