ユーザー定義型変数との組み合わせ例（リスト3）

　今作成したマクロ「案内状」は、クエリー結果を1度ワークシートのセルに転送して使うようにしました。しかしこれだと、件数の多いクエリーの場合は、ワークシートにデータを転送する分だけ処理に時間がかかってしまいます。
　そこで、クエリー結果をワークシートに転送せずに、ユーザー定義型の変数を使って、オンメモリで処理を行なわせるように変更してみます（なお、以降の説明では、修正した部分のコードのみ掲載します）。

まずマクロを分けるために、プロジェクトにもうひとつ標準モジュール（Module3）を追加します。
　そして、標準モジュール「Module3」の宣言セクションに、次のユーザー定義のデータ型を宣言します。

Type CustData
    Id As String
    jusyo As String
    simei As String
    hizuke As String
End Type

　このユーザー定義のデータ型の各要素は、上から順番に「会員番号」「住所」「氏名」「入会年月日」を格納する変数です。すべて、データベースのデータと同じ文字列型で宣言します。
　そしてこのデータ型を元に、変数「QueryData」を宣言します。この時、この変数を要素数をもたない配列で宣言しておきます。

Dim QueryData() As CustData

　なぜなら、クエリーの実行結果で抽出されるレコード数は、クエリー結果によって異なるからです。1件の抽出があれば、3件の抽出もありうるわけですから、いくつの配列を必要とするかは、クエリーの結果を見てから決定するようにします（これを配列の動的確保と言います）。そのため、この時点では要素数をもたない配列として宣言しておきます。
　また、クエリー結果の総レコード数を格納する変数「Rcdcnt」をいっしょに宣言しておきます。これらの変数は、プロシージャ「RunQuery」と「案内状作成」の2つのプロシージャで使用するため、モジュールレベルの変数として宣言します。

Dim Rcdcnt As Integer

メインのプロシージャは特に変更ありませんので、そのままにしておきます。なお、このマクロでは、先に作成したマクロと内容が混乱しないように、すべてのプロシージャ名を「xxx2」に変更しています。

Sub 案内状2()
    Dim IPTData As String
    IPTData =  _
    InputBox("作成する月名を入力してください。", "案内状作成")
    IPTData = "*/" & IPTData & "/*"

    If RunQuery2(IPTData) = True Then
        Call 案内状作成2
    End If
End Sub

今度は、クエリーを実行するFunctionプロシージャ「RunQuery」を修正し、「RunQuery2」にします。これにより、プロシージャの戻り値を格納する変数も「RunQuery」から「RunQuery2」に変更します。

If QResult.RecordCount = 0 Then
    MsgBox "該当データがありません"
    RunQuery2 = False
    Exit Function
End If
............
............
............
RunQuery2 = True

クエリーを実行し、OpenRecordsetメソッドを実行してクエリーテーブルを開いたら、MoveLastメソッドでカレントレコード位置をクエリーテーブルの最後尾に移動します。そして、全レコード数をRecordCountプロパティから取得し、モジュールレベルで宣言した変数「Rcdcnt」にいったん格納します。

QResult.MoveLast
Rcdcnt = QResult.RecordCount

　そしてこの変数を使って、メッセージボックスで抽出データの件数を表示します。

MsgBox Rcdcnt & "件のデータを抽出しました。"

次に、クエリーテーブルからレコードデータを取得する処理を行ないますが、この部分をすべて修正し、ワークシートに転送するのではなく、配列で宣言したユーザー定義型変数「QueryData（）」に格納するようにします。
　まず、ReDimステートメントで抽出データを格納するユーザー定義型変数「QueryData」に、配列の要素数を指定して再宣言します。
　抽出レコードの総数は、変数「Rcdcnt」に格納されていますので、この数字分だけ配列を確保すればいいことになります。そこで、配列の要素数に変数「Rcdcnt」を指定し、ReDimステートメントで配列数を決定します。キーワード「Preserve」を付けると、直前の配列数を動的に変更することができます。

ReDim Preserve QueryData(Rcdcnt)

　これで、変数「QueryData」は、クエリー結果の全レコード分の配列が確保されました。
　次に、カレントレコードをテーブルの先頭位置に移動し、For...NextループとMoveNextメソッドを組み合わせて、カレントレコード位置をひとつずつ移動しながら、4つのフィールドに格納されている値を、配列の変数「QueryData」に転送します。
　この時、変数「QueryData」の添え字にループのカウンタ用変数「i」を指定し、ループの回数と配列のインデックス番号が一致するようにしておきます。

QResult.MoveFirst
For i = 1 To Rcdcnt
    With QueryData(i)
        .Id = QResult.Fields(0).Value
        .simei = QResult.Fields(1).Value
        .hizuke = QResult.Fields(2).Value
        .jusyo = QResult.Fields(3).Value
    End With
    QResult.MoveNext
Next

　例えば、クエリーの結果、3件のレコードが抽出されれば、変数「QueryData」は「QueryData(3)」という配列数で再度宣言され、「QueryData(1)」に1件目のレコードデータが、「QueryData(2)」に2件目のレコードデータが格納されるようになります。

これで、クエリーによる抽出結果のデータを全件、ユーザー定義型の変数「QueryData」に格納しました。あとは、ここからデータを取り出して、案内状の作成に使用するだけです。
　プロシージャ「案内状作成2」は、基本的にはワークシートからデータを取り出して案内状を作成する処理と同じですが、データの取り出し元がセルではなく変数「QueryData」になっている点が違います。
　また、ここではループを使用して、抽出した全員分の案内状を一気に作成してしまいます。この時、ワークシート名に変数を使用して、「作成1」「作成2」「作成3」と番号を組み合わせた名前にしています（図9）。

図9：抽出した全員分の案内状をいっきに作成

Sub 案内状作成2()
    Dim i As Integer

    For i = 1 To Rcdcnt
        Worksheets("案内状原紙").Copy ＿　　
　　　　　After:=Worksheets("Sheet3")
        With ActiveSheet
            .Name = "作成" & i
            .Range("B1") = QueryData(i).Id
            .Range("A3") = QueryData(i).jusyo
            .Range("C4") = QueryData(i).simei
        End With
    Next
End Sub

ワンポイントアドバイス

　この例では、ReDimステートメントによって「QueryData(3)」と再宣言していますが、これだと配列の要素は4つ確保されます。「QueryData(0)」から「QueryData(3)」の4つということです。
　一方、レコード番号は「1」から始まりますので、配列の添え字とレコード番号を一致させるために、「QueryData(0)」は使わず、「QueryData(1)」からデータを格納するようにしています。使わない配列をひとつ余分に宣言するため、メモリの無駄な消費になりますが、わかりやすいコードにするためにあえてこのような処理にしています。