ReDimPreserveで「実行時エラー’9′:インデクスが有効範囲にありません。」が発生

記事としてはReDimPreserveで発生したエラーの解決方法なんだけど、なぜそうなったのかも説明したいので元々の目的から念のため書いておく。

Windowsサーバ運用あるあるでイベントビューアのログ確認が面倒というのがある。

過去発生したイベントログはExcelに書き溜めていくのでIDで検索やフィルタしたりするが、これがだるい。

サーバからイベントログを抽出してExcelのナレッジと突き合わせる事で解決させる事にした。

やりたい事

この記事で書いている内容はプロセスの3番目です。

1.wevtutilコマンドでイベントログを抽出

2.イベントログをExcelへ読み込み

3.Excelのナレッジを1情報1行に変換←この記事のコード

4.vlookup関数で過去の有り無しを表現

サンプルプログラムの動作

ちょっとわかりにくいけど1つのセルに改行されて2つ書かれていたら、それを1行に分けます。

サンプルプログラム

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

失敗例

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′:インデクスが有効範囲にありません。」が発生します。

なぜかというと2次元配列の場合最後の要素しか変えれないから。

今回はホスト名が複数ある場合行数を増やしたいんだけど、

Rangeから配列に持ってくると行,列の順で格納されるので行が最初の要素になってしまう

だからダメでした。

  • ×:リスト2(1 To cnt, 1 To 3)
  • ○:リスト2(1 To 3, 1 To cnt)

こうすれば要素を増やしているcntが最後の要素になるのでOK。

その代わり要素の位置が逆転しているのでRangeヘ貼り付けるときは左右を入れ替える必要がある。

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

この「WorksheetFunction.Transpose」は要素入れ替え以外でも配列をセルに貼り付ける際に重宝するので存在は覚えておいた方がいい。

コメント

タイトルとURLをコピーしました