【実務・中級編】DAOのRecordsetをExcelへ転送する際のメモリ管理と高速化 – Access VBA解析バイブル

Access VBA

2026.11.09

「Cell-by-Cell」の呪縛を解け：DAO RecordsetをExcelへ高速転送する極限の設計思想

「Cell-by-Cell」の呪縛を解け：DAO RecordsetをExcelへ高速転送する極限の設計思想

業務自動化の現場において、多くの開発者が陥る「致命的な罠」がある。それは、データベースから取得した大量のデータを、Excelのセルへ一つずつ書き込んでいく、いわゆる「Cell-by-Cell（セル逐次書き込み）」の手法だ。

数件のデータなら問題ない。しかし、数万件、数十万件という実務レベルのデータ量に直面したとき、このコードは牙を剥く。処理時間は指数関数的に増大し、ユーザーの待ち時間は数分、時には数十分へと膨れ上がる。

なぜ、これほどまでに遅いのか？なぜ、メモリ不足（Out of Memory）でクラッシュするのか？

本記事では、Access VBAの深淵に潜む「オブジェクトのライフサイクル」と「COM（Component Object Model）の境界コスト」を紐解き、大量データを一瞬でExcelへ叩き込むための「配列バッファ戦略」を伝授する。

1. なぜ「セルへの直接書き込み」は遅いのか：アーキテクチャの視点

エンジニアとして、単に「遅いから別の方法を使う」という思考では不十分だ。「なぜ遅いのか」を構造的に理解せよ。

COM境界のオーバーヘッド

VBAからExcelのセルに値を書き込む際、プログラムは「VBAエンジン」から「Excelのオブジェクトモデル（COM）」へとコンテキストを切り替える。
`Cells(i, j).Value = data` という一行を実行するたびに、この高コストな「境界越え（Context Switch）」が発生している。

Cell-by-Cellの場合: $N$ 行 $\times$ $M$ 列のデータがあれば、$N \times M$ 回の境界越えが発生する。
配列一括転送の場合: データをメモリ上の配列に格納し、最後にたった1回の境界越えでExcelへ流し込む。

この差は、単なる速度差ではない。設計思想の差だ。

2. 黄金律：配列バッファ戦略（Array Buffer Strategy）

高速化の極意は、「データベースからメモリ（Variant配列）へ、メモリからExcelへ」という二段構えの転送にある。

戦略のステップ

1. DAO Recordset を開き、データを取得する。
2. Recordsetのレコード数とフィールド数に基づき、Variant型の2次元配列を `ReDim` で確保する。
3. Recordsetをループさせ、値を配列に詰め込む。
4. Excelの範囲（Range）に対して、配列を一括代入する。

ここで重要なのは、Recordsetから直接Excelへ飛ばすのではなく、一旦「VBA内のメモリ空間（配列）」をバッファとして介在させることだ。これにより、データベースのカーソル操作とExcelの描画操作を完全に分離できる。

3. 実戦投入：堅牢かつ高速なプロダクションコード

以下に、実務でそのまま利用可能な、エラーハンドリングとメモリ管理を徹底したコードを示す。

Option Explicit

