【Excel VBA】文字列連結の闇：なぜ `&` 演算子の多用はメモリを崩壊させるのか？ Join関数と配列による極限のパフォーマンス最適化
業務自動化の現場で、数千行、数万行に及ぶデータを処理するVBAマクロを書いていて、こんな経験はないだろうか？
- 「最初はサクサク動いていたのに、データ量が増えた途端に処理が急激に重くなった」
- 「タスクマネージャーを見ると、Excelがメモリを異常に喰い潰している」
- 「最終行付近の処理で、突如として『メモリ不足 (Error 7)』でクラッシュした」
もし心当たりがあるなら、あなたの書いたコードは「文字列の継ぎ接ぎ地獄」に陥っている可能性が高い。
素人が書くVBAと、プロが設計するVBAの決定的な違い。それは「メモリのライフサイクルとオブジェクトの挙動を支配しているか否か」に尽きる。
今回は、大量の文字列を扱う処理において、なぜ `&` 演算子がタブー視されるのか、そしてプロはどうやって `Join` 関数と配列を駆使してパフォーマンスを極限まで引き上げるのかを、アーキテクトの視点からロジカルに解説しよう。
—
1. なぜ `&` 演算子でのループ結合は「悪」なのか？
まずは、多くの開発者がやりがちな「最悪のアンチパターン」を見てほしい。
‘ 【アンチパターン】絶対に行ってはいけない文字列の逐次結合
Dim result As String
Dim i As Long
result = “”
For i = 1 to 50000
‘ ループのたびに & で結合している
result = result & “行データ:” & i & vbCrLf
Next i
一見、何の問題もなさそうなシンプルなコードだ。しかし、VBAの内部エンジン（COMのBSTR管理）の挙動を知る者からすれば、これは「暴力的なメモリの無駄遣い」である。
BSTR（Basic String）の残酷な真実
VBAが扱う文字列（BSTR）は、メモリ上で連続した領域に格納される。そして、文字列の長官が変動するとき、内部で何が起きているか？
1. `result = result & “追加文字列”` が実行されると、OSは 「現在の `result` の長さ」＋「追加する文字列の長さ」 の新しいメモリ領域を毎回新しく確保する。
2. 古いメモリ領域から、新しい領域へ既存の文字列をすべてコピーする。
3. 最後に、古いメモリ領域を破棄する。
これを5万回繰り返すということは、5万回にわたる「メモリの確保・データコピー・解放」のドラマが裏で繰り広げられていることになる。$O(N^2)$ に近い計算量となり、データが増えれば増えるほど、処理速度は指数関数的に劣化していく。これが「後半急激に重くなる」メカニズムの正体だ。
—
2. 救世主：`Join` 関数と「配列バッファ」のメカニズム
この問題をスマートに解決するのが、「配列に一度データを蓄え、最後に `Join` 関数で一気に結合する」 という設計手法だ。
‘ 【推奨パターン】配列 ＋ Join関数による高速化
Dim buf() As String
Dim i As Long
‘ あらかじめ必要なサイズ分の配列を確保（動的配列のRedim）
ReDim buf(1 To 50000)
For i = 1 to 50000
‘ 配列に要素を格納するだけ（メモリの再割り当ては発生しない）
buf(i) = “行データ:” & i & vbCrLf
Next i
‘ 最後に一括して結合
Dim result As String
result = Join(buf, “”)
なぜこれが圧倒的に速いのか？
`ReDim` であらかじめ必要なメモリ領域をドンと一括確保するため、ループ内ではメモリの再割り当てやコピーが一切発生しない。
そして最後の `Join` 関数は、C/C++レベルの高度に最適化されたアルゴリズムで、一瞬にしてメモリ上で文字列を連結する。
体感速度にして、数万行規模であれば数十倍〜数百倍の差となって現れる。実務でCSV出力やSQL文の動的生成を行う際、この差はプロジェクトの成否を分ける。
—
3. 実践！プロダクションコードによるパフォーマンス比較
百聞は一見にしかず。実務でそのまま使える、ベンチマーク測定用の堅牢なプロシージャを用意した。
VBAの `Timer` 関数を使い、悪名高い `&` 結合と、モダンな `Join` 結合のパフォーマンスを計測・比較してみよう。
Option Explicit
‘ =====================================================================
‘ 模块名: MdlStringBenchmark
‘ 概要 : 文字列結合のパフォーマンス比較と実務的検証
‘ =====================================================================
Public Sub RunStringBenchmark()
Const LOOP_COUNT As Long = 30000
Dim startTime As Double
Dim i As Long
Dim result As String
‘ — 1. & 演算子による逐次結合（アンチパターン） —
startTime = Timer
result = “”
For i = 1 To LOOP_COUNT
result = result & “ID_” & i & “,Name_” & i & vbCrLf
Next i
Debug.Print “& 演算子の処理時間: ” & Format(Timer – startTime, “0.000秒”)
‘ — 2. 配列 ＋ Join関数による結合（推奨パターン） —
startTime = Timer
Dim buf() As String
ReDim buf(1 To LOOP_COUNT)
For i = 1 To LOOP_COUNT
buf(i) = “ID_” & i & “,Name_” & i & vbCrLf
Next i
result = Join(buf, “”)
Debug.Print “Join関数の処理時間: ” & Format(Timer – startTime, “0.000秒”)
End Sub
実行結果の目安（※環境により変動）
- `&` 演算子ループ: 数秒〜十数秒（データ数によっては固まる）
- `Join` 関数: 0.05秒以下
この圧倒的な差を見せつけられれば、今後くだらない `&` の連打コードを書く気は起きなくなるはずだ。
—
4. 実務で「さらに堅牢に」使いこなすためのアーキテクチャ
実際の業務では、結合するデータの総数が事前に分からないケースが多い（例：フィルター結果の行数、可変長のDBレコードなど）。
そんなときは、固定長の `ReDim` は使えない。しかし、ここで `ReDim Preserve` を毎回呼んでは、結局元の木阿弥（メモリの再割り当て地獄）になってしまう。
プロの開発者は、「チャンク（塊）管理」または「コレクション／Dictionaryの活用」、あるいは「適切な初期サイズ見積もり」によってこれをいなす。
実務向け：動的データに対応するバッファクラス（構造体アプローチ）
事前にサイズが不明な場合、一定の大きさに達するたびに配列を倍々に拡張するか、あるいは標準的なコレクションを経由して最後に配列化するアプローチが堅牢だ。
以下は、実務のファイル出力やSQL生成でそのまま流用できる「安全かつ高速な文字列ビルダー」の基本パターンである。
‘ ———————————————————————
‘ 概要: 行数が不明なデータから効率的に文字列を生成する実務パターン
‘ ———————————————————————
Public Sub GenerateReportFromRange()
Dim ws As Worksheet
Set ws = ActiveSheet
‘ 最終行を取得
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
If lastRow < 2 Then
MsgBox "処理対象データが存在しません。", vbExclamation
Exit Sub
End If
' データを一括してバリアント配列に読み込む（シートへのアクセスを最小限に！）
Dim rawData As Variant
rawData = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, 5)).Value
' 出力用の配列を確保
Dim buf() As String
ReDim buf(1 To lastRow)
Dim i As Long, count As Long
count = 0
For i = 2 To lastRow
' 値の加工と結合をメモリ上で完結させる
If rawData(i, 1) <> “” Then
count = count + 1
buf(count) = rawData(i, 1) & vbTab & rawData(i, 2) & vbTab & rawData(i, 3) & vbCrLf
End If
Next i
‘ 実際に有効だった件数だけに再リサイズ
If count > 0 Then
ReDim Preserve buf(1 To count)
Dim finalOutput As String
finalOutput = Join(buf, “”)
‘ ここでファイル出力やAPI送信などの次のアクションへ渡す
Debug.Print “生成された文字列の長さ: ” & Len(finalOutput) & ” 文字”
Else
Debug.Print “有効なデータはありませんでした。”
End If
End Sub
このコードには、VBAプログラミングの極意が詰まっている。
1. シートへのセルアクセスを極限まで減らしている (`Range.Value` で一括配列化)。
2. ループ内で `&` を使っているが、これは単一の行内要素の結合であり（数個程度）、メモリ再割り当てのコストは無視できるレベル。
3. 行ごとの結合には `Join` 関数をベースとした配列バッファを活用。
—
5. チーフアーキテクトからの総括：保守性とパフォーマンスの両立
「たかが文字列の結合」と侮るなかれ。
現場のツールが「使い物にならないほど遅い」「すぐにエラーで落ちる」というクレームの8割は、こうした基礎的なメモリ管理の無知に起因している。
- 少数の結合（2〜3個）: 可読性を優先して `&` 演算子で全く問題ない。
- ループ内での結合、数千行を超えるデータ処理: 迷わず `Join` 関数と配列バッファを導入せよ。
コードの美しさは、そのまま実行速度と堅牢さに直結する。
あなたの書くマクロを、ただの「動くスクリプト」から、プロフェッショナルな「エンタープライズ・ツール」へと昇華させてほしい。
