【ExcelVBA】複数文字列の組み合わせを一気に置換するマクロ

はじめに

エクセルでは「Ctrl+F」にて以下画面が出てくるのでFind Whatに置換元、Replace Withに置換後の文字列を入れてReplace Allボタンを押下すると一括で置換が可能です。

ただ、このFindWhatとReplaceWithが複数セットである場合は手動で行うと労力がかかります。

本記事ではそのような場合に利用できるマクロと、そのマクロを生成するシェルスクリプトについて解説します。

環境情報

Excel：Microsoft Office 365 2020年
Cygwin：GNU bash, バージョン 4.4.12(3)-release (x86_64-unknown-cygwin)

置換用VBA

今回のサンプルコードは開いているExcelのワークブック全てを対象に置換をかけるので必要なファイルのみを開いてください。

以下をMicrosoft Viusual Basic for Applicationに張り付けてRunを行えば、全てのシートにあるセルの「インタネット」が「インターネット」になります。

Sub ReplaceBook()
Dim WBsh As Worksheet
For Each WBsh In ActiveWorkbook.Worksheets
WBsh.UsedRange.Replace What:="インタネット", Replacement:="インターネット", LookAt:=xlPart
Next WBsh
End Sub

よって

WBsh.UsedRange.Replace What:="X", Replacement:="X", LookAt:=xlPart

の箇所を複数行連ねれば一気に置換するマクロとなります。

VBA置換コード生成スクリプト

自分の用に作成したのでシェアします。
以下のようなテキストファイルにCSV区切りでリストをファイルに保管すれば

$ cat test.txt
Tokyo,東京
Osaka,大阪
Hiroshima,広島
Fukuoka,福岡

以下コードを実行すれば

#!/bin/bash
echo "Sub ReplaceBook()"
echo "Dim WBsh As Worksheet"
echo "For Each WBsh In ActiveWorkbook.Worksheets"

while read line
do
        name_from=`echo $line | cut -d"," -f1`
        name_to=`echo $line | cut -d"," -f2`
        echo "WBsh.UsedRange.Replace What:=\""$name_from"\", Replacement:=\""$name_to"\", LookAt:=xlPart"
done < ./test.txt
echo "Next WBsh"
echo "End Sub"

標準出力に以下が出力されるのでそのままVBAとして貼り付けて実行可能です。

Sub ReplaceBook()
Dim WBsh As Worksheet
For Each WBsh In ActiveWorkbook.Worksheets
WBsh.UsedRange.Replace What:="Tokyo", Replacement:="東京", LookAt:=xlPart
WBsh.UsedRange.Replace What:="Osaka", Replacement:="大阪", LookAt:=xlPart
WBsh.UsedRange.Replace What:="Hiroshima", Replacement:="広島", LookAt:=xlPart
WBsh.UsedRange.Replace What:="Fukuoka", Replacement:="福岡", LookAt:=xlPart
Next WBsh
End Sub

終わりに

Excelの一括置換を複数のペアで行いたい際のTipsです。特にReplace構文をいちいち打ち込むのが面倒なので今回はシェルスクリプトにしました。

以上、ご参考になれば幸いです。

(O+P)ut

アウトプット