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

エラー表示

このエラーが発生すると、マクロ起動時（コンパイル時）に次のエラーが表示されます。
実行時エラー '9'

インデックスが有効範囲にありません。

エラーの発生理由について

Microsoft公式の解答

Microsoft公式によると次のようにあります。

  • 存在しない配列の要素を参照しました。 添字が使用可能な添字の範囲外であるか、またはこの時点でアプリケーション内で配列に次元が割り当てられていません。配列の 宣言で、配列の上限と下限を確認します。次元が変更された配列を使用する場合は、 UBound 関数および LBound 関数を使用して、配列へのアクセスを調整します。インデックスが 変数として指定されている場合は、変数名のスペルを確認します。
  • 配列を宣言しましたが、要素数を指定していません。たとえば、次のコードではこのエラーが発生します。

少しわかりにくいですね。配列を使用していないのに本エラーが発生する場合もあります。詳しく見てみましょう。

【原因１】配列の添え字ミス

1つ目はMicrosoftの言うとおり、配列の範囲の指定ミスもしくは配列の添え字の指定ミスです。配列の要素の最下限を返すLBound関数や配列の要素の最大値を返すUBound関数を使用してその範囲内に添え字を指定しているか調べましょう。

例１：配列の最終要素を超える要素に格納しようとした。 

'Arr(1) に 1を格納したかった
Sub TestFunc1_1()
    Dim Arr(5) As Long
    '「Arr(1) = 1」のつもり
    Arr(10) = 1
End Sub

配列の最終要素を超える要素に格納を防ぐ方法

例２：未割り当ての動的配列に値を格納した。 

'Arr() で宣言後、 Redimで定義し忘れた
Sub TestFunc1_2()
    Dim Arr() As Long
    '「ReDim Arr(5) As Long」をしたつもり
    Arr(0) = 1
End Sub

未割り当ての動的配列に値を格納することを防ぐ方法

【原因２】開いていないブック名

開いていないブック名を指定してしまった時も本エラーが発生します。変数で指定している場合は、値をよく見直してみましょう。

例１：開いていないブック名を指定した。 

'「book1」のSheet1のセルA1の値を表示したかった
Sub TestFunc2()
    '「"/book1"」は誤字で「"book1"」が正
    MsgBox Workbooks("/book1").Worksheets("Sheet1").Cells(1, 1)
End Sub

値を格納する前にブックが開かれているか確認する方法

【原因３】存在しないシート名

存在しないシート名を指定してしまった時も本エラーが発生します。変数で指定している場合は、値をよく見直してみましょう。シート名に加え、ブックの指定もしている場合は「ブックAにあるシート名をブックBで指定してしまっている」ことが無いかを確認してください。

例１：存在しないシート名を指定した 

'「Sheet1」のセルA1の値を表示したかった
Sub TestFunc3()
    '「"Sheeet1"」は誤字で「"Sheet1"」が正
    MsgBox Worksheets("Sheeet1").Cells(1, 1)
End Sub

シートが存在するか確認する方法

【原因４】セルの範囲の指定ミス

セルの範囲の指定をミスしてしまった時も本エラーが発生します。変数で指定している場合は、値をよく見直してみましょう。

例１：セルの指定ミス 

'セルA2を指定したかった
Sub TestFunc4_1()
    Dim i as Long, j as Long
    i = -1 '「i = -1」でなく、「i = 1」が正しい
    j = 2
    'MsgBox Worksheets("Sheet1").cells(-1 , 2) となっている
    MsgBox Worksheets("Sheet1").cells(i , j)
End Sub

例２：セルの範囲の指定ミス 

'セルA1～A3の範囲指定をしたかった
Sub TestFunc4_2()
    '「Range("A0:A3")」でなく、「Range("A1:A3")」が正しい
    MsgBox Worksheets("Sheet1").Range("A0:A3")
End Sub

【原因５】シートの保護がかかっている

シートの保護がかかっている場合も本エラーが発生します。ただ、エラーメッセージのポップアップで表示されるので比較的気付きやすいかと思います。 

