Excel VBAで一つのセルを入力するたびに待たされる場合、書き込む行数より、書き込みをきっかけに起きる再計算が時間を使っていることがあります。表示シートの変更をすぐDBシートへ転記する構成では、入力の回数だけ保存処理が走ります。
この場合は、入力時に「何を変更したか」を記録し、保存や表示切替の前にまとめて反映する設計が使えます。入力処理と書き込み処理を分けることで、操作中の待ち時間を減らします。
まず書き込みと再計算を分けて測る
計測対象を、入力イベント、DBの読み取り、書き込み、再計算に分けます。調査したブックでは、入力ごとの全体書き込みに約0.85秒、変更の記録だけなら約55ms、一件追加の一括反映は約195msでした。特定ブックでの実測であり、同じ改善率を保証する値ではありません。
揮発性関数や広い参照範囲も調べます。MicrosoftのExcel性能ガイドには、再計算とVBAからの読み書きを減らす考え方がまとめられています。
行番号ではなく業務キーで保留する
Scripting.Dictionaryへ、レコードIDと項目IDの組をキーにして入力時点の値を記録します。同じ項目を何度変更しても、最後の値が残る形にします。
Private Pending As Object
Private Sub RecordChange(ByVal RecordId As String, _
ByVal FieldId As String, ByVal NewValue As Variant)
If Pending Is Nothing Then Set Pending = CreateObject("Scripting.Dictionary")
Dim Key As String
Key = CStr(Len(RecordId)) & ":" & RecordId & FieldId
Pending.Item(Key) = Array(RecordId, FieldId, NewValue)
End Sub
キーの区切り文字がIDに含まれても衝突しないよう、この例では先頭IDの長さを付けています。変更したセルのRangeそのものを保存すると、あとで位置が変わるため、業務キーと値のスナップショットにします。複数セルの貼り付けも、一つずつ変更として記録します。
行番号や「何件目」を識別に使うブックでは、その番号が変わる前に保留分を反映するか、不変の行IDを導入します。
反映するタイミングを決める
| 操作 | 反映する位置 |
|---|---|
| 年月や分類の切替 | 表示を作り直す前 |
| 別シートへ移動 | Worksheet_Deactivateなど |
| 保存 | Workbook_BeforeSave |
| 終了 | Workbook_BeforeClose |
| 行の追加・削除 | キーや並びを変更する前 |
イベント停止中に保存するマクロはBeforeSaveを通りません。明示的に反映処理を呼びます。保存・終了時の反映に失敗したらCancelで処理を止め、未反映の記録を残して利用者へ知らせます。シート移動は失敗後に移動を止められない場合があるため、移動先から保存しても再試行できる状態にします。
対象行を確定してから配列へ適用する
反映前のDBを読み、保留されたキーがどの行かを先にすべて解決します。続いて更新・追加・削除を配列上で組み立て、最後に書き込みます。削除で行番号がずれた後に、別の更新先を探すことを避けます。
変更のない項目は書き戻しません。表示中に別の処理がDBを直す可能性があれば、入力開始時の値と現在のDB値を照合し、競合を検出します。古い表示全体を保存すると、別の変更まで上書きしてしまいます。
Excelのシート書き込みは、一般的なDBトランザクションと同じ保証にはなりません。全件検証を先に行い、途中失敗時の復旧方法を決め、書き込みに成功した分だけ保留から外します。
差分出力は表の専有範囲へ限定する
出力先と配列が同じなら書かず、少数行なら変更部分、多数行ならまとめて書く方法も併用できます。ただしUsedRangeの最終行まで無条件に消すと、別の表を壊すおそれがあります。出力範囲と、前回の表の行数を明示します。
値専用のDBシートと、数式を含むシートは区別します。値で上書きする差分出力を数式列へ使うと数式が消えます。比較も、数値・文字列・日付・空欄・エラー値をどう扱うかを定義します。文字列化して同じなら等価というルールが、すべての表で正しいわけではありません。
未反映のデータを失わない運用にする
モジュール変数は、VBAのリセットや異常終了で消えます。画面に値が残ることと、DBへ反映済みであることは別です。未反映件数を表示し、反映失敗時の再試行を用意します。異常終了にも耐える必要がある場合は、保留変更を別の作業領域へ永続化する設計が必要です。
イベントや計算モードは変更前の値を保存し、エラー時にも戻します。テストでは連続入力、貼り付け、年月切替、保存失敗、行削除、直接DBを修正した場合を確認します。