何度もこのエラーに遭遇するのには理由がある

エラー9「インデックスが有効範囲にありません」は、例えば配列の要素数が3なのに4つ目にアクセスするなどの配列の範囲外へアクセスや、VBAの各種コレクションに含まれない値を指定した場合などの存在しない引数が原因なのですが、その原因を知っていてもなぜか同じようにこのエラーを発生させてしまう方がいます。

このページでは、エラー自体についての説明ももちろん書いていますが、そもそもなぜこのエラーを発生させてしまうのかという根本原因についても書いています。

VBAではこのエラーにはまることがとても多いのです。

その根本原因を知っているかどうかで今後このエラーに対する対応が劇的に速くなるはずです。

エラーの根本原因

このエラーは非常に多発するエラーです。そのためVBAに詳しくこのエラーには慣れている方は、ネットで調べるまでもなく自分で解決していることが多いでしょう。

このエラーについて説明しているサイトのほぼ全てが、「配列と要素数」というエラー自体のキーワードについて説明していると思いますが、それはそういうエラーが発生したという事象にすぎません。問題の根本はそこではありません。

おそらく今ここを見ている方は今から説明する２つの根本原因のどちらか、もしくは両方について知らなかったり慣れていないのではないかと思います。

根本原因は以下の２つがあります。

1. エラーメッセージの意味が分からない

根本原因の１つはエラーメッセージを読んでも意味が分からないということです。

意味が分からないことには理由があります。それはメッセージの日本語がおかしいためです。

「実行時エラー ‘9’: インデックスが有効範囲にありません。」というエラーメッセージを初めて見たときに、配列やコレクションのエラーだと気付くのはかなりプログラミングに慣れた人でしょう。

このメッセージは主語が間違っています。本当の主語は「インデックス」ではなく「配列」です。実際は配列だけでなくコレクションなども対象なのですがエラーの概念は同じなので、ここではまとめて配列とします。

もしエラーメッセージが「配列の番号が配列の範囲（要素数）を超えているため配列への参照や設定ができません」と書いてあればどうでしょうか。初めてそれを見ても理解できて、あとは自分で対応できる方は結構多いのではないかと思います。

主語がないと意味が分かりませんからね。

2. 配列の最後の番号が要素数から１引いた数であることを強く認識できていない

もう１つの根本原因は配列の最後の番号が何になるのか分からないという点です。

通常は配列が0開始であるということは理解されていると思いますが、では配列の最後は？と聞かれたときにすぐにこたえられるかどうかです。

もし答えられないのであれば、配列の先頭が0である、ということと同じぐらいに、配列の最後は要素数マイナス１、と覚えておきましょう。

このエラーが発生するのはほとんどがループ処理の終端処理をミスしていることによります。言い方を変えるとループの終了条件と配列の最後を一致させることができていない、ということになります。以下に配列の説明を３つ書いていますが、概念としては同じことを言っています。

「配列の先頭は1ではなく0から始まる」
「配列の2番目を指定するときは番号は1を設定する」
「配列の最後は最大要素数から1を引いた番号を指定する」

もしこれを見てモヤモヤする部分が少しでもあるなら、それがこのエラーの根本原因です。

配列に、”aaa”と”bbb”と”ccc”の３つを入れてある場合、要素数は3になります。これは分かると思います。そして配列の番号は0、1、2です。これもわかると思います。では、配列の最後の番号の2とは何の数字なのか理解できているでしょうか。

ここが重要です。

特に、要素数と最後の番号は1ずれているという点です。ただ、VBAの場合、このあたりがコードで書くとわかりにくく感じる場合があります。

このコードの2行目には配列の要素数を2と指定し、3行目で配列番号2を指定しています。

このコードがやりたいのは、要素を３つ持つ配列の最後の要素に”ccc”を代入したい、ということですが、要素数が3であることが一目で分かりません。

このように、コードだけを見ると要素数と最大要素番号がずれていることに気が付かないことがあります。

分からないのには理由があります。それは、Redimで指定するのは要素数マイナス1であり、また、UBound関数が要素数ではなく要素数マイナス１を返すためです。

ループ終端を判断する際に、配列の要素数を求めるためにUBound関数を使います。ただ、注意が必要なのが、UBound関数が取得するのは要素数ではなく最大の要素番号（要素数から１引いた値）である、ということです。

配列に、”aaa”と”bbb”と”ccc”の３つを入れてある場合、要素数は3になります。これは分かると思います。しかしUBound関数は3ではなく2を返します。ここが人間の感覚と違います。それで「あれ？」ってなることがあります。

UBound関数についての詳細は「配列の要素数を取得する（UBound、LBound）」をご参照ください。

大事なことなので繰り返し書きますが、配列が0開始ということは、配列の最後の番号が要素数から１引いた数ということであることを覚えておきましょう。そしてUBound関数は要素数ではなく要素数マイナス１を返します。その知識が身についていれば本件エラーに遭遇する機会は随分減るはずです。

ここがこのエラー発生の根本原因だということを知っているのといないのとではコードの精度は違ってきます。結果的にループ終了条件の誤りが減り、このエラーの回避になります。

一度でいいのでサイトや本を見るだけでなく、自分で配列の絵と各要素の番号の絵を書いてみることをお勧めします。そして、配列の最後の要素と配列の要素数の関係を確認しましょう。その一度が今後の配列の理解に大きく役立ちます。

以上が本件エラーの根本原因の説明です。では以下はエラー自体についての説明をします。

エラーの原因

VBAの実行時に「実行時エラー ‘9’: インデックスが有効範囲にありません。」というエラーになることがあります。エラーの原因は、配列の最大要素を超えた要素にアクセスすることがエラーの原因です。

例えば、日曜日から土曜日までの７日間の配列を用意する場合、配列の要素は７つの領域を用意することになります。ところが誤って６つしか領域が用意されていない状態の場合に、７つ目の領域にアクセスするとエラー９になります。

上のコードは設定時のエラーの例ですが、配列の値を参照しようとした場合もエラーになります。

エラーの対応方法

エラーの対応方法は、配列設定時や参照時のループ終了条件を正しく設定することでほとんど解決します。

配列のループ処理であればForループとUBound関数やFor Each構文を使うのが分かりやすくていいと思います。

For + UBoundでの書き方

12行目で要素数を取得し、それを15行目のForループで使用する方法です。

For Eachでの書き方

12行目のFor Eachで配列の各要素値を取得する方法です。この方法が一番簡単です。

Doループなどの終了条件がないループ構文での書き方

Doループなどの終了条件がないループ構文を使う場合は、配列処理を行う前に終了条件を書くようにする、などのルールを決めておくとよいです。

 

終了条件を書く位置を決めておく

配列の処理は出来るだけFor EachやFor文で書いた方が確実です。しかし、どうしてもDoループなどを使う必要がある場合は、終了条件をどこに書くかを自分の中で決めておいた方がよいです。

終了条件をループの先頭に書いたり最後に書いたり途中に書いたり、とバラバラだと、都度終了判定が > なのか >= なのかなどを考えなければなりません。

私は上のサンプルのように「終了条件はループの先頭に書く」と決めています。

もちろん例外はありますが、基本的なルールを決めておいた方が本件エラーに悩まされることは少なくなります。