Excelマクロ実行時エラー’9′ 添字が有効範囲にありません｜原因と修正方法

Excelマクロを実行中に突然表示される「実行時エラー ‘9’: 添字が有効範囲にありません」。このエラーは、存在しないシート名・ブック名・配列の範囲外アクセスなど、「参照しようとした対象が見つからない」ときに発生するVBAの代表的なエラーのひとつです。エラーメッセージが「添字が有効範囲にありません」という少し難しい表現のため、初心者が原因を特定しにくいという声も多くあります。本記事では、エラー9が発生するあらゆるパターンを網羅し、その場でコピーして使える修正コードとともに丁寧に解説します。

実行時エラー’9’とは何か
原因1：シート名の指定ミス
原因2：ブック名・ブックインデックスの指定ミス
原因3：配列の添字オーバー
原因4：コレクションへの範囲外アクセス
原因5：Split関数の結果配列の添字ミス
存在確認ユーティリティ関数集
デバッグ手順とイミディエイトウィンドウ活用法
エラー9対策のエラーハンドリングテンプレート
エラー9 原因チェックリスト
まとめ

実行時エラー’9’とは何か

「添字が有効範囲にありません（Subscript out of range）」というメッセージが示すとおり、エラー9は存在しないインデックスや名前でオブジェクト・配列を参照しようとしたときに発生します。具体的には次のような状況です。

Sheets("Sheet1") で指定したシート名が存在しない、またはスペルが違う
Workbooks("data.xlsx") で指定したブックが開かれていない
arr(10) のように宣言した配列のサイズを超えた添字でアクセスしている
Sheets(5) のように番号でシートを参照しているが、シートが4枚しかない
Split() の結果配列をUBoundを使わずにハードコードで参照している

エラー1004が「操作自体の失敗」を示すのに対し、エラー9は「参照先が見つからない」という点が特徴です。多くの場合、原因はシート名のタイポかブックが開かれていないかのどちらかです。落ち着いて以下の各原因を順番に確認してください。

原因1：シート名の指定ミス

エラー9の原因として最も多いのが、シート名の文字列指定ミスです。全角・半角スペースの混入、スペルミス、シートが削除・リネームされたのにコードが古いままになっているといったケースが該当します。

エラーが起きるコード例

' シート名「データ」が存在しない（実際は「データ入力」など）
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("データ")   ' → 実行時エラー '9'

' シート番号が存在しない（シートが3枚なのに4番目を指定）
Set ws = ThisWorkbook.Sheets(4)          ' → 実行時エラー '9'

' スペースが混入している（見た目では気づきにくい）
Set ws = ThisWorkbook.Sheets("Sheet1 ") ' → 実行時エラー '9'（末尾スペース）

修正後のコード（存在確認してからアクセスする）

' ========================================
' シートの存在確認を行う安全なアクセス方法
' ========================================
Function SheetExists(sheetName As String, Optional wb As Workbook) As Boolean

    If wb Is Nothing Then Set wb = ThisWorkbook

    Dim ws As Worksheet
    On Error Resume Next
    Set ws = wb.Sheets(sheetName)
    On Error GoTo 0

    SheetExists = Not (ws Is Nothing)

End Function

' 使用例
Sub SafeSheetAccess()

    Dim targetName As String
    targetName = "データ"

    If Not SheetExists(targetName) Then
        MsgBox "シート「" & targetName & "」が見つかりません。" & vbCrLf & _
               "シート名を確認してください。", vbCritical
        Exit Sub
    End If

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(targetName)
    ws.Cells(1, 1).Value = "処理済"

End Sub

全シート名をイミディエイトウィンドウに出力して確認する

' VBAエディタで Ctrl+G を押してイミディエイトウィンドウを開き、
' 以下のコードを貼り付けて Enter を押す
Sub ListAllSheetNames()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print "インデックス:" & ws.Index & "  名前:[" & ws.Name & "]"
    Next ws
End Sub

この出力で名前を確認すると、末尾スペースや全角文字の混入をすぐに発見できます。角括弧 [ ] で囲んでいるため、スペースが一目でわかります。

シート名をハードコードせずコード名（CodeName）で参照する

