【ExcelVBA】ReDimPreserveで「実行時エラー’9′:インデクスが有効範囲にありません。」は要素の位置が原因

ExcelVBAで配列の値を保持したまま要素数を変える事ができるReDimPreserveですが、コードの書き方を誤るとエラーになってしまいます。

この記事は、

配列の値を保持したまま要素数を変えたい
実行時エラー’9′:インデクスが有効範囲にありません。の原因がわからない

こんな方に向けてコードの正しい書き方とエラーの解決方法を説明します。

エラーの原因
正しい書き方とエラーになる書き方
- 正しい例で書いたコード
- エラーになる例で書いたコード
VBAはどうやって覚える？
まとめ

エラーの原因

２次元配列の一番最後以外の要素数を変更しようとしているからです。

ReDim Preserveで要素数を変更できるのは一番最後の要素だけです。

正しい書き方とエラーになる書き方

リストという２次元配列の変数が既にあり、
その値を保持したまま要素数を追加したいとします。
この場合はReDim Preserveを使い要素数を変更します。

ReDimは要素数を変更する。
Preserveは要素の値を保持させる。

２つコードの例を書きましたが片方はエラーになります。

ReDim Preserve リスト(1 To 3, 1 To cnt)

ReDim Preserve リスト(1 To cnt, 1 To 3)

GOODの例は要素２を変更しようとしています。
BADの例は要素１を変更しようとしています。

エラーの原因はこの変更する要素の位置が関係しています。

正しい例で書いたコード

Sub 二次元配列要素追加テスト成功例完成版()
Dim リスト1()
リスト1 = Range("A1:C6")

Dim リスト2()
ReDim リスト2(1 To 3, 1 To 1)

cnt = 1
For i = 1 To UBound(リスト1)
    改行処理セル = リスト1(i, 2)
    If InStr(改行処理セル, vbLf) > 1 Then
        '改行がある場合は改行分リストのコピーを繰り返す
        For Each 改行処理セルの値 In Split(改行処理セル, vbLf)
            ReDim Preserve リスト2(1 To 3, 1 To cnt)
            リスト2(1, cnt) = リスト1(i, 1)
            リスト2(2, cnt) = 改行処理セルの値
            リスト2(3, cnt) = リスト1(i, 3)
            cnt = cnt + 1
        Next
    Else
        '改行がない場合はリストをそのままコピー
        ReDim Preserve リスト2(1 To 3, 1 To cnt) '「1 To cnt, 1 To 3」→「1 To 3, 1 To cnt」で解消
        リスト2(1, cnt) = リスト1(i, 1)
        リスト2(2, cnt) = リスト1(i, 2)
        リスト2(3, cnt) = リスト1(i, 3)
        cnt = cnt + 1
    End If
Next

Range("A10:C20").Clear
Range("A10:C20") = WorksheetFunction.Transpose(リスト2)
End Sub

２２行目でReDimPreserveを使っていますが、こちらのコードは正しく動作します。

エラーになる例で書いたコード

Sub 二次元配列要素追加テスト失敗例()
Dim リスト1()
リスト1 = Range("A1:C6")

Dim リスト2()
ReDim リスト2(1 To 1, 1 To 3)

cnt = 1
For i = 1 To UBound(リスト1)
    改行処理セル = リスト1(i, 2)
    If InStr(改行処理セル, vbLf) > 1 Then
        '改行がある場合は改行分リストのコピーを繰り返す
        For Each 改行処理セルの値 In Split(改行処理セル, vbLf)
            ReDim Preserve リスト2(1 To cnt, 1 To 3)
            リスト2(cnt, 1) = リスト1(i, 1)
            リスト2(cnt, 2) = 改行処理セルの値
            リスト2(cnt, 3) = リスト1(i, 3)
            cnt = cnt + 1
        Next
    Else
        '改行がない場合はリストをそのままコピー
        ReDim Preserve リスト2(1 To cnt, 1 To 3) 'ここで「実行時エラー'9':インデクスが有効範囲にありません。」
        リスト2(cnt, 1) = リスト1(i, 1)
        リスト2(cnt, 2) = リスト1(i, 2)
        リスト2(cnt, 3) = リスト1(i, 3)
        cnt = cnt + 1
    End If
Next

Range("A10:C20").Clear
Range("A10:C20") = リスト2
End Sub

２２行目のReDim Preserve リスト2(1 To cnt, 1 To 3)で
「実行時エラー’9′:インデクスが有効範囲にありません。」が発生します。

なので変更する要素の位置を後ろに変えればいいのですが、
ここで問題が発生します。

３１行目にあるリスト２という配列をRangeのセルに貼り付けるコードです。

Range(“A10:C20”) = リスト2

Rangeに貼り付ける配列の要素は（行、列）の順番にする必要がありますが、
エラーの対策で要素の位置を入れ替えると変数の値が（列、行）になってしまいます。

そこでRangeに貼り付ける時は行と列の入れ替えを行うWorksheetFunction.Transpose()を使います。

Range(“A10:C20”) = WorksheetFunction.Transpose(リスト2)

こうすることでReDimPreserveのエラー対策で入れ替えた要素を元に戻してセルに貼り付けることができます。

VBAはどうやって覚える？

プログラムのコードが読めないと、参考にしているコードの理解や自分でプログラムを書くときに時間がかかってしまいます。
そんな時は基礎から学ぶと応用もできるようになり自分の作りたいプログラムがスムーズに作れるようになります。

無料でやるならネット上の学習サイトを参考にするといいです。
こちらはVBAの基礎から説明している無料のWEBサイトになります。

>>基礎から学べるWEBサイトを見てみる

過去にネットでの学習でつまずいた方には専門書をオススメします。
本なら基礎から学べる事はもちろん、読者視点で分かりやすい解説になっています。
一人で集中してコツコツ進めたい方は書籍を試してください。