''' <summary>
''' DAO Recordsetの内容をExcelシートへ高速に転送する
''' </summary>
''' <param name="rs">転送元のDAO.Recordsetオブジェクト</param>
''' <param name="targetSheet">転送先のExcel.Worksheetオブジェクト</param>
Public Sub FastExportRecordsetToExcel(ByRef rs As DAO.Recordset, ByRef targetSheet As Worksheet)
    On Error GoTo ErrorHandler

    ' 1. 事前チェック：Recordsetが空でないか
    If rs Is Nothing Then Err.Raise vbObjectError + 1, "FastExport", "RecordsetがNothingです。"
    If rs.EOF Then
        MsgBox "転送するデータが存在しません。", vbInformation
        Exit Sub
    End If

    ' 2. 変数宣言
    Dim rowCount As Long
    Dim colCount As Long
    Dim i As Long, j As Long
    Dim dataArray() As Variant ' メモリバッファとなる2次元配列
    Dim startCell As Range

    ' 3. データの次元を特定
    ' ※DAOのRecordCountはCursorTypeによっては正確ではないため、
    ' 確実にカウントするために一旦末尾へ移動するか、事前に設計された取得方法を用いる必要がある。
    ' ここでは、既にRecordCountが取得可能な状態（dbOpenDynaset等）を前提とする。
    rowCount = rs.RecordCount
    colCount = rs.Fields.Count

    ' 4. 配列の動的確保 (1から始まるインデックスで確保するとExcelへの代入がスムーズ)
    ReDim dataArray(1 To rowCount, 1 To colCount)

    ' 5. Recordsetから配列へのデータ転送（メモリ内操作）
    ' ここが最速のポイント：セルに触れず、メモリ上の配列のみを操作する
    i = 1
    Do Until rs.EOF
        For j = 1 To colCount
            dataArray(i, j) = rs.Fields(j - 1).Value
        Next j
        i = i + 1
        rs.MoveNext
    Loop

    ' 6. Excelへの一括書き込み
    ' セル一つずつではなく、Rangeオブジェクトに対して配列を一度に流し込む
    Set startCell = targetSheet.Cells(1, 1)
    
    ' 描画停止による高速化（お作法）
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' Rangeのサイズを配列のサイズに合わせて一気に拡張し、代入
    targetCellRange(startCell, rowCount, colCount).Value = dataArray

    ' 7. 後処理
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    MsgBox "転送完了: " & rowCount & " 件のデータを処理しました。", vbInformation

ExitProcedure:
    ' メモリ解放の徹底
    Erase dataArray
    Exit Sub

ErrorHandler:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "エラーが発生しました: " & Err.Description, vbCritical, "Error " & Err.Number
    Resume ExitProcedure
End Sub

''' <summary>
''' 開始セルから指定した行数・列数分のアドレスを返すヘルパー関数
''' </summary>
Private Function targetCellRange(ByVal startCell As Range, ByVal rows As Long, ByVal cols As Long) As Range
    Set targetCellRange = startCell.Resize(rows, cols)
End Function

4. アーキテクトの視点：運用上の注意点と設計の勘所

このコードを「ただのコピペ」で終わらせてはいけない。プロフェッショナルとして、以下の3点を肝に銘じておけ。

① RecordCountの罠

DAOの `Recordset.RecordCount` は、カーソルタイプ（`dbOpenForwardOnly` など）によっては、全レコードをスキャンするまで正確な値を返さない。
大量データを扱う場合は、必ず `dbOpenDynaset` を使用してRecordsetを開くこと。さもなければ、配列の `ReDim` に失敗するか、データが欠落する原因となる。

② メモリ消費量（Memory Footprint）

`Variant` 配列は非常に強力だが、メモリを大量に消費する。
例えば、$100,000$ 行 $\times$ $50$ 列のデータを `Variant` 配列に格納すると、それだけで数百MBのメモリを占有する可能性がある。
もし、実行環境のメモリが極端に少ないクライアントPCで動かすツールを作るなら、「1万件ずつ分割して転送する」といったチャンク（分割）処理を検討すべきだ。

③ 型の整合性

`rs.Fields(j).Value` を配列に格納する際、データベース側が `Null` を返すと、VBAの `Variant` は適切に処理できるが、Excelのセルに書き込む際に型変換のコストが発生する場合がある。
可能な限り、データベース設計の段階で `NOT NULL` 制約を検討し、データとしての「質」を高めておくことが、VBAのパフォーマンス向上にも直結する。

結論

「動けばいい」というコードは、ゴミと同じだ。
大量のデータを扱う業務システムにおいて、エンジニアに求められるのは、オブジェクトの挙動を予測し、計算量を最小化する設計力である。

セルへの逐次書き込みを捨て、メモリバッファを活用した一括転送へとパラダイムシフトせよ。それが、プロフェッショナルな自動化エンジニアへの第一歩である。