Excel VBA クラスコレクションの内容を書き出す方法

「Excel VBAのクラス」についての第15回目です。前回はクラスのコレクションに変更を加える方法について勉強しました。しかし、変更されたコレクションの内容は、そのままではExcel（VBA）が終了すると破棄されてしまいます。今回は、この変更されたコレクションの内容を書き出す方法について勉強します。

くるみこ

表データをクラスのインスタンスにして、集合化したコレクションに追加や削除などの変更を加えたとしても、メモリ上に存在しているだけです。どこかに書き出しておかないと目に見えるかたちで保存されません。どのようにしたらよいのか考えましょう。

そうですね。せっかく作った最終的なデータが一瞬で無駄になってしまうなんてダメです。
どうすればよいのか教えてください。今回もよろしくお願いしますm(_ _)m

はじめに
コレクションの要素を指定セル範囲に書き出す処理
1. コレクション要素を表データで書き出すメソッド
標準モジュール側のコード
1. 要素を書き出すメソッドを呼び出すプロシージャ
まとめ（おわりに）
1. まとめ
2. Excel VBA クラスについての記事一覧
記事のサンプルファイルをダウンロードできます

はじめに

表データのインスタンスをコレクション化し、要素を「追加」や「削除」して変更できるようになりました。

でも、その結果を出力する機能がまだありませんので、今回はこの部分を設定していきましょう。

【この記事でわかることは】
Excel VBA でコレクション化されたインスタンス要素をセル範囲に書き出す方法
　1 コレクション要素を書き出しメソッドの作成方法
　2 書き出し先セル範囲を指定する方法

使用する表データは前回のものをそのまま使います。

この記事で使用したサンプルファイルを登録しています。リンク先からご利用ください。

コレクションの要素を指定セル範囲に書き出す処理

これまで設定してきたクラスをもう一度確認しておきます。

「Class1」（名前がデフォルトのまま）クラスは、プロパティ設定とコンストラクタ直後に実行するに各プロパティを設定するメソッドを設定しています。
「clsCol」クラスでは、インスタンスをコレクション化するコンストラクタを設定しています。「itemAddメソッド」と「itemRemoveメソッド」を追加しました。

今回はコレクション化されているインスタンスの要素を全部書き出すのですから、このような手順で処理を行うようにします。

「clsCol」クラスに「データを書き込む処理」のメソッドを追加設定します。
書き込み先の場所は自由に指定できるよう、標準モジュールにプロシージャを設定します。

この追加する手順を、クラスのメソッド（プロシージャ）として設定することとします。

コレクション要素を表データで書き出すメソッド

ということで、コレクション設定用の「clsCol」クラスモジュールに、要素をセル範囲に書き出す「OutputResurtsメソッド」を新たに追加します。

'結果を指定セル範囲に出力する
Public Sub OutputResurts(ByVal rng As Range)
  Dim arr() As Variant
  Dim p As Class1 'Itemのプロパティ取得用
  Dim sh As Worksheet: Set sh = rng.Parent
  Dim wb As Workbook: Set wb = sh.Parent
  Dim r As Long: r = rng.Row
  Dim c As Long: c = rng.Column
  Dim i As Long: i = 1

  'コレクションItemを配列に変換する
  ReDim arr(0 To mycol.Count)
  '配列に列見出しを追加
  arr(0) = Array("ID", "Name", "Age", _
            "Pet.Name", "Pet.Age", "Pet.Types", "Pet.Gender")
  For Each p In mycol
    With mycol.item(i)
      '配列に要素ごとのプロパティ値を追加
      arr(i) = Array(.ID, .Name, .Age, _
                .Pet.Name, .Pet.Age, .Pet.Types, .Pet.Gender)
      i = i + 1
    End With
  Next
  'シートのセル範囲に書き出す
  For i = 0 To UBound(arr)
    With wb.Sheets(sh.Name) '別ブックもあるので
      .Range(.Cells(i + r, c), .Cells(i + r, c + 7 - 1)) = arr(i)
    End With
  Next
End Sub

【コード補足】

