Excel VBAの処理が遅いとき、原因は複雑なロジックではなく、シートへのアクセス回数にあることが少なくありません。
特に、ループのたびに Cells や Range を何度も読み書きしているコードは、見た目以上に重くなります。
今回は、数分かかっていた処理が1秒未満まで短縮できた実例をもとに、どこが遅かったのか、何を変えたのか、なぜ速くなったのかを整理して解説します。
先に結論を書くと、最大のボトルネックはセルの逐次読み書きでした。
改善の中心になったのは、元データを配列へ一括で読み込み、処理結果も配列にためて最後に一括で書き戻すことです。
加えて、ScreenUpdating、EnableEvents、Calculation の制御を組み合わせると、ブック構成によってはさらに大きな差が出ます。
今回の改善対象だった処理の概要
対象の処理は、SeatMapシートにある座席情報を読み取り、Recordsシートへ1レコードずつ展開していくものでした。
SeatMapシートでは、行方向と列方向に座席の識別情報があり、その交点にある値を見て、表示用の行列情報や表示フラグを判定しています。
その結果を、Recordsシートへ1件ずつ追記していく構造でした。
つまり、処理の目的そのものは単純です。
座席マップを表形式からレコード形式へ変換するだけです。
今回重要だったのは、処理結果は変えず、入出力のやり方だけを見直したことです。
ロジックの意味はそのままにして、Excelオブジェクトとの往復回数を減らす方向で最適化しました。
修正前コード
まずは修正前のコードです。
遅さの原因が見える本質部分として、以下のコードをそのまま掲載します。
Private Function ProcessOneSeatMap( _
ByVal mapSheetName As String, _
ByVal startRow As Long, _
ByVal wsRec As Worksheet) As Long
Dim wsMap As Worksheet
Set wsMap = ThisWorkbook.Sheets(mapSheetName)
Dim recRow As Long
recRow = startRow
' SeatMapシートのA1セルからseat_idを取得
Dim seatIDVal As Variant
seatIDVal = wsMap.Cells(1, 1).Value
Dim lastRow As Long, tmpRow As Long
tmpRow = 3
Do While Not IsEmpty(wsMap.Cells(tmpRow, 1))
tmpRow = tmpRow + 1
Loop
lastRow = tmpRow - 1
Dim lastCol As Long, tmpCol As Long
tmpCol = 3
Do While Not IsEmpty(wsMap.Cells(1, tmpCol))
tmpCol = tmpCol + 1
Loop
lastCol = tmpCol - 1
Dim i As Long, j As Long
For i = 3 To lastRow
Dim rowVal As Variant
rowVal = wsMap.Cells(i, 1).Value
For j = 3 To lastCol
Dim colVal As Variant
colVal = wsMap.Cells(1, j).Value
Dim seatVal As Variant
seatVal = wsMap.Cells(i, j).Value
Dim showRowVal As Variant
showRowVal = wsMap.Cells(i, 2).Value
Dim showColVal As Variant
showColVal = wsMap.Cells(2, j).Value
Dim showVal As Long
If seatVal = "●" Then
showVal = 1
Else
Dim slashPos As Long
slashPos = InStr(seatVal, "/")
If slashPos > 0 Then
showVal = 1
Dim leftPart As String
Dim rightPart As String
leftPart = Trim(Left(seatVal, slashPos - 1))
rightPart = Trim(Mid(seatVal, slashPos + 1))
If leftPart <> "" Then
showRowVal = leftPart
End If
If rightPart <> "" Then
showColVal = rightPart
End If
Else
showVal = 0
End If
End If
wsRec.Cells(recRow, 1).Value = seatIDVal
wsRec.Cells(recRow, 2).Value = rowVal
wsRec.Cells(recRow, 3).Value = colVal
wsRec.Cells(recRow, 4).Value = showVal
wsRec.Cells(recRow, 5).Value = showRowVal
wsRec.Cells(recRow, 6).Value = showColVal
recRow = recRow + 1
Next j
Next i
ProcessOneSeatMap = recRow
End Functionどこが遅かったのか
このコードが遅くなりやすい最大の理由は、二重ループの中で何度もシートへアクセスしていることです。
VBAの中で変数に代入したり、If で条件分岐したりする処理は、それ自体は比較的軽い部類です。
一方で、Worksheet、Range、Cells を通じてExcel本体のオブジェクトへ値を取りに行く処理や書き戻す処理は、非常にコストが高くなります。
見た目は単なる wsMap.Cells(i, j).Value でも、実際にはVBAからExcelオブジェクトモデルへ問い合わせを行うため、単純なメモリアクセスよりはるかに重くなります。
このコードでは、内側のループ1回ごとに少なくとも以下の読み書きが発生しています。
- SeatMapシートからの読取 4回前後（
colVal、seatVal、showRowVal、showColVal） - Recordsシートへの書込 6回
- 行ごとの読取として
rowValの取得が別途発生
つまり、1レコードあたり概算で10回前後のシートアクセスが発生しています。
例えば、展開後のレコード数が1万件なら、それだけで10万回規模のExcelオブジェクトアクセスになります。
このとき重いのは、文字列判定や InStr、Trim そのものではありません。
処理が重い正体は、計算量そのものよりも、Excelオブジェクトとの往復回数だったということです。
さらに、出力先シートに数式、参照、条件付き書式が設定されている場合は、1セル書き込むたびに再計算や再描画に近い負荷が連鎖することがあります。
そのため、同じ6列分を書き込むとしても、1行ずつ6セル更新するやり方と、最後にまとめて範囲へ書き込むやり方では、体感差が非常に大きくなります。
今回の事例でも、処理ロジックを大きく変えたわけではありません。
速くなった理由は、ロジック改善というより、データの持ち方とExcelとのやり取り方法を変えたためです。
修正後コード
次に、修正後のコードです。
こちらもコード本体はそのまま掲載します。
Private Function ProcessOneSeatMap( _
ByVal mapSheetName As String, _
ByVal startRow As Long, _
ByVal wsRec As Worksheet) As Long
Dim wsMap As Worksheet
Set wsMap = ThisWorkbook.Sheets(mapSheetName)
Dim recCount As Long
Dim seatIDVal As Variant
Dim lastRow As Long
Dim lastCol As Long
Dim tmpRow As Long
Dim tmpCol As Long
seatIDVal = wsMap.Cells(1, 1).Value2
tmpRow = 3
Do While Not IsEmpty(wsMap.Cells(tmpRow, 1).Value)
tmpRow = tmpRow + 1
Loop
lastRow = tmpRow - 1
tmpCol = 3
Do While Not IsEmpty(wsMap.Cells(1, tmpCol).Value)
tmpCol = tmpCol + 1
Loop
lastCol = tmpCol - 1
If lastRow < 3 Or lastCol < 3 Then
ProcessOneSeatMap = startRow
Exit Function
End If
Dim mapData As Variant
mapData = wsMap.Range(wsMap.Cells(1, 1), wsMap.Cells(lastRow, lastCol)).Value2
Dim outRowCount As Long
outRowCount = (lastRow - 2) * (lastCol - 2)
Dim outData() As Variant
ReDim outData(1 To outRowCount, 1 To 6)
Dim i As Long, j As Long
Dim outIdx As Long
Dim rowVal As Variant
Dim colVal As Variant
Dim seatVal As Variant
Dim showRowVal As Variant
Dim showColVal As Variant
Dim showVal As Long
Dim slashPos As Long
Dim leftPart As String
Dim rightPart As String
outIdx = 1
For i = 3 To lastRow
rowVal = mapData(i, 1)
For j = 3 To lastCol
colVal = mapData(1, j)
seatVal = mapData(i, j)
showRowVal = mapData(i, 2)
showColVal = mapData(2, j)
If seatVal = "●" Then
showVal = 1
Else
slashPos = InStr(1, seatVal, "/", vbBinaryCompare)
If slashPos > 0 Then
showVal = 1
leftPart = Trim$(Left$(CStr(seatVal), slashPos - 1))
rightPart = Trim$(Mid$(CStr(seatVal), slashPos + 1))
If leftPart <> "" Then
showRowVal = leftPart
End If
If rightPart <> "" Then
showColVal = rightPart
End If
Else
showVal = 0
End If
End If
outData(outIdx, 1) = seatIDVal
outData(outIdx, 2) = rowVal
outData(outIdx, 3) = colVal
outData(outIdx, 4) = showVal
outData(outIdx, 5) = showRowVal
outData(outIdx, 6) = showColVal
outIdx = outIdx + 1
Next j
Next i
wsRec.Cells(startRow, 1).Resize(outRowCount, 6).Value = outData
ProcessOneSeatMap = startRow + outRowCount
End Function改善点1：SeatMapシートを配列へ一括読込している
修正後コードでは、まず必要な範囲を mapData に一括で読み込んでいます。
mapData = wsMap.Range(...).Value2 とすることで、以後のループ内では mapData(i, j) のように配列アクセスだけで値を参照できます。
これはExcelオブジェクトへの問い合わせではなく、VBA内部のメモリ上の配列を読むだけなので、Cells(i, j).Value を繰り返すより大幅に軽くなります。
改善点2：出力用の配列を用意して最後に一括書込している
修正前は、内側のループ1回ごとにRecordsシートへ6セルずつ書き込んでいました。
修正後は、outData 配列へ結果をため、最後に Resize(...).Value = outData でまとめて書き込んでいます。
これにより、出力時のExcelオブジェクトアクセスは実質1回に近い形まで圧縮されます。
今回の改善で最も効いたのは、この逐次書き込みの廃止と一括書き込みへの変更です。
改善点3：ValueではなくValue2を使っている
Value2 は、日付や通貨の自動変換を伴う Value よりも素直な値の受け渡しになりやすく、若干有利になる場合があります。
ただし、今回の劇的改善の主因はここではありません。
主役はあくまで、配列への一括読込と一括書込です。
修正前後の比較
| 比較項目 | 修正前 | 修正後 |
|---|---|---|
| SeatMapシートの読み取り方法 | ループ内で Cells を都度参照 | 必要範囲を配列へ一括読込 |
| Recordsシートへの書き込み方法 | 1レコードごとに6セルずつ書込 | 配列へ蓄積して最後に一括書込 |
| Excelとのやり取り回数 | レコード数に比例して非常に多い | 読込1回＋書込1回に大幅圧縮 |
| 主なボトルネック | 二重ループ内のセルI/O | VBA側の軽い配列処理が中心 |
| 処理速度の傾向 | データ量増加に弱く、数分かかりやすい | 同条件でも大幅短縮しやすい |
| 条件付き書式や数式の影響 | 逐次書込のたびに負荷が出やすい | 再計算回数を抑えやすい |
今回の高速化で特に効いたポイント
今回の改善で効き目が大きかった順に整理すると、次のようになります。
- 出力先シートへの逐次書き込みをやめて一括書き込みにした
- 元データシートを逐次参照せず配列に一括読込した
- Calculation を Manual にした
- ScreenUpdating を False にした
- EnableEvents を False にした
- Value より Value2 を使った
この中でも、1位と2位が支配的です。
つまり、配列化と一括書き込みが本体で、画面更新停止やイベント停止は補助的な最適化と考えると理解しやすくなります。
特に、出力先に参照数式や条件付き書式が多いブックでは、Calculation = xlCalculationManual の効果が非常に大きくなることがあります。
逆に、単純な表に書き込むだけのケースでは、ScreenUpdating や EnableEvents の効果は補助的に留まることもあります。
Application設定を安全に切り替える基本形
画面更新、イベント、再計算を止める場合は、必ず復旧処理までセットで書く必要があります。
Dim oldCalc As XlCalculation
On Error GoTo SafeExit
With Application
.ScreenUpdating = False
.EnableEvents = False
oldCalc = .Calculation
.Calculation = xlCalculationManual
End With
' ここに重い処理
SafeExit:
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = oldCalc
End Withこれを忘れると、エラー時にExcel全体の挙動が変わったまま残るため注意が必要です。
今回の事例以外でも使えるExcel VBA高速化テクニック
Select / Activate を使わない
Select や Activate は、見た目には分かりやすいものの、画面操作前提のコードになりやすく、処理速度と安定性の両面で不利です。
対象オブジェクトを直接指定して、Worksheets("Sheet1").Range("A1").Value = 1 のように書いたほうが速く、誤動作も減ります。
With を使ってオブジェクト参照をまとめる
同じシートやレンジを何度も参照する場合、With を使うとコードが短くなり、参照の重複も減らせます。
With wsRec
.Cells(1, 1).Value = "A"
.Cells(1, 2).Value = "B"
.Cells(1, 3).Value = "C"
End With劇的な改善要因になるとは限りませんが、保守性と速度の両面で有効な基本です。
Cells / Range 参照の重複を減らす
同じ値をループ中で何度も取りに行っている場合は、一度変数へ受けて再利用するだけでも差が出ます。
ただし、今回のように根本的に回数が多いケースでは、変数キャッシュだけでは不十分です。
その場合は、配列化まで踏み込む必要があります。
ループ中にシートへ書き込まない
実務上、最も優先して見直したいポイントの一つです。
1行ずつ書く、1セルずつ書く、判定のたびに書く、という構造は重くなりやすいため、可能な限りメモリ上で完結させて最後にまとめて出力します。
配列でまとめて処理する
読込も配列、処理も配列、書込も配列という形は、VBA高速化の定番です。
行列データをそのまま持てるため、シート構造との相性もよく、ループ処理が多い業務マクロでは特に効果が出やすくなります。
Dictionary や Collection で検索回数を減らす
別表との照合や重複判定を毎回ループで探していると、計算量が増えて遅くなります。
検索キーが明確なら、Dictionary を使って先に索引を作っておくほうが効率的です。
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict("A001") = 120
dict("A002") = 250
If dict.Exists("A001") Then
Debug.Print dict("A001")
End If特に、ループの中でさらに別ループを回して検索しているコードは、見直し候補です。
UsedRange や最終行取得の考え方を見直す
最終行の取得方法が不適切だと、不要な空行や過去の書式設定まで含めて処理してしまうことがあります。
UsedRange は便利ですが、期待通りでないこともあるため、データの特性に応じて Cells(Rows.Count, 1).End(xlUp).Row などと使い分けるのが実務的です。
計算式を多く触る処理ではCalculation制御を検討する
数式セルが多いシートに対して大量の書込を行う場合、自動計算が毎回走るだけで大きな時間を消費します。
このときは、処理前に xlCalculationManual に切り替え、終了後に元へ戻すだけで改善することがあります。
ただし、再計算漏れや戻し忘れが業務影響につながるため、復旧処理は必須です。
条件付き書式や揮発性関数が重くなるケースを疑う
シート側に複雑な条件付き書式や、OFFSET、INDIRECT などの揮発性関数が多いと、単純な書込でも予想以上に遅く感じることがあります。
VBAだけを見るのではなく、書き込み先シートの構造まで含めて調べることが重要です。
DoEvents の多用を避ける
DoEvents はUI応答性のために必要な場面もありますが、ループのたびに呼ぶと速度低下の原因になります。
長時間処理でどうしても必要なら、一定件数ごとに限定して呼ぶほうが安全です。
Variantの乱用と型指定の考え方
配列でシートから値を受ける場合、Variant は必要になることが多いです。
一方で、カウンタやフラグまで何でも Variant にすると、無駄な型変換や意図しない挙動の温床になります。
Long、String、Boolean で明確に持てるものは型指定したほうが、速度と可読性の両方で有利です。
文字列連結を大量に行う場合の注意
VBAで大量の文字列を & で連結し続ける処理は、データ量によっては重くなることがあります。
件数が多い場合は、配列へためて最後に Join する構成のほうが有利になることがあります。
ただし、通常の表処理ではセルI/Oのほうが支配的なことが多いため、まずはシートアクセス削減を優先して確認するのが現実的です。
実務で注意したいこと
高速化では、処理結果を変えないことが最優先です。
今回の改善でも重要だったのは、判定ロジック自体はほぼ変えず、入出力の方法だけを変えたことでした。
高速化のためにロジックまで同時に書き換えると、速くなった理由と不具合の原因が切り分けにくくなります。
また、配列化するとコードの見通しが少し変わるため、保守性とのバランスも必要です。
短いコードに見えても、添字の意味が不明確だと後で修正しにくくなるため、変数名やコメントで意図を補うほうが安全です。
さらに、Application の設定を変える処理では、エラー時も確実に元へ戻す設計にしておく必要があります。
速度だけを追うのではなく、可読性、保守性、安全性も含めて最適化を考えることが、実務では特に重要です。
まとめ
Excel VBA高速化の本質は、Excelとのやり取りを減らすことにあります。
今回のように、処理ロジックそのものは大きく変えず、データの持ち方と入出力方法を見直すだけで、数分かかっていた処理が1秒未満になることもあります。
特に見直す価値が高いのは、一括読込、一括処理、一括書込です。
画面更新停止やイベント停止、自動計算停止は有効ですが、主役ではなく補助的な改善と捉えると整理しやすくなります。
遅いVBAに悩んだら、まずループ内の Cells / Range を疑うことが、実務で最も再現性の高い第一歩です。
コメント