YiaoWang
{@last-update}

Excel VBA:高速化 >ループ内:書式の設定をまとめてする

概要

詳細

対応例

Public Sub Test1(ByVal MAX_COUNT As Long) Dim y As Long For y = 1 To MAX_COUNT Cells(y, 1) = y ' セルの書式を"###,0"にする Cells(y, 1).NumberFormatLocal = "#,##0" Next End Sub Public Sub Test2(ByVal MAX_COUNT As Long) Dim y As Long For y = 1 To MAX_COUNT Cells(y, 1) = y Next ' セルの書式を"###,0"にする Range("A1:A" & MAX_COUNT).NumberFormatLocal = "#,##0" End Sub Public Sub Test3(ByVal MAX_COUNT As Long) Dim y As Long For y = 1 To MAX_COUNT Cells(y, 1) = y Next ' 1行目のセルの書式を"###,0"にして残りの行に書式をコピーする Cells(1, 1).NumberFormatLocal = "#,##0" Cells(1, 1).Copy Range("A1:A" & MAX_COUNT).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub

説明

処理がなければ速度は最速です。セルの書式や罫線などを事前に設定可能なら VBA での実装は不要です。 但し、この場合は、ユーザが設定を変更してしまう可能性を考慮する必要があります。 業務システムでは、シートの保護等でユーザが変更できないことを保証しない限りは、事前の設定は難しいと思います。

一括設定できるものをループの外で実装することは、処理速度だけでなく、処理の分離になり、可読性、保守性も上がります。

尚、今回は1列に対しての書式設定の場合は、改修後1(ループ後に一括設定) > 改修後2(処理の書式をコピー適用) の結果となりました。 複数列に対して、列ごとに異なる書式を設定する必要がある場合、両者の設定方法で速度比に変化があるかどうかは試してみる価値があると思います。

参考

< <
MAX_COUNT 改修前 (a) 改修後1 (b) 改修後2 (c) 速度比:(b)/(a) 速度比:(c)/(a)
1,000 1.4555秒 0.0461秒 0.1258秒 3.2% 8.6%
10,000 11.768秒 0.3938秒 0.5828秒 3.4%5.0%
100,000 117.0531秒 4.6617秒 6.0312秒 4.0%5.2%
  • 処理時間:10回実施した結果の平均値。画面の再描画を止めて計測。(参考:『画面の再描画/自動計算を止める』
  • 実施環境:Excel 2010 / Intel(R) Core(TM) i5 CPU M450 2.40GHz / Windows 7 Home SP1 64bit。
inserted by FC2 system