シートには「シート名」とは別に「コード名（CodeName）」があります。コード名はVBAプロジェクトのシートオブジェクト名（例：Sheet1、Sheet2）のことで、ユーザーがシートタブをリネームしても変わりません。シート名変更に強いコードを書くには、コード名での参照が有効です。

' コード名（Sheet1）で参照する方法
' → タブ名を「データ入力」に変更されても動作する
Sheet1.Cells(1, 1).Value = "コード名参照は安全"

' コード名の確認方法
' VBAエディタのプロジェクトエクスプローラーで
' 「Sheet1 (データ入力)」のように表示される
' 括弧の外側がコード名、内側がシートタブ名

コード名はVBAエディタのプロパティウィンドウ（F4キー）で変更できます。分かりやすい名前（例：wsInput、wsMaster）にしておくとコードの可読性も上がります。

原因2：ブック名・ブックインデックスの指定ミス

Workbooks("ファイル名") で別のブックを参照する場合、そのブックが現在Excelで開かれていないとエラー9が発生します。また、ファイル名に拡張子を含め忘れるケースも多いです。

エラーが起きるコード例

' ブックが開かれていない場合はエラー9
Dim wb As Workbook
Set wb = Workbooks("売上データ.xlsx")   ' → エラー9（開かれていなければ）

' 拡張子を忘れるとエラー9
Set wb = Workbooks("売上データ")        ' → エラー9（.xlsxが必要）

' インデックスがブック数を超えている
Set wb = Workbooks(10)                  ' → 開いているブックが3冊しかなければエラー9

修正後のコード（ブックの存在確認）

' ========================================
' 開いているブックの存在確認ユーティリティ
' ========================================
Function WorkbookIsOpen(wbName As String) As Boolean

    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(wbName)
    On Error GoTo 0

    WorkbookIsOpen = Not (wb Is Nothing)

End Function

' 使用例
Sub SafeWorkbookAccess()

    Dim targetWB As String
    targetWB = "売上データ.xlsx"

    If Not WorkbookIsOpen(targetWB) Then
        ' 自動で開く
        Dim filePath As String
        filePath = ThisWorkbook.Path & "\" & targetWB

        If Dir(filePath) = "" Then
            MsgBox "ファイルが見つかりません:" & vbCrLf & filePath, vbCritical
            Exit Sub
        End If

        Workbooks.Open filePath
    End If

    Dim wb As Workbook
    Set wb = Workbooks(targetWB)
    wb.Sheets(1).Cells(1, 1).Value = "アクセス成功"

End Sub

開いているすべてのブック名を確認する

' 現在開いているブックの一覧をイミディエイトウィンドウに出力
Sub ListOpenWorkbooks()
    Dim wb As Workbook
    For Each wb In Workbooks
        Debug.Print "インデックス:" & wb.Index & "  名前:[" & wb.Name & "]"
    Next wb
End Sub

原因3：配列の添字オーバー

VBAで配列を宣言したとき、宣言したサイズを超える添字でアクセスするとエラー9が発生します。VBAのデフォルトでは配列の開始インデックスは0ですが、Option Base 1 を宣言している場合や Dim arr(1 To 5) のように範囲指定している場合は挙動が変わります。この違いを理解していないと混乱の原因になります。

エラーが起きるコード例

' Dim arr(5) → インデックス 0〜5 の6要素（デフォルト）
Dim arr(5) As String
arr(6) = "エラー"   ' → 実行時エラー '9'（6は範囲外）

' Option Base 1 のとき Dim arr(5) → インデックス 1〜5 の5要素
' モジュール先頭に Option Base 1 が宣言されている場合
arr(0) = "エラー"   ' → 実行時エラー '9'（0は範囲外）

' 動的配列をReDimする前にアクセス
Dim dynArr() As Long
dynArr(0) = 100   ' → 実行時エラー '9'（ReDim前は要素なし）

修正後のコード

' ========================================
' 配列の安全な使い方
' ========================================

' 方法1：LBound / UBound で範囲を確認してからアクセス
Dim arr(1 To 5) As String
arr(1) = "A"
arr(2) = "B"
arr(3) = "C"

