実行時エラー9「インデックスが有効範囲にありません」の原因と直し方

どんなエラーか

実行時エラー 9「インデックスが有効範囲にありません」は、「名前や番号で指定したものが見つからない」ときに出るエラーです。Worksheets("売上") のようにシートを名前で指定したとき、その名前のシートがなければこのエラーになります。配列の添字が範囲を超えたときにも同じ番号のエラーが出ます。

よくある原因

実務で多いのは次の4つです。とくに1つ目と2つ目は、マクロを作ったときは動いていたのに、ある日突然止まる原因になります。

• シート名が変わった（「売上」→「売上 」のように末尾に空白、全角・半角の違い）
• 参照しているブックが開いていない、またはファイル名が違う（Workbooks("集計.xlsx")）
• 配列の添字が範囲外（ReDim の大きさより大きい番号、0 始まりと 1 始まりの取り違え）
• Split の結果に想定した数の要素がない（区切り文字が含まれていない行）

原因の見つけ方

エラーのダイアログで「デバッグ」を押すと、止まった行が黄色く表示されます。その行で何を名前や番号で指定しているかを確認します。シート名が原因かどうかは、イミディエイトウィンドウ（Ctrl＋G）で次のように入力すると、ブック内のシート名をすべて確認できます。

' イミディエイトウィンドウで実行（? の代わりに Debug.Print でも可）
For Each s In ThisWorkbook.Worksheets: Debug.Print "[" & s.Name & "]": Next

再発を防ぐ書き方

シートの存在を確認してから使う関数を用意しておくと、エラーで止まる代わりに分かりやすいメッセージを出せます。

Function GetSheet(ByVal wb As Workbook, ByVal name As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        If ws.Name = name Then
            Set GetSheet = ws
            Exit Function
        End If
    Next ws
    Err.Raise vbObjectError + 1, , "シート「" & name & "」が見つかりません（ブック：" & wb.Name & "）"
End Function

Sub Sample()
    Dim ws As Worksheet
    Set ws = GetSheet(ThisWorkbook, "売上")
    ' 以降の処理
End Sub

そのほかの対策

シート名を利用者が変えてしまうのが原因の場合は、VBE のプロパティで設定できる「オブジェクト名（コード名）」でシートを指定する方法もあります。コード名は Excel の画面からは変更できないため、シート見出しの名前が変わっても影響を受けません。

ブックの指定が原因の場合は、Workbooks("ファイル名") ではなく、Workbooks.Open の戻り値を変数に入れて使うようにすると、ファイル名の違いで止まることがなくなります。配列が原因の場合は、ループの範囲を LBound と UBound で指定する習慣をつけましょう。

ほかの記事