【完全網羅】VBA エラー 9 インデックスが有効範囲にありません。の原因と対処方法

VBAを実行するとVBA エラー 9「インデックスが有効範囲にありません。」を見る機会はありませんか？

VBA エラー 9はVBAエラーの中でも発生頻度の高いエラーとしてランクインされているとか。

そんな中で悩むことは、

ではないでしょうか？

今回は、

についてまとめます！

VBA エラー 9「インデックスが有効範囲にありません。」はどんなエラー？

配列の要素外の指定やコレクションに存在しないインデックスや名前を指定したときに発生するエラーです。

タカヒロ

配列要素の指定でよくやってしまいますよね・・・

VBA エラー 9の原因①「配列の最小・最大要素数を超えた要素を指定」

VBA エラー 9「インデックスが有効範囲にありません。」の原因は

配列の最小・最大要素数を超えた要素を指定したためです。

最大要素数を超えて指定した場合

例えば配列の数が3つだったとして、

存在しない4つ目を指定してしまうなどです。

以下はエラー再現のサンプルコードです。

Sub エラーテスト1()
    Dim Hairetsu(3) As String
    Hairetsu(1) = "Aさん"
    Hairetsu(2) = "Bさん"
    Hairetsu(3) = "Cさん"
    MsgBox "4番目は、" & Hairetsu(4) & "です"
End Sub

エラーが表示されますね。

最小要素数を超えて指定した場合

また、よくあるのが、Rangeプロパティでセル範囲を指定し、配列へ一気に代入するやり方で配列を作成し、
0番目を指定するケースです。

以下エラーサンプルコードです。

Sub エラーテスト1()
    Dim Hairetsu As Variant
    Hairetsu = Range("A1:A3")
    MsgBox "0番目は、" & Hairetsu(0) & "です"
End Sub

エラーが表示されます。

セルには0番目が存在しないので、セル範囲から配列へ代入した場合、1番目から配列が作成されます。

存在しない0番目の配列を指定したため、エラーとなるわけですね。

宣言した配列の要素数を超えて指定した場合

配列はReDimステートメントやReDimステートメントで定義、再定義しますが、「To」を使うと最小値と最大値を同時に指定することができます。

その「To」によって宣言した要素の範囲を超えて配列を指定するとエラーとなります。

以下サンプルコードです。

Sub エラーテスト1()
    Dim Hairetsu(1 To 3) As String
    Dim temp As Variant
    Hairetsu(0) = "Aさん"
    Hairetsu(1) = "Bさん"
    Hairetsu(2) = "Cさん"
    Hairetsu(3) = "Dさん"
    For Each temp In Hairetsu
        Debug.Print temp & "です"
    Next
End Sub

エラーがでますね。

配列の宣言は1番目から3番目となっていますが、0番目を指定しているためエラーとなります。

VBA エラー 9の原因①「配列の最小・最大要素数を超えた要素を指定」の対処方法は配列設定時や参照時のループ終了条件を正しく設定する

配列の要素数を超えない範囲で指定する

VBA エラー 9の原因①「配列の最大要素数を超えた要素を指定」の対処方法は

配列の要素数を超えない範囲で指定するようにします。

タカヒロ

存在しない要素を指定しないようにすればエラーはでることはありませんね。

エラー修正版のサンプルコードです。

Sub エラーテスト1a()
    Dim Hairetsu(3) As String
    Hairetsu(1) = "Aさん"
    Hairetsu(2) = "Bさん"
    Hairetsu(3) = "Cさん"
    MsgBox "2番目は、" & Hairetsu(2) & "です"
End Sub

問題ありませんね！

LBoundとUBoundで最小・最大要素数を取得し処理する

LBoundとUBoundで最小・最大要素数を取得し、

Forループで配列数分まわしてデータを取得します。

LBoundは配列の最小要素数を、UBoundは配列の最大要素数を返す関数です。

自動で配列の要素数を取得しますので、範囲外の要素を指定することはありません。

Sub エラーテスト1b()
    Dim Hairetsu(1 To 3) As String
    Hairetsu(1) = "Aさん"
    Hairetsu(2) = "Bさん"
    Hairetsu(3) = "Cさん"
    For i = LBound(Hairetsu) To UBound(Hairetsu)
        Debug.Print i & "番目は、" & Hairetsu(i) & "です"
    Next
End Sub

結果です。

タカヒロ

配列数を宣言する場合は通常0番目から作成されます。
1番目から利用する場合は宣言にtoをいれ1番目から〇〇番までという指定をしましょう。

For Eachで配列の値を取得し制御する

For Eachで配列の要素値を取得します。

タカヒロ

配列の要素分繰り返し処理してくれるのでForで回数を指定するより確実ですね。

Sub エラーテスト1c()
    Dim Hairetsu(1 to 3) As String
    Dim temp
    Hairetsu(1) = "Aさん"
    Hairetsu(2) = "Bさん"
    Hairetsu(3) = "Cさん"
    For Each temp In Hairetsu
        Debug.Print temp & "です"
    Next
End Sub

結果です。

VBA エラー 9の原因②「存在しないブック名やシート名を指定」

VBA エラー 9「インデックスが有効範囲にありません。」の原因は

存在しないブック名やシート名を指定したためとなります。

以下はエラー再現のサンプルコードです。

存在しないシート名を指定

Sub エラーテスト2a()
    Dim objSheet As Worksheet
    Set objSheet = Worksheets("存在しないシート名")
End Sub

タカヒロ

シート名は打ち間違いが多いでしょうか。全角半角など気を付けたいですね。

存在しないブック名を指定

Sub エラーテスト2b()
    Dim objWB As Workbook
    Set objWB = Workbooks("存在しないブック名")
End Sub

特にブック名指定でオブジェクトをセットする時には対象ブックを開いている必要がありますが、
その対象ブックを誤って閉じていまったりすると同様のエラーが発生するので気を付けたいですね。

2パターンともエラーが表示されました。

VBA エラー 9の原因②「存在しないブック名やシート名を指定」の対処方法は存在するブック名、シート名を設定する

VBA エラー 9の原因②「存在しないブック名やシート名を指定」の対処方法は

存在するブック名、シート名を設定するようにしましょう。

タカヒロ

ブックオブジェクトをセットしている場合は対象ブックを閉じないようにしましょう。

VBA エラー 9の原因③「存在しないHyperlinksコレクションのアイテムを指定している」

VBA エラー 9「インデックスが有効範囲にありません。」の原因は

存在しないHyperlinksコレクションのアイテムを指定したためとなります。

以下はエラー再現のサンプルコードです。

コレクションの最大要素数を超えて指定している

Sub エラーテスト3a()
    MsgBox Range("A1:A3").Hyperlinks(4).TextToDisplay
End Sub

こちらはA1～A3の3つの要素を対象にハイパーリンクアイテムを取得していますが、存在しない4つ目の要素を指定しているためエラーとなります。

コレクション内のアイテムが存在しない

Sub エラーテスト3b()
    MsgBox Range("A1:A3").Hyperlinks(3).TextToDisplay
End Sub

こちらはA1～A3の3つの要素の範囲内である3つ目の要素を指定していますが、

3つ目にハイパーリンクが存在しないためエラーとなります。

タカヒロ

空欄のセルを含めて処理する場合は注意したいですね。

VBA エラー 9の原因③「存在しないHyperlinksコレクションのアイテムを指定している」の対処方法は存在するHyperlinksコレクションのアイテムを設定する

VBA エラー 9の原因③「存在しないHyperlinksコレクションのアイテムを指定している」の対処方法は存在するHyperlinksコレクションのアイテムを設定するようにしましょう。

以下のように対象範囲すべてにハイパーリンクを挿入します。

VBAはその範囲内の存在する要素を指定するようにします。

Sub エラーテスト3b()
    MsgBox Range("A1:A3").Hyperlinks(3).TextToDisplay
End Sub

VBA エラー 9の共通の原因はCountできるコレクションを処理する際の例外

VBA エラー 9の共通の原因はCountできるコレクションを処理する際の例外が多いです。

コレクションとは同じ種類の複数のオブジェクトをひとつにまとめたものです。
上記例のブックやシート、ハイパーリンクを集めたHyperlinksコレクションが該当します。

コレクションの特徴としては、

です。

タカヒロ

デバッグをすると、Item(1)、Item(2)などオブジェクトが複数含まれていることがわかりますね。

コレクションを扱う時にその中身を把握しないでコードを組むと、VBA エラー 9が発生する可能性が高くなります。

特にコレクションの中身が運用をしていく中で変動がある場合は注意が必要です。

リリース前の検証では問題なかったのに、運用に入ってからコレクションの中身に変動が生じ、例外となりエラーとなる場合があるからです。

ですので、処理したい対象がコレクションであった場合は中身が将来どのように変化していくかという点を見据え、上記対策を参考に構築したほうがよいでしょう。

タカヒロ

Webサイトをスクレイピングしたり、Outlookのメールアイテムを収集したりなど、情報取得先が常に変動している場合も注意したいですね。

さいごに

今回は、

についてまとめました。

このエラー9は配列やコレクションなど複数の要素を保有しているオブジェクトを処理する際によく発生しますので、
しっかり例外処理を入れ、データに変更があったとしてもエラーを回避できるようにしたいですね。