Dim i As Long
For i = LBound(arr) To UBound(arr)
    Debug.Print arr(i)
Next i

' 方法2：動的配列はReDimしてからアクセス
Dim dynArr() As Long
ReDim dynArr(1 To 10)

Dim j As Long
For j = 1 To 10
    dynArr(j) = j * 100
Next j

' 方法3：要素を追加するたびにReDim Preserve
Dim results() As String
Dim count As Long
count = 0

ReDim results(1 To 1)   ' 初期化

Dim k As Long
For k = 1 To 5
    If k Mod 2 = 1 Then   ' 条件に合うものだけ追加
        count = count + 1
        ReDim Preserve results(1 To count)
        results(count) = "Item_" & k
    End If
Next k

Debug.Print "要素数: " & UBound(results)

Option Baseの落とし穴

' モジュール先頭に記述すると、そのモジュール内の配列開始インデックスが変わる
Option Base 1

Sub ArrayTest()
    Dim arr(5) As String   ' Option Base 1 なら 1〜5 の5要素
                           ' Option Base 0（デフォルト）なら 0〜5 の6要素

    ' 必ず LBound / UBound で制御するクセをつける
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        arr(i) = "要素" & i
    Next i
End Sub

推奨：Option Baseはプロジェクト内で統一し、ループは常に LBound / UBound で制御することで、エラー9を根本から防げます。

原因4：コレクションへの範囲外アクセス

Sheetsコレクション・Worksheetsコレクション・独自のCollectionオブジェクトに対して、存在しないインデックスやキーでアクセスするとエラー9が発生します。

エラーが起きるコード例

' シートが3枚しかないのに4番目を参照
Debug.Print ThisWorkbook.Sheets(4).Name   ' → エラー9

' Collectionに存在しないキーでアクセス
Dim col As New Collection
col.Add "Apple",  "key1"
col.Add "Banana", "key2"

Debug.Print col("key3")   ' → エラー9（key3は存在しない）

修正後のコード

' シート数を確認してからインデックスアクセス
Dim targetIndex As Long
targetIndex = 4

If targetIndex <= ThisWorkbook.Sheets.Count Then
    Debug.Print ThisWorkbook.Sheets(targetIndex).Name
Else
    MsgBox "シートが " & targetIndex & " 枚存在しません。" & vbCrLf & _
           "現在のシート数: " & ThisWorkbook.Sheets.Count, vbExclamation
End If

' ========================================
' Collectionのキー存在確認ユーティリティ
' ========================================
Function CollectionKeyExists(col As Collection, key As String) As Boolean

    On Error Resume Next
    Dim dummy As Variant
    dummy = col(key)
    CollectionKeyExists = (Err.Number = 0)
    Err.Clear
    On Error GoTo 0

End Function

' 使用例
Dim col As New Collection
col.Add "Apple",  "key1"
col.Add "Banana", "key2"

If CollectionKeyExists(col, "key3") Then
    Debug.Print col("key3")
Else
    Debug.Print "key3 は存在しません"
End If

原因5：Split関数の結果配列の添字ミス

CSVの行を Split() で分割するとき、期待した列数よりも実際のフィールド数が少ない場合にエラー9が発生します。これはCSVのデータ不備や、区切り文字の指定ミスが原因です。

エラーが起きるコード例

' CSVの一行が "A,B,C" のとき、Split結果は cols(0)〜cols(2) の3要素
Dim lineText As String
lineText = "A,B,C"

Dim cols() As String
cols = Split(lineText, ",")

' 4番目（インデックス3）にアクセスしようとするとエラー9
Debug.Print cols(3)   ' → 実行時エラー '9'

' 空行（lineText = ""）のときもSplit結果は1要素 cols(0) = ""
' cols(1) 以上にアクセスするとエラー9
lineText = ""
cols = Split(lineText, ",")
Debug.Print cols(1)   ' → 実行時エラー '9'

修正後のコード（UBoundチェックを入れる）

