セル内テキストを置換する場合、大きく分けて以下二つの方法があります。
- RangeオブジェクトのReplaceメソッドを使用する方法
- Replace関数を使用する方法
このどちらが高速なのかを検証します。
お忙しい方向けに先に結論を述べますと、
- ReplaceメソッドよりもReplace関数の方が高速
- ただし「セルへのアクセス」による速度低下の方が影響が大きいため、
Replace関数を直接セルへ出力するループ文はかなり遅い - Replace関数の結果を一度配列に入れて、最後にセルに出力するのが最速
- 置換対象が少ない場合はReplaceメソッドが高速化する
という結果になりました。
検証内容と詳細を見たい方はこのまま読み進めてください。
ソースコード
時間計測コード
このコードの中に検証コードを記載して実行します。
Sub Replaceメソッドを50000行に実行() Dim 開始時刻 As Double, 終了時刻 As Double 開始時刻 = Timer ' この部分に各ソースコードを入れて実行 終了時刻 = Timer Debug.Print "Replaceメソッドの実行時間：" & (終了時刻 - 開始時刻) & "秒" End Sub
検証用データ
A～Jまでをランダムに10個並べた文字列を50,000行用意し、
"A"を"X"に置換する時間を計測します。
Range.Replaceメソッド
Range("A1:A50000").Replace "A", "X"
Replace関数
Dim R As Long For R = 1 To 50000 Cells(R, 1) = Replace(Cells(R, 1), "A", "X") Next
Replace関数を一旦配列へ出力してからセルに一括出力
Dim Arr(1 To 50000, 1 To 1) Dim R As Long For R = 1 To 50000 If InStr(Cells(R, 1), "A") > 0 Then Arr(R, 1) = Replace(Cells(R, 1), "A", "X") End If Next Range("A1:A50000").Value = Arr
計測結果
| 処理内容 | 処理時間 |
|---|---|
| Range.Replaceメソッド | 0.9531250秒 |
| Replace関数 | 4.1640625秒 |
| Replace関数+配列 | 0.3671875秒 |
考察
計測結果の通り、Replace関数+配列が最速となりました。
ReplaceメソッドよりもReplace関数の方が高速なのですが、
それ以上に「セルへのアクセス回数」による影響が大きいため、
配列を使用せず1セルずつ出力するとReplace関数の方が遅くなりますね。
Replaceメソッドでも50,000件やって1秒かかっていませんので、
よほど大量のデータでなければそこまで速度は気にする必要はなさそうです。
Replaceメソッドには「ユーザーが実行した置換ダイアログの影響を受ける」という弱点がありますが、上記の通りコードの短さは素晴らしい点がありますので、
- サッと書きたいときはRangeオブジェクトのReplaceメソッド
- しっかししたマクロを組みたいときは配列を活用してReplace関数
と使い分ければいいと思います。
なお余談になりますが、"A"を"X"に置換している部分を、
"AB"を"XX"、"ABC"を"XXX"と置換文字数を増やしていくと、
Range.Replaceメソッドだけが目に見えて高速化します。
（Replace関数はほとんど同じ実行時間）
| 処理内容 | 処理時間 |
|---|---|
| "A"⇒"X" | 0.953125秒 |
| "AB"⇒"XX" | 0.203125秒 |
| "ABC"⇒"XXX" | 0.078125秒 |
Replaceメソッドは「置換が実行されたときだけセルにアクセスしている」ようで、
滅多に置換対象が出現しない処理の場合は高速のようです。
50,000件中100件ほどしか対象がない置換というのも珍しい気がしますので、
あまり活用できる豆知識ではないですが覚えておいてください。
