Excel VBA 逆引き集 | メモリ管理

Excel VBA
スポンサーリンク

ねらい：VBAのメモリ管理を理解して「速く・安定して・落ちない」コードへ

Excel VBAは、Rangeを1セルずつ触るより「配列でまとめて」扱った方が速い一方で、配列やオブジェクトの寿命・解放を誤ると、メモリを無駄に消費して動作が不安定になります。メモリ管理の勘所は、型と配列の特性を知ること、寿命（スコープ）を適切に設計すること、完了後に的確に後片付けすること、そしてピークメモリを下げるチャンク処理です。初心者でもすぐ使えるコードと、なぜそうするかの理由を丁寧に解説します。

重要ポイントの深掘り

配列は「まとめて速い」が、サイズに比例してメモリを使います。巨大配列を持ち続けず、必要な区間で使ってすぐ解放する設計が基本です。オブジェクト参照は「最後に必ず切る（Set Nothing）」習慣を付けると、イベントや外部ライブラリ絡みの残留参照を避けられます。文字列はイミュータブル（都度新規メモリ）なので、連結を配列＋Joinに置き換えるとピークを抑えられます。

メモリの基本：VBAの型と配列の特性を押さえる

Variantと配列のメモリ特性

VBAでRangeから読み込む表データは、ふつう「二次元のVariant配列」になります。Variantは汎用型で便利ですが、数値・文字列・Emptyなどを動的に持てる分だけオーバーヘッドがあります。大量計算では、可能な部分だけでも数値はDouble、カウンタはLongに明示すると、無駄な型変換が減って速度とメモリの安定性が上がります。

' 数値計算は型を明示してメモリと速度を安定化
Sub SumAmount(ByVal arr As Variant, ByVal amtCol As Long)
    Dim i As Long
    Dim total As Double
    For i = 1 To UBound(arr, 1)
        total = total + CDbl(arr(i, amtCol))
    Next
    Debug.Print total
End Sub
VB

オブジェクト参照の寿命とスコープ

WorksheetやListObject、ADODB.Streamなどは「参照を持っている限り残り続ける」ため、長寿命のモジュール変数で持ちっぱなしにすると、メモリや外部ハンドルが解放されないことがあります。必要な範囲（Sub内）で宣言し、使い終わったら最後に参照を切ると安全です。

Sub UseObjectsSafely()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    ' ... 使う ...
    Set ws = Nothing ' 参照を切る（特にイベントや外部オブジェクトで有効）
End Sub
VB

典型シナリオ別のメモリ最適化

Rangeから配列へ一括読みしたときのサイズ感と扱い方

CurrentRegionやDataBodyRangeから二次元配列に読み込むと、行×列ぶんのVariantがメモリに載ります。配列の「寿命」を短くし、処理が終わったらすぐに Erase でヒープから解放するのが基本です。中間結果の配列も、使い終わりで明確に解放します。

Sub ProcessArraySafely()
    Dim arr As Variant
    arr = Worksheets("Input").Range("A1").CurrentRegion.Value
    
    Dim rows As Long: rows = UBound(arr, 1)
    Dim cols As Long: cols = UBound(arr, 2)
    
    Dim out() As Variant
    ReDim out(1 To rows, 1 To cols + 1)
    
    ' ... 配列処理 ...
    
    Worksheets("Output").Range("A1").Resize(rows, cols + 1).Value = out
    
    Erase out   ' 中間配列の解放
    Erase arr   ' 入力配列の解放（以降は使わない）
End Sub
VB

文字列の大量連結を配列＋Joinへ置き換える

文字列は連結のたびに新しい領域を確保します。大きなループで s = s & ... を繰り返すとピークメモリが膨らみます。いったんString配列に詰めて最後にJoinすると、割り当て回数を減らし、メモリ効率が改善します。