' ========================================
' Split結果の安全なアクセスパターン
' ========================================
Sub SafeSplitAccess()

    Dim lineText  As String
    Dim cols()    As String
    Dim requiredCols As Long

    requiredCols = 4   ' 必要な列数

    ' 空行スキップ
    lineText = "A,B,C"

    If Len(Trim(lineText)) = 0 Then
        Debug.Print "空行のためスキップ"
        Exit Sub
    End If

    cols = Split(lineText, ",")

    ' 必要な列数が揃っているか確認
    If UBound(cols) < requiredCols - 1 Then
        MsgBox "列数が不足しています。" & vbCrLf & _
               "必要: " & requiredCols & " 列 / 実際: " & (UBound(cols) + 1) & " 列" & vbCrLf & _
               "行の内容: " & lineText, vbExclamation
        Exit Sub
    End If

    ' 安全にアクセス
    Debug.Print "列1: " & cols(0)
    Debug.Print "列2: " & cols(1)
    Debug.Print "列3: " & cols(2)
    Debug.Print "列4: " & cols(3)

End Sub

' ========================================
' CSVインポートループ内での安全な列アクセス
' ========================================
Sub ImportCSVSafe()

    Dim fileNo    As Integer
    Dim lineText  As String
    Dim cols()    As String
    Dim ws        As Worksheet
    Dim rowIndex  As Long
    Dim errorLog  As String

    Set ws = ThisWorkbook.Sheets(1)
    ws.Cells.Clear
    rowIndex = 1

    fileNo = FreeFile()
    Open ThisWorkbook.Path & "\data.csv" For Input As #fileNo

    Do While Not EOF(fileNo)
        Line Input #fileNo, lineText

        ' 空行スキップ
        If Len(Trim(lineText)) = 0 Then GoTo NextLine

        cols = Split(lineText, ",")

        ' 列数チェック（例：最低3列必要）
        If UBound(cols) < 2 Then
            errorLog = errorLog & rowIndex & "行目: 列数不足 [" & lineText & "]" & vbCrLf
            GoTo NextLine
        End If

        ws.Cells(rowIndex, 1).Value = cols(0)
        ws.Cells(rowIndex, 2).Value = cols(1)
        ws.Cells(rowIndex, 3).Value = cols(2)
        ' 4列目以降は存在する場合のみ取得
        If UBound(cols) >= 3 Then ws.Cells(rowIndex, 4).Value = cols(3)

        rowIndex = rowIndex + 1

NextLine:
    Loop

    Close #fileNo

    If Len(errorLog) > 0 Then
        MsgBox "以下の行でエラーが発生しました:" & vbCrLf & errorLog, vbExclamation
    Else
        MsgBox "インポート完了！ " & rowIndex - 1 & " 行処理しました。", vbInformation
    End If

End Sub

存在確認ユーティリティ関数集

エラー9の対策として汎用的に使えるユーティリティ関数をまとめました。標準モジュールに追加しておくと、どのマクロからでも呼び出せます。

' ========================================
' エラー9対策 ユーティリティ関数集
' 標準モジュールに貼り付けて使用してください
' ========================================

' ----- シートの存在確認 -----
Function SheetExists(sheetName As String, Optional wb As Workbook) As Boolean
    If wb Is Nothing Then Set wb = ThisWorkbook
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = wb.Sheets(sheetName)
    On Error GoTo 0
    SheetExists = Not (ws Is Nothing)
End Function

' ----- ブックが開かれているか確認 -----
Function WorkbookIsOpen(wbName As String) As Boolean
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(wbName)
    On Error GoTo 0
    WorkbookIsOpen = Not (wb Is Nothing)
End Function

' ----- 配列が初期化されているか確認 -----
Function IsArrayInitialized(arr As Variant) As Boolean
    On Error Resume Next
    Dim lb As Long
    lb = LBound(arr)
    IsArrayInitialized = (Err.Number = 0)
    Err.Clear
    On Error GoTo 0
End Function

' ----- Collectionにキーが存在するか確認 -----
Function CollectionKeyExists(col As Collection, key As String) As Boolean
    On Error Resume Next
    Dim dummy As Variant
    dummy = col(key)
    CollectionKeyExists = (Err.Number = 0)
    Err.Clear
    On Error GoTo 0
End Function

