【VBA】置換処理が遅い、Replace関数とReplaceメソッドの処理時間の違い

こんにちは、わたあめです。最近Excelの文字列の置換処理をするマクロを書きました。実際に処理をはしらせてみると…

悩める私

VBA置換処理が遅い～！！

ということを感じました。どうしたら置換処理を速く処理できるか？ということと、文字列の置換を行うには「Replace関数」と「Replaceメソッド」とどちらを使えばいいのか？という事を考えてみたので記事にします。

あくまで個人調べです。環境条件によって処理時間は異なりますのでご了承ください。

それでは、いってみましょう！

検索・置換範囲を絞る

当たり前のことですが、検索して置換する範囲は絞れば絞るほど処理が早かったです。"AAA"を"BBB"へ置換するVBAスクリプトを例にTimer関数を使って処理速度を測ってみます。

シート全体を検索する以下のスクリプトは処理時間が0.35秒でした。

' 全体検索
Cells.Replace What:="AAA", Replacement:="BBB", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

次のスクリプトは、Cellsの部分をRangeで「A:A」とA列を範囲指定しています。A列のみであれば処理時間は0.19秒でした。範囲が決まっている場合は、「A1:C100」などと指定すればOKです。

' 列検索
Range("A:A").Replace What:="AAA", Replacement:="BBB", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

範囲を絞るだけでも結構処理時間は変わるので、範囲が分かっている場合は狭めると良いかなと思います。

ReplaceメソッドではなくReplace関数を使う

VBAマクロでは置換処理は「Replaceメソッド」か「Replace関数」を使います。Replaceメソッドは前章で時間を測り、シート全体でも0.35秒かかりましたね。

Replace関数で10000行処理してみることとします。処理時間は0.24秒でした。

For i = 1 To 10000
  If Instr(Cells(i, 1),"AAA") then
    Cells(i, 1) = Replace(Cells(i, 1), "AAA", "BBB")
  End if
Next i

ReplaceメソッドはA列全体が0.19秒だったので、この結果を見るとReplaceメソッドの方が早く処理ができるのかな？という印象です。

調べものしてる人

ただ、Replaceメソッドには使用するにあたっての注意点があるみたい！

Replaceメソッドの使用上の注意点

Replaceメソッドは、使用上の注意点がありました。

オートフィルタで非表示になっているセルは置換されない
置換後、1文字目の文字の書式設定（フォントや文字サイズ、文字色等）が2文字目以降にも反映される※
セルの見た目上は文字列一致していても関数式の中で文字が離れていれば置換対象として検知されない

※1文字目の書式設定が置換後に反映されるというのは、1文字目が赤文字でその後黒文字でも、Replaceメソッド実行後は全ての文字が赤文字になってしまうということが起こります。

上記に該当してしまう場合は、Replace関数を使うのが良いかもしれないです。確実な処理を取るならば、時間がかかってもReplace関数を使うのが安心なのかなと思ったりです。

さいごに

いかがだったでしょうか？個人的な感想ですが、確実に処理をしたいのならばReplace関数で、でもやっぱり処理速度が速いのはReplaceメソッドなのかな～という所です。なにかいい方法ないかな…。それでは、また次の記事で！