Function BuildCsvLines(ByVal arr As Variant, ByVal col As Long) As String
    Dim i As Long
    Dim buf() As String
    ReDim buf(1 To UBound(arr, 1))
    For i = 1 To UBound(arr, 1)
        buf(i) = CStr(arr(i, col))
    Next
    BuildCsvLines = Join(buf, vbCrLf)
    Erase buf ' バッファ解放
End Function
VB

コレクションやディクショナリのサイズ管理

CollectionやScripting.Dictionaryは便利ですが、巨大データを長時間保持するとメモリを圧迫します。完成したらすぐ出力（シートやファイルへ）し、保持を続けない設計が安全です。不要になったらキー削除や Set dict = Nothing を実行します。

Sub UseDictionaryCarefully()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    ' ... 追加 ...
    ' 出力後は解放
    dict.RemoveAll
    Set dict = Nothing
End Sub
VB

解放と後片付けのテンプレートを徹底する

EraseとSet Nothingの正しい使い分け

Erase は配列のメモリを解放します。固定長配列はゼロ化、動的配列は未割り当て状態に戻ります。Set Nothing はオブジェクト参照を切り、そのオブジェクトへの参照カウントがゼロならリソースが解放されます。配列とオブジェクトで役割が違うので、両方を適切に使い分けます。

Sub CleanupExamples()
    Dim out() As Variant
    ReDim out(1 To 1000, 1 To 10)
    ' ... 使う ...
    Erase out ' 動的配列の解放
    
    Dim st As Object
    Set st = CreateObject("ADODB.Stream")
    ' ... 使う ...
    st.Close
    Set st = Nothing ' オブジェクト参照の解放
End Sub
VB

ストリームや外部オブジェクトのクリーンアップ

ADODB.Stream、FileSystemObject、XMLHTTPなど外部COMオブジェクトは、クローズしてから Set Nothing が基本です。クローズ忘れはハンドル残りにつながり、連続実行で不安定になります。