' ----- 名前付き範囲が存在するか確認 -----
Function NamedRangeExists(rangeName As String, Optional wb As Workbook) As Boolean
    If wb Is Nothing Then Set wb = ThisWorkbook
    Dim nm As Name
    On Error Resume Next
    Set nm = wb.Names(rangeName)
    On Error GoTo 0
    NamedRangeExists = Not (nm Is Nothing)
End Function

' 使用例まとめ
Sub CheckAllExistence()

    ' シート確認
    If SheetExists("集計") Then
        Debug.Print "シート「集計」が存在します"
    End If

    ' ブック確認
    If WorkbookIsOpen("マスタ.xlsx") Then
        Debug.Print "マスタ.xlsx が開かれています"
    End If

    ' 配列確認
    Dim arr() As String
    If Not IsArrayInitialized(arr) Then
        ReDim arr(1 To 5)
    End If

    ' 名前付き範囲確認
    If NamedRangeExists("売上範囲") Then
        Debug.Print ThisWorkbook.Names("売上範囲").RefersToRange.Address
    End If

End Sub

デバッグ手順とイミディエイトウィンドウ活用法

エラー9が発生したとき、原因を素早く特定するための手順です。

ステップ1：エラー行を黄色ハイライトで確認する

エラーダイアログが出たら「デバッグ」ボタンをクリックします。VBAエディタ上でエラーが発生した行が黄色くハイライトされます。その行で何を参照しようとしているかを確認してください。

ステップ2：イミディエイトウィンドウで現状を確認する

' Ctrl+G でイミディエイトウィンドウを開き、以下を入力してEnter

' 全シート名の確認
For Each ws In ThisWorkbook.Worksheets : Debug.Print ws.Index, ws.Name : Next ws

' 開いているブック名の確認
For Each wb In Workbooks : Debug.Print wb.Index, wb.Name : Next wb

' 変数の値をその場で確認（?変数名 で即時評価）
' 例：?targetName  → targetName 変数の現在値が表示される

ステップ3：F8キーによるステップ実行で変数を追跡する

マクロにカーソルを置いてF8キーを押すと、1行ずつ実行するステップ実行モードになります。黄色いカーソルが移動するたびにローカルウィンドウ（表示メニュー→ローカルウィンドウ）で変数の値が更新されます。

ステップ4：問題のある行をOn Error Resume Nextで一時的に囲んでErr.Numberを確認

' エラーが起きる箇所を特定したい場合の一時的なデバッグコード
On Error Resume Next

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("問題のシート名")

If Err.Number = 9 Then
    Debug.Print "エラー9発生：シートが見つかりません"
    Debug.Print "指定した名前：[問題のシート名]"
    ' 全シート名を出力して比較
    Dim s As Worksheet
    For Each s In ThisWorkbook.Worksheets
        Debug.Print "  存在するシート：[" & s.Name & "]"
    Next s
    Err.Clear
End If

On Error GoTo 0

エラー9対策のエラーハンドリングテンプレート

実務で運用するマクロには、エラー9を含む全エラーをキャッチして処理を安全に終了させる仕組みが必要です。以下は、エラー9に特化したメッセージを表示する実装パターンです。

' ========================================
' エラー9に対応したエラーハンドリングテンプレート
' ========================================
Sub MainWithErrorHandling()

    Application.ScreenUpdating = False
    Application.Calculation    = xlCalculationManual
    Application.EnableEvents   = False

    On Error GoTo ErrorHandler

    ' ===== メイン処理 =====
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("集計")   ' ← ここでエラー9が起きうる

    ws.Cells(1, 1).Value = "処理開始"
    ' 以降の処理...
    ' =====================

    Application.ScreenUpdating = True
    Application.Calculation    = xlCalculationAutomatic
    Application.EnableEvents   = True
    MsgBox "処理完了", vbInformation
    Exit Sub

ErrorHandler:
    Application.ScreenUpdating = True
    Application.Calculation    = xlCalculationAutomatic
    Application.EnableEvents   = True

    Dim msg As String

    Select Case Err.Number
        Case 9
            msg = "指定したシート・ブック・配列の要素が見つかりません。" & vbCrLf & vbCrLf & _
                  "確認してください:" & vbCrLf & _
                  "・シート名のスペルミスがないか" & vbCrLf & _
                  "・参照するブックが開かれているか" & vbCrLf & _
                  "・配列の添字がLBound〜UBoundの範囲内か"
        Case 1004
            msg = "Excelオブジェクトへの操作が失敗しました。" & vbCrLf & _
                  "シートの保護、セル範囲、ファイルパスを確認してください。"
        Case Else
            msg = "予期しないエラーが発生しました。"
    End Select

    MsgBox msg & vbCrLf & vbCrLf & _
           "エラー番号: " & Err.Number & vbCrLf & _
           "内容: " & Err.Description, vbCritical, "実行時エラー " & Err.Number

End Sub

エラー9 原因チェックリスト

エラー9が発生したとき、以下のリストを上から順に確認してください。多くのケースはこのリストで原因を特定できます。

シート名の文字列にスペル・全角半角・スペースの混入がないか（Debug.Print で全シート名を確認する）
シートがユーザーによってリネームまたは削除されていないか
シートをインデックス番号で参照している場合、シート総数以内の番号か（ThisWorkbook.Sheets.Count で確認）
Workbooks("ファイル名") で参照しているブックが現在Excelで開かれているか
ブック名に拡張子（.xlsx / .xlsm）が含まれているか
配列を Dim arr(5) で宣言した場合、アクセスしている添字がLBound〜UBound の範囲内か
動的配列（Dim arr() As ...）をReDimする前にアクセスしていないか
Option Base 1 が宣言されているモジュールで、インデックス0からのアクセスをしていないか
Split() の結果に対して、UBound(cols) 以下の添字でしかアクセスしていないか
Collectionに対して存在しないキー文字列でアクセスしていないか
名前付き範囲（Names）に対して存在しない範囲名で参照していないか

まとめ

実行時エラー’9’「添字が有効範囲にありません」は、参照しようとした対象（シート・ブック・配列要素・コレクション）が存在しないか、範囲外のインデックスを指定しているときに発生します。本記事で解説した内容を整理すると次のとおりです。

シート名ミス：SheetExists() 関数で事前確認し、コード名（CodeName）での参照に切り替えるとリネームに強いコードになる
ブック参照：WorkbookIsOpen() で確認し、未オープンなら自動で開く処理を実装する
配列の添字：ループは常に LBound / UBound で制御し、動的配列は ReDim してからアクセスする
Splitの結果：UBound(cols) で列数を確認してから各フィールドにアクセスする
Collection：On Error Resume Next を活用したキー存在確認関数を用意する
デバッグ：イミディエイトウィンドウで全シート名・ブック名を出力して目視確認するのが最速

本記事で紹介したユーティリティ関数集を標準モジュールに追加しておくだけで、エラー9への耐性が大幅に向上します。「存在確認してからアクセスする」という習慣を身につけることが、安定したVBAコード作成の第一歩です。

目次

実行時エラー’9’とは何か

原因1：シート名の指定ミス

エラーが起きるコード例

修正後のコード（存在確認してからアクセスする）

全シート名をイミディエイトウィンドウに出力して確認する

シート名をハードコードせずコード名（CodeName）で参照する

原因2：ブック名・ブックインデックスの指定ミス

エラーが起きるコード例

修正後のコード（ブックの存在確認）

開いているすべてのブック名を確認する

原因3：配列の添字オーバー

エラーが起きるコード例

修正後のコード

Option Baseの落とし穴

原因4：コレクションへの範囲外アクセス

エラーが起きるコード例

修正後のコード

原因5：Split関数の結果配列の添字ミス

エラーが起きるコード例

修正後のコード（UBoundチェックを入れる）

存在確認ユーティリティ関数集

デバッグ手順とイミディエイトウィンドウ活用法

ステップ1：エラー行を黄色ハイライトで確認する

ステップ2：イミディエイトウィンドウで現状を確認する

ステップ3：F8キーによるステップ実行で変数を追跡する

ステップ4：問題のある行をOn Error Resume Nextで一時的に囲んでErr.Numberを確認

エラー9対策のエラーハンドリングテンプレート

エラー9 原因チェックリスト

まとめ