Excel VBAのエラー9の対処法をお探しですね。

広告

Excel VBA「インデックスが有効範囲にありません」エラーの解決方法

Excel VBAで「実行時エラー ‘9’：インデックスが有効範囲にありません」と表示されると、どこを直せばいいのか困ってしまいますよね。

このエラーは、簡単に言うと**「存在しない番号や名前を指定している」**ときに出るエラーです。

特に、シート名の間違い、開いていないブックの指定、配列の番号ミスが原因になりやすいので、順番にチェックしていけば解決できることが多いです。

エラー9って何？どんなときに出るの？

エラー9は、配列やコレクション（データのまとまり）に対して、存在しない要素を参照しようとしたときに発生します。

「インデックス」というのは、配列の番号や、`Worksheets(“Sheet1”)`のようにオブジェクトを取り出すための名前・番号のことです。

存在しないシート名を指定したり、配列の範囲外の番号を指定したりすると、VBAは対象を見つけられず「インデックスが有効範囲にありません」と教えてくれます。

ちょっと分かりにくいのは、配列を使っていないコードでもこのエラーが出ることです。

実は、Workbooks、Worksheets、Sheets、Dictionaryなどを使っているコードでもよく発生します。

つまり、**エラー9を見たら「指定した番号や名前が本当に存在しているか」をまず疑ってみる**のが基本です。

例えば、次のようなコードは、「データ」という名前のシートが存在しない場合にエラー9になります。

“`vba
Sub Sample()
Worksheets(“データ”).Range(“A1”).Value = “テスト”
End Sub
“`

ブック内に「データ」という名前のワークシートがあれば問題なく動きます。

でも、シート名が「data」「データ一覧」「データ 」（末尾に空白）のように少しでも違っていると、VBAは該当シートを見つけられません。

見た目では同じに見えても、全角と半角が混ざっている、末尾にスペースが入っている、シート名を変更したのにコードを直していない、といったケースもよくあります。

エラー9が出たら、まずVBEで黄色くハイライトされた行を確認しましょう。

その行にある Worksheets、Workbooks、配列名などを見て、指定している名前や番号が実際に存在するかをチェックします。

変数で指定している場合は、イミディエイトウィンドウで `? 変数名` と入力して中身を確認すると、誤字や想定外の値に気づきやすくなります。

シート名・ブック名の指定ミスに注意

Excel VBAでエラー9が出る一番の原因は、**存在しないシート名を指定していること**です。

`Worksheets(“Sheet1”)` のように名前でシートを指定する場合、VBAは完全一致でシートを探します。

「Sheet1」と「Sheet 1」、「集計」と「集計表」は別物として扱われます。

人間には似た名前に見えても、VBAにとっては違うものなので、指定したシートがなければエラー9になります。

また、複数のブックを開いて処理している場合は、「どのブックのシートを見ているか」も重要です。

`Worksheets(“Sheet1”)` とだけ書くと、アクティブなブック（今操作しているブック）のシートを参照することがあります。

本当はマクロが保存されているブックのSheet1を操作したいのに、別のブックが前面に出ていると、想定外のブックからシートを探すことになり、エラー9につながります。

このようなミスを避けるには、**ブックとシートを明示して書く**ことが大切です。

マクロを保存しているブックを対象にするなら `ThisWorkbook`、現在操作中のブックを対象にするなら `ActiveWorkbook` を使い分けます。

– `ThisWorkbook`：このVBAコードが入っているブック
– `ActiveWorkbook`：現在アクティブなブック

複数ブックを開く処理では、この違いを曖昧にするとエラー9や誤操作の原因になります。

“`vba
Sub SafeSheetAccess()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“データ”)

ws.Range(“A1”).Value = “テスト”
End Sub
“`

ブック名の指定ミスでもエラー9はよく発生します。

`Workbooks(“Book1.xlsx”)` のように書いた場合、そのブックが現在Excel上で開かれていなければ参照できません。

さらに、**ブック名を指定するときは拡張子まで含める必要がある**点にも注意してください。

「Book1」と書いてしまったり、実際は「Book1.xlsm」なのに「Book1.xlsx」と書いたりすると、やはりエラーになります。

ブック名やシート名がユーザー操作によって変わる可能性がある場合は、参照する前に存在確認を入れると安全です。

以下のように、対象シートがあるかを確認してから操作すれば、突然エラーで止まるのを防げます。

“`vba
Function SheetExists(ByVal sheetName As String, ByVal wb As Workbook) As Boolean
Dim ws As Worksheet

For Each ws In wb.Worksheets
If ws.Name = sheetName Then
SheetExists = True
Exit Function
End If
Next ws
End Function

Sub UseSheet()
If SheetExists(“データ”, ThisWorkbook) Then
ThisWorkbook.Worksheets(“データ”).Range(“A1”).Value = “OK”
Else
MsgBox “指定したシートが見つかりません。

”
End If
End Sub
“`

ちなみに、`Sheets` と `Worksheets` の違いにも注意が必要です。

`Worksheets` はワークシートだけを対象にしますが、`Sheets` はグラフシートなども含みます。

通常のセル操作では `Worksheets` を使うほうが意図が明確で分かりやすいです。

