こんにちは! 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
と加えてあげると完全一致したものを置き換えてくれます。
ただ、記録マクロの要領でマクロを書くと画面がちかちか動くので、裏側で動かしていない感が満載なのですけどね。
もちろん置換とひとえに言っても、様々な条件が付きます。どれが管理しやすいか? エラーを起こしくいか? 現場にあったベストなコードを書いてください。