Sub WriteUtf8Csv(ByVal path As String, ByVal arr As Variant)
    Dim st As Object
    Set st = CreateObject("ADODB.Stream")
    st.Type = 2: st.Charset = "UTF-8": st.Open
    
    Dim r As Long, c As Long
    For r = 1 To UBound(arr, 1)
        Dim line As String: line = ""
        For c = 1 To UBound(arr, 2)
            Dim s As String: s = Replace(CStr(arr(r, c)), """", """""")
            line = line & IIf(c > 1, ",", "") & """" & s & """"
        Next
        st.WriteText line & vbCrLf
    Next
    
    st.SaveToFile path, 2
    st.Close
    Set st = Nothing
End Sub
VB

チャンク処理でピークメモリを下げる設計

大規模配列を分割して処理するテンプレート

100万行級を1配列に乗せるのは無理があります。最終行から範囲を数千行単位に分割し、都度読み・処理・書き戻し・解放を繰り返すと、ピークメモリが安定します。

Sub ProcessInChunks()
    Dim ws As Worksheet: Set ws = Worksheets("Input")
    Dim last As Long: last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim chunkSize As Long: chunkSize = 5000
    Dim startRow As Long: startRow = 2
    
    Do While startRow <= last
        Dim endRow As Long
        endRow = WorksheetFunction.Min(startRow + chunkSize - 1, last)
        
        Dim arr As Variant
        arr = ws.Range(ws.Cells(startRow, "A"), ws.Cells(endRow, "F")).Value
        
        ' ... チャンク内の配列処理 ...
        
        Worksheets("Output").Range(ws.Cells(startRow, "A"), ws.Cells(endRow, "G")).Value = arr ' 例：列増やした場合
        Erase arr ' チャンクの解放
        
        startRow = endRow + 1
    Loop
End Sub
VB

OnTimeを使った疑似非同期でUIの応答を保つ

巨大処理を連続実行せず、チャンクごとに次回を予約すると、UIが固まりにくくなります。各チャンクの後に解放を挟めるため、メモリも安定します。

Dim gStartRow As Long
Dim gLastRow As Long

Sub StartChunkProcess()
    gStartRow = 2
    gLastRow = Worksheets("Input").Cells(Worksheets("Input").Rows.Count, "A").End(xlUp).Row
    Application.OnTime Now, "RunNextChunk"
End Sub

Sub RunNextChunk()
    Dim ws As Worksheet: Set ws = Worksheets("Input")
    Dim chunkSize As Long: chunkSize = 5000
    
    If gStartRow > gLastRow Then
        MsgBox "完了": Exit Sub
    End If
    
    Dim endRow As Long: endRow = WorksheetFunction.Min(gStartRow + chunkSize - 1, gLastRow)
    
    Dim arr As Variant
    arr = ws.Range(ws.Cells(gStartRow, "A"), ws.Cells(endRow, "F")).Value
    
    ' ... チャンク処理 ...
    
    Worksheets("Output").Range(ws.Cells(gStartRow, "A"), ws.Cells(endRow, "F")).Value = arr
    Erase arr
    
    gStartRow = endRow + 1
    Application.OnTime Now + TimeValue("0:00:01"), "RunNextChunk" ' 1秒後に次チャンク
End Sub
VB

メモリリークに見える挙動を避けるテクニック

WithEventsやグローバル参照で参照が残る問題の回避

UserFormやWithEventsのクラスをグローバルに保持したままにすると、参照が切れずイベントが残留することがあります。終了時は Unload Me（フォーム側）と、モジュール側の Set frm = Nothing を忘れずに。クラスのイベント監視も停止用メソッドで参照を外すと安全です。

' フォーム終了時の後片付け（呼び出し側）
Sub ShowInput()
    Dim frm As New FrmInput
    frm.Show vbModal
    Unload frm
    Set frm = Nothing
End Sub
VB

コレクションの長期保持を避け、処理後すぐに解放する

Collectionは参照を保持し続けます。大きなデータをため込む設計にせず、処理後に出力して Set Nothing まで行う習慣を付けると、長期運用でも安定します。

例題で確認：ピークメモリを抑えながら処理する

数万行の配列処理をチャンク分割して安定化する

上のチャンクテンプレートを使って、5万行を1万行×5回に分けます。各チャンク終了時に Erase arr を挟むことで、タスクマネージャ上のメモリが波形になり、ピークが一定に保たれるのを確認できます。UI応答も維持され、キャンセルや他作業への切り替えが可能になります。

文字列生成を配列＋Joinへ置き換えてフリーズを解消する

1セルずつの長い連結から、String配列を作って最後にJoinする形へ変更します。同じ出力を維持しつつ、実行時間とExcelのメモリ使用量が下がり、フリーズが解消することを確認します。

Sub BuildLargeTextSafely()
    Dim arr As Variant
    arr = Worksheets("Input").Range("A1").CurrentRegion.Value
    
    Dim text As String
    text = BuildCsvLines(arr, 1)
    
    Worksheets("Output").Range("A1").Value = text
End Sub
VB

トラブルの兆候から原因を絞る実務ポイント

長処理後に操作不能やクラッシュが起きる場合の観点

終了時の後片付け漏れ（配列の Erase、外部オブジェクトのクローズ、フォームの Unload、参照の Set Nothing）を疑い、テンプレートを導入して一律に後片付けするのが近道です。巨大配列の保持時間が長くないか、コレクションやディクショナリに大量データを溜め続けていないかも確認します。必要な場合はチャンク化に置き換え、ピークメモリを意図的に下げる設計に移すと安定します。

途中から極端に遅くなる場合の観点

文字列連結の増加、DoEventsの呼びすぎ、Variantの暗黙変換の連続などが、時間の経過とともに効いてきます。配列＋Joinへの置換、1〜5%刻みの進捗更新、数値型の明示で改善します。配列やバッファは使い終わりで必ず Erase を入れ、都度新しく確保し直す方針が安全です。

スポンサーリンク
タイトルとURLをコピーしました