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

カテゴリ：VBA・マクロ

「インデックスが有効範囲にありません」と出たとき、コードが指定するブックやシートが、参照先にないことがあります。名前を実際の処理対象と照合してみましょう。

ブック名やシート名を指定した行で止まる場合の直し方です。

1．指定したブックとシートを確認する

次の行で止まったとします。wsはWorksheet型として宣言済みの変数です。

Set ws = Workbooks("売上集計.xlsm").Worksheets("9月")

このコードは、マクロを動かしているExcelで開いている「売上集計.xlsm」の中から、「9月」というワークシートを探します。ブックとシートのどちらかが見つからなければ、エラー9の原因になります。

保存先にファイルがあるだけでは、Workbooksで参照できません。別に起動したExcelで開いている場合や、保護ビューで表示中の場合も、そのまま同じ扱いにはできません。

2．実際の処理対象の名前へ直す

編集前のブックを別に残します。この例では、開いている「売上集計.xlsm」の処理対象が「10月」シートだと確認できたら、「9月」を「10月」へ直します。

Set ws = Workbooks("売上集計.xlsm").Worksheets("10月")

ブック名は拡張子まで、シート名はタブの文字と照合します。「売上集計.xlsx」と「売上集計.xlsm」、「10月」と「10月分」は別の名前です。Workbooksの括弧にはブック名を指定します。保存先のフルパスを入れる場所ではありません。

3．ThisWorkbookへの置き換えは対象を確認してから

マクロが入っているブック自体を処理する場合は、次のように書けます。

Set ws = ThisWorkbook.Worksheets("10月")

別ブックを処理するコードまで、一律にThisWorkbookへ変えないでください。個人用マクロブックにコードがある場合も、処理したいブックとは別です。

ActiveWorkbookは、アクティブなウィンドウのブックを指します。手前のブックを切り替えると対象が変わるため、「どれでも動く」置き換えにはなりません。

4．再実行前に出力先を確認する

途中まで転記・保存されていないか確認し、二重処理を避けます。コピーで試す場合も、固定のブック名や保存先が原本を指していないか確かめてください。参照先が分からないまま再実行せず、処理後は対象シートと出力内容を照合します。

コードに合わせて空シートを追加したり、On Error Resume Nextでエラーを隠したりすると、参照先の取り違えを見落とします。配列の番号で止まるエラー9は、今回とは別の確認が必要です。

カスタマイズやご要望がある場合は、お気軽にご相談ください。

https://coconala.com/users/1867172