Excel VBA Replace関数で文字列を高速で置換する方法と速度検証

VBAには文字列を置換するReplaceメソッドとReplace関数があります。

今回はReplace関数を使って高速に置換する方法をご説明します。

この置換という作業はデータクレンジングを行う際に必須と言っても良いくらい使用します。

この置換作業が遅い、重いと感じる方はコードを見直すことで高速化する事が出来るかもしれません。

とは言っても、実はReplace関数自体の速度を上げるのではなく、ロジックの見直しをするだけです。

結論から言うと配列を使う方法と、セルを直接参照する場合は「InStr関数」を使用して参照回数を減らす方法です。

Replaceメソッド、関数、その他変換系の関数については下記記事をご覧下さい。

1.Replace関数の使い方
2.基本的な置換のコード
3.高速に置換するコード
- Insrt関数を使用した処理
- 配列を使用した高速化

1.Replace関数の使い方

簡単にですが、Replace関数の使い方についてご説明します。

Replace関数は標準で用意されている関数です。

特定の文字列の中から指定の文字列に置換します。

文字列の一部を置換したい場合などに使用します。

Replace関数の構文は次のように記述します。

Replace(expression,find,replace[,start[,count[,compare]]])

これではわかりにくいので、簡単に書くと次のようになります。

「Replace(文字列,検索文字列,置換文字列[,開始位置[,置換回数],比較方法]]])」

引数一覧

引数名	数値	説明
Eexpression	–	置換したい文字列を含む文字列指定します。
Find	–	検索する文字列指定します。
Replace	–	置換する文字列指定します。
Start	1	検索開始位置を指定します。
Count	-1	置換する文字列数を指定します。(省略の場合すべて置換します。)
Compare	0	大文字と小文字を区別するか指定します。

Compareの定数

定数	説明
vbBinaryCompare	バイナリモードで大文字と小文字を区別します。
vbTextCompare	テキストモードで大文字と小文字を区別しません。

Replace関数とReplaseメソッドの違い

・Replaceメソッド：指定範囲のセルの文字列の一部を置換します。
・Replace関数：変数やセル値の指定文字列の一部を置換します。

2.基本的な置換のコード

処理速度など一切考慮していない、置換するための基本的なコードです。

下記図のようにA列に「A」から始まる番号と「B」から始まる番号がランダムに10万個あります。

直接セルを参照してこのAをBに置換します。

Sub Sample1()

Dim i As Long

For i = 1 To 100000

    Cells(i, 1) = Replace(Cells(i, 1), "A", "B")

Next i

End Sub

約14秒かかりました。

データクレンジングではこのような置換作業が頻繁に行われるので、1つの処理で14秒は致命的です。

3.高速に置換するコード

次は高速に置換するコードです。

ロジック次第でいろいろな方法があると思いますが、今回は「Insrt関数」を使って置換する回数を減らす方法と、配列を使用する方法で速度検証します。

画面の更新を停止したら早くなるのでは？と思いますが、今回は画面の更新を停止しても速度改善につながりませんでした。

Insrt関数を使用した処理

この方法はもし置換するデータがすべて該当しない場合、Instr関数で置換する内容が含まれているか判定して、少しでも置換処理とセルへの出力を減らします。

配列が苦手という方は、こちらの方法で多少処理速度が改善できます。

Sub Sample2()

Dim i As Long

For i = 1 To 100000

    If InStr(Cells(i, 1), "A") <> 0 Then Cells(i, 1) = Replace(Cells(i, 1), "A", "B")

Next i

End Sub

約7.5秒でした。

ものすごい改善とまではいきませんが、全体のデータ量に対し置換するデータが少ない場合は有効です。

「If InStr(Cells(i, 1), “A”) <> 0 Then」でAが含まれている場合のみ、置換を行っています。

配列を使用した高速化

次は配列を使用した方法です。

置換だけに限った内容ではないですが、配列に格納して配列内で処理を行って、最後に1回だけ出力します。

配列の使い方については下記記事をご覧ください。

Sub Sample3()

Dim i As Long
Dim MyArray As Variant

MyArray = Range(Cells(1, 1), Cells(100000, 1))

For i = 1 To 100000

    MyArray(i, 1) = Replace(MyArray(i, 1), "A", "B")

Next i

Range(Cells(1, 1), Cells(100000, 1)) = MyArray

End Sub