配列の添え字ミスとUBound・LBoundの使い方

エラー9のもう一つの大きな原因が、**配列の範囲外アクセス**です。

配列は複数の値をまとめて扱うための入れ物で、それぞれの値には番号が付いています。

この番号を「添え字」または「インデックス」と呼びます。

存在しない番号を指定すると、VBAはどの要素を使えばよいか分からず、エラー9を出します。

例えば、次のコードでは `arr(3)` を参照した時点でエラーになります。

“`vba
Sub ArrayError()
Dim arr(2) As String

arr(0) = “東京”
arr(1) = “大阪”
arr(2) = “名古屋”

Debug.Print arr(3) ‘ エラー！
End Sub
“`

`Dim arr(2)` と書くと、要素数が2個になると勘違いしやすいですが、実際には `arr(0)` から `arr(2)` までの**3個の要素**が作られます。

括弧内の数字は「要素数」ではなく「最大の添え字」なんです。

この感覚が曖昧なままループを書くと、最後に1つ余分に参照してしまい、エラー9が発生しやすくなります。

配列を扱うときは、**`LBound` と `UBound` を使う**のが基本です。

– `LBound`：配列の最小インデックス
– `UBound`：配列の最大インデックス

重要なのは、`UBound` が「要素数」ではなく「最後の番号」を返すという点です。

要素数を知りたい場合は、`UBound(arr) – LBound(arr) + 1` のように計算します。

“`vba
Sub ArraySafeLoop()
Dim arr(2) As String
Dim i As Long

arr(0) = “東京”
arr(1) = “大阪”
arr(2) = “名古屋”

For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i
End Sub
“`

動的配列（サイズを後から決める配列）でも同じ考え方が必要です。

`Dim arr() As String` と宣言しただけでは、まだ要素の領域は確保されていません。

その状態で `arr(0)` のようにアクセスすると、エラー9が発生します。

“`vba
Sub DynamicArraySample()
Dim arr() As String

ReDim arr(2) ‘ ここでサイズを決める
arr(0) = “A”
arr(1) = “B”
arr(2) = “C”
End Sub
“`

配列の全要素を処理するだけなら、`For Each` を使う方法も便利です。

`For Each` はインデックス番号を直接指定しないため、範囲外の番号を指定するミスを減らせます。

配列エラーを減らすために、特に確認したいポイントは次のとおりです。

– `UBound` を要素数だと勘違いしていないか
– `ReDim` する前の動的配列にアクセスしていないか
– ループの終了条件が `UBound(arr) + 1` などになっていないか
– 配列の開始番号が0なのか1なのかを確認しているか

配列の範囲を決め打ちせず、`LBound` と `UBound` で取得する習慣をつけると、後から要素数が変わっても壊れにくいコードになります。

エラー9を効率よく解決・予防する方法

エラー9が出たときは、やみくもにコードを書き換えるより、**発生箇所と参照対象を分けて確認する**ことが大切です。

まず、エラーダイアログで「デバッグ」を押し、黄色く表示された行を確認します。

その行の中に、配列、ブック、シート、コレクションを参照している部分がないかを見ます。

変数で名前を指定している場合は、**イミディエイトウィンドウ**を使うと原因を見つけやすくなります。

VBEでイミディエイトウィンドウを開き、`? bookName` や `? sheetName` と入力すると、現在の変数の中身を確認できます。

実際に表示された値が「Book1.xlsx」ではなく「Book1」だったり、「データ」ではなく空文字だったりすれば、エラーの原因は参照先ではなく変数に値を入れる処理にあると分かります。

エラー9を予防するには、**参照対象を明示する書き方を習慣にする**ことが効果的です。

`ActiveSheet` や省略した `Worksheets(“Sheet1”)` に頼りすぎると、アクティブなブックやシートが変わっただけで動作が変わります。

次のように、ブック、シート、範囲を段階的に変数へ入れると、読みやすくデバッグしやすいコードになります。

“`vba
Sub BetterCode()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Worksheets(“データ”)

ws.Range(“A1”).Value = “完了”
End Sub
“`

さらに、外部ブックやユーザー入力に依存する処理では、**存在確認を入れてから本処理に進む**設計が安全です。

ブックが開いているか、シートが存在するか、配列が初期化されているかを確認すれば、突然マクロが停止するリスクを減らせます。

最後に、エラー9の原因を探すときの優先順位を整理しておきます。

1. **シート名とブック名が正しいか**を確認
2. **配列の添え字が `LBound` から `UBound` の範囲内か**を確認
3. Dictionaryなどのキー、SheetsとWorksheetsの使い分け、セル範囲などの周辺要因を確認

この順番でチェックすると、無駄なく原因にたどり着けます。

まとめ

Excel VBAの「インデックスが有効範囲にありません（エラー9）」は、メッセージだけを見ると難しく感じますが、原因の多くは**「存在しないものを指定している」**ことに集約されます。

– シート名やブック名は完全一致で確認
– 配列は `LBound` と `UBound` で範囲を取得
– 変数の中身はイミディエイトウィンドウで確認

この基本手順を押さえておけば、エラー9の解決だけでなく、再発しにくいVBAコードを書く力も身につきます。

焦らず、一つずつ確認していきましょう！

広告