'シートの保護の解除 → セルに値を格納 → シートの保護 としたかった
Sub TestFunc5()
    'Protect と Unprotect の順番が逆
    ActiveSheet.Protect
    Cells(1, 1) = "a5"
    ActiveSheet.Unprotect
End Sub

解決方法は？

添え字やブック名、シート名を見直してみましょう。ケアレスミスをしてしまっているはずです。

Point!

添え字やブック名、シート名を変数にしている場合は、変数の値を確認してみましょう。イミディエイトウィンドウを使用すると楽に確認できます。

例１：配列の最終要素を超える要素に格納を防ぐ方法 

' 配列の最終要素を超える要素に格納を防ぐ方法
Sub SolutionSample1()
    Dim Arr(5) As Long
    Dim Target As Long
    Target = 10
    If LBound(Arr) <= Target Then
        Arr(Target) = 1234
    Else
        MsgBox "最終要素を超えた要素に格納しようとしました"
    End If
End Sub

例２：未割り当ての動的配列に値を格納することを防ぐ方法 

' 配列の最終要素を超える要素に格納を防ぐ方法
Sub SolutionSample2()
    Dim Arr() As Long
    If Not Not Arr Then
        Arr(0) = 1
    Else
        MsgBox "ReDimで初期化していません"
    End If
End Sub

例３：値を格納する前にブックが開かれているか確認する方法 

'-----------------------------------------------------
'OpenedBook 関数
'機　能：指定（引数）のブック名を開いているか確認する
'引　数：開いているか確認するブック名
'戻り値：True→引数のブック名を開いている
'        False→引数のブック名を開いていない
'-----------------------------------------------------
Function OpenedBook(ByRef bookName As String) As Boolean
    Dim wb As Workbook
    For Each wb In Workbooks
        If wb.Name = bookName Then
            OpenedBook = True
            Exit Function
        End If
    Next
End Function

'--------------
'メインの関数
'--------------
Sub SolutionSample3()
    Dim bookName As String
    bookName = "Book1.xlsx"
    If OpenedBook(bookName) Then
        Workbooks(bookName).Worksheets("Sheet1").Cells(1, 1) = "aa"
    Else
        MsgBox "指定のブック：" & bookName & "　を開いていません"
    End If
End Sub

例４：シートが存在するか確認する方法 

'-----------------------------------------------------
'ExistSheet 関数
'機　能：指定（引数）のシート名が存在しているか確認する
'引　数：開いているか確認するブック名
'戻り値：True→引数のシート名が存在している,
'        False→引数のブック名が存在しない
'-----------------------------------------------------
Function ExistSheet(ByRef bookName As String, _
                         ByRef sheetName As String) As Boolean
    Dim ws As Worksheet
    For Each ws In Workbooks(bookName).Worksheets
        If ws.Name = sheetName Then
            ExistSheet = True
            Exit Function
        End If
    Next
End Function
'--------------
'メインの関数
'--------------
Sub SolutionSample4()
    Dim bookName As String
    Dim sheetName As String
    bookName = ThisWorkbook.Name
    sheetName = "Sheet1"
    If ExistSheet(bookName, sheetName) Then
        Workbooks(bookName).Worksheets(sheetName).Cells(1, 1) = "aa"
    Else
        MsgBox "指定のシート：" & sheetName & "　が存在しません"
    End If
End Sub

エラーで止まってしまった時に、添え字やブックやシートを見直すことも重要です。ブックやシートを見直すときに画面の更新を停止しているとデバッグしにくいです。停止するプログラムをコメントアウトして、エラーが解消したら、コメントアウトを外して画面の更新を停止させてください。

このエラーの予防策

エラーが発生しそうなプログラムは重点的にテストを行いましょう。
どうしても不安な箇所は、添え字ならUBoundやLBoundで確認をします。ブックは開いているか、シートは存在しているかを確認してから値などの操作をしましょう。ただし、操作のたびにこの確認をしてしまうと処理が遅くなりますので極力重点的なテストで済ませましょう。

関連リンク

ページの先頭へ