Excel VBAでセルの値を配列へ格納する処理を作成していると、空白セルを読み込んだだけなのに実行時エラー9が発生することがあります。しかし、このエラーの原因はセルが空欄であることではなく、配列の指定範囲や添字の扱いに問題があるケースがほとんどです。この記事では、VBAの実行時エラー9が発生する原因と、空白セルを含むデータを安全に処理する方法について解説します。
VBAの実行時エラー9は空白セルが原因ではない
VBAの「実行時エラー9 インデックスが有効範囲にありません」は、配列やコレクションなどで存在しない番号を指定した場合に発生するエラーです。
そのため、Cells(j,20)やCells(j+1,20)の値がEmpty（空欄）であっても、それ自体が原因でエラー9になることはありません。
例えば、以下のような処理ではセルが空欄でも問題なく文字列として連結できます。
Dim x As String
x = Cells(10,20).Value & ” ” & Cells(11,20).Value
空欄の場合はEmptyが文字列連結時に変換され、結果として空文字に近い状態で処理されます。
配列Miの宣言範囲がエラーの原因になる可能性
提示されているコードでは、最初に以下のように配列を宣言しています。
Public Mi(500, 12) As String
この場合、配列Miで使用できる範囲は1次元目が0～500、2次元目が0～12です。つまり、Mi(Mis,13)という指定は範囲外になります。
VBAでは配列の添字は宣言した範囲内でしか利用できません。そのため、以下の行でエラー9が発生します。
Mi(Mis, 13) = Cells(j, 20) & ” ” & Cells(j + 1, 20)
2次元目の最大値が12なのに13番目へ書き込もうとしているためです。
配列の範囲を修正する方法
13列目まで使用したい場合は、配列宣言を修正する必要があります。
例えば、以下のように変更します。
Public Mi(500, 13) As String
または、0からではなく1から500まで利用したい場合は、次のように明示することもできます。
Public Mi(1 To 500, 1 To 13) As String
このように範囲を明確にしておくと、配列番号の間違いによるエラーを防ぎやすくなります。
空白セルを含む場合に安全に処理する方法
今回のようなセル結合処理では、空白セルが含まれていてもエラーにならないように値を確認してから代入する方法があります。
例えば、以下のように記述できます。
If IsEmpty(Cells(j,20).Value) Then
Mi(Mis,13) = Cells(j+1,20).Value
ElseIf IsEmpty(Cells(j+1,20).Value) Then
Mi(Mis,13) = Cells(j,20).Value
Else
Mi(Mis,13) = Cells(j,20).Value & ” ” & Cells(j+1,20).Value
End If
ただし、この処理は空白時の文字列処理を調整するためのものであり、エラー9そのものを解決するものではありません。まずは配列範囲を確認することが重要です。
Misの型にも注意する
コードではMisをSingle型で宣言しています。
Public Mis As Single
しかし、配列の添字として利用する変数は整数型にすることが一般的です。Single型の場合、小数値を保持できるため、意図しない値になる可能性があります。
以下のようにInteger型またはLong型へ変更することをおすすめします。
Public Mis As Long
特に今回の処理では、jが10から50000まで増加するため、Integer型よりLong型のほうが安全です。
セル参照時はワークシート指定も確認する
Cellsだけを記述すると、現在アクティブになっているシートを参照します。
複数のシートを扱う処理では、意図しないシートを参照してエラーやデータ不一致の原因になる場合があります。
例えば以下のようにシートを指定すると、安定したコードになります。
Worksheets(“Sheet1”).Cells(j,20).Value
配列エラーとは直接関係ありませんが、VBAでは参照先を明確にすることでトラブルを減らせます。
まとめ｜VBAエラー9は配列番号を最初に確認する
今回のようなケースでは、Cells(j,20)やCells(j+1,20)が空欄であることが原因で実行時エラー9が発生しているわけではありません。
主な原因は、宣言した配列の範囲を超えてMi(Mis,13)へアクセスしていることです。Public Mi(500,12)では13番目の列は利用できないため、配列の宣言範囲を修正する必要があります。
また、添字に使う変数はLong型に変更し、空白セルの処理は必要に応じてIsEmptyなどで確認すると、より安定したVBAプログラムになります。
コメント