2行目、引数の Rangeオブジェクトは書き込み先のセルのRangeです。標準モジュールからの呼び出し時にRangeオブジェクトを乗せてくる感じです。
5～8行目、引数で受け取ったRangeオブジェクトから「ブック」「シート」「行」「列」を変数に設定しています。
12行目、配列のサイズを0～要素数をカウントして設定しています。要素数をマイナス「-1」しないのは「0」に見出し列を設定するからです。
14行目、配列「0」にArray(見出し列)で配列にして設定しています。
16～23行目で、配列に各要素(Item)の各プロパティを配列にして代入しています。
25～29行目が、シートのセル範囲に書き込んでいる部分です。
25行目が配列要素数のループ（配列は0からスタートです）
26行目の「wb.Sheets(sh.Name)」は、書き込み先が別ブックの場合があるためブックから設定しています。指定しないと同名のシート名だった場合書き込みが失敗する可能性があるためです。
27行目では、指定セルから列数分のセル範囲に配列の要素を一括で書き込んでいます。

標準モジュール側のコード

標準モジュール側には「書き込み先を指定するプロシージャ」を設定して、基本部分（インスタンス化→コレクション化→追加や削除）の後にそれを呼び出すように設定します。

'標準モジュール
Sub rngCollectionTest()
  Dim table As clsCol
  'インスタンス作成⇒コンストラクタ起動
  Set table = New clsCol

  'コレクションに要素を追加する
  Call ColAddItem(table)

  'コレクションの内容を書き込む
  Call OutputRangeSelect(table)

End Sub

最後に、書き込むためのプロシージャ「OutputRangeSelect(table)」を Call しています。

要素を書き出すメソッドを呼び出すプロシージャ

Application.InputBox を使って書き出し先を任意に指定できるようにします。もちろん別のブック内のセル範囲への書き出しもできます。

'OutputResurtメソッドに渡す書き込み先設定
Sub OutputRangeSelect(ByVal table As clsCol)
  Dim rng As Range
  Do
    On Error Resume Next
    Set rng = Application.InputBox( _
      Prompt:="書き出し先の先頭セルを選択してください", _
      Title:="セル選択", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    If rng.Count <> 1 Then MsgBox "選択が単一セルではありません！"
  Loop Until rng.Count = 1
  '要素を出力するメソッドへ
  Call table.OutputResurts(rng)
End Sub

【コード補足】

6行目、Application.InputBox の Type:=8 でセルを選択指定する設定（Rangeオブジェクト）にしています。書き込みたい範囲の先頭セルを選択するようにメッセージしています。
10行目、キャンセルが選択された場合処理を終了します。
11行目、選択したセルは単一ではない場合、メッセージを出します。
12行目は、Do Loop処理の条件は Until rng.Count = 1 として、選択が単一セルになるまでループさせる設定にしています。
※ 要素を追加する処理では GoTo ステートメントで再度設定させるようにしていました。
14行目の Call table.OutputResurts(rng) で要素を書き出すメソッドに処理を渡します。

・セル選択の際に表示するインプットボックス

・セルが複数選択された場合に表示されるメッセージ

・実行動画（mp4）
　はじめに1要素追加して、次に書き出し先を選択して結果を書き出しています。

まとめ（おわりに）

以上、Excel VBA クラスでコレクション化されたインスタンスの要素を指定したワークシートのセル範囲に書き出す方法について紹介しました。

くるみこ

クラスの15回目はいかがでしたか。コレクション化されているインスタンス要素を、指定したセル範囲に書き出す方法でした。別ブックのセル範囲への書き出しにも対応できます。

ただし、今回は書き込み先のセル範囲の状況を調べる機能は設定していません。上書きで書き込んでしまうので注意してください。

はい。コレクションから要素ごとのプロパティ値を配列に変換して配列に代入していくんですね。書き込む際には単一セルのRangeオブジェクトからセル範囲を指定して行数分ループ処理で配列の値を書き込むいく感じですか。
配列にしなくても書き込めると思うのですがいかがですか？

くるみこ

書き込む手順についてはよく理解できていると思ます。
配列にしなくても書き込みは可能です。特に要素にオブジェクトが含まれる場合は配列変換が使えないので直接ループ処理で書き込みます。
ただし、速度やメモリの点で配列変換の方が優れています。コレクションから直接ループ処理でセルに書き出す場合は、速度が遅くなる可能性やメモリ使用量も増加します。

コレクションの要素数が多い場合は、配列変換を使いましょう。