こんにちは! Excel講師の榊裕次郎です。

Excelマクロの置換について考えてみました。データクレンジングを行う際、Replace関数を使って置き換えるのと、記録マクロで吐き出されるSelection.Replaceを使うのと、どっちのほうがマクロを共有しやすいか? というお題です。

サンプルデータをご覧ください。

オペレーション操作なら[検索と選択]→[置換]で置換操作を行うかと思います。1行目は見出しがあるので、2行目からデータがスタートです。

これをマクロで書いてみると……

VBAコード

① Sub Sample()
②  Dim i As Long
③  For i = 2 To 100
④   If Cells(i, 1) = "東京" Then
⑤    Cells(i, 1) = Replace(Cells(i, 1), "東京", "埼玉")
⑥   End If
⑦  Next i
⑧ End Sub

日本語訳

① サンプルマクロです。
②  変数「i」の箱を用意します。
③  変数「i」の箱に2~100を順番に入れていきます。まずは2から。
④   もし、セル「A2」が東京だったら
⑤    セル「A2」=セル「A2」を東京→埼玉に変換
⑥   条件終了
⑦  変数「i」に+1を加えて③に戻るよ。
⑧ サンプルマクロ終了。

となります。なのでループ処理が行われるわけですね。

続いて、記録マクロで行い、余計なものを削除したものがこちらです。

VBAコード

① Sub Sample2()
②  Columns("A:A").Select
③  Selection.Replace What:="埼玉", Replacement:="東京"
④  Range("A1").Select
⑤ End Sub

日本語訳

① サンプル2のマクロです。
②  A列を選択します。
③  東京から埼玉に変換します。
④  ホームポジションに戻ります。
⑤ サンプルマクロ2終了。

もし、VBAの不慣れな子と共有するときは、どちらを共有したほうがわかりやすいか……と考えたとき、後者じゃないかなと思いました。

ループ処理の前者は、このままだとワイルドカードを使うことができません。しかし、後者のコードだと、

③ Selection.Replace What:="埼*", Replacement:="東京"

と、修正すればワイルドカードを使って置き換えることができます。

処理の速度としては、どちらも変わらないので(100,000行とかのレベルになると後者のほうが早い)、後者のほうが楽なんじゃないかなと思いました。

もうすこし短縮すると……

VBAコード

① Sub Sample2()
②  Columns("A:A").Replace What:="埼玉", Replacement:="東京"
④  Range("A1").Select
⑤ End Sub

で済みます。この状態だと、「東埼玉」というセルが存在していた場合、「東東京」になってしまいますので、「埼玉」という文言と完全一致で置き換えたい場合は、

VBAコード

① Sub Sample2()
②  Columns("A:A").Replace What:="埼玉", Replacement:="東京" , LookAt:=xlWhole
④  Range("A1").Select
⑤ End Sub

と加えてあげると完全一致したものを置き換えてくれます。

ただ、記録マクロの要領でマクロを書くと画面がちかちか動くので、裏側で動かしていない感が満載なのですけどね。

もちろん置換とひとえに言っても、様々な条件が付きます。どれが管理しやすいか? エラーを起こしくいか? 現場にあったベストなコードを書いてください。