Excel 2013 マクロ講座36回

リストを使って複数の語を連続置換するマクロ

前回のマクロ講座35回は複数の語を連続置換するマクロを、作成しました。
頻繁に複数語句の置換を行ったり大量の置換作業がある場合は、マクロのコードを書き換えるのが大変です。
そこで、置換する語句を元に置換リストを作成してリストから置換してくれるマクロを作成しましょう。


(サンプルファイルは、こちらから 2013マクロ講座36回サンプルデータ

リストを使った複数語句の置換マクロ

効率良く置換するためにリストを作成します。
置換前文字列と置換後文字列を変数として使用しましょう。
Sub リストによる文字列の置換()
    Dim 前文字列1 As String
    Dim 後文字列2 As String
複数語句の置換1
置換対象の表は、マクロ講座35回で使った、新商品リストです。
この表の価格と商品名を変更します。この表の変換対象範囲とシートを変数とします。
複数語句の置換2
変数を宣言します。なるべく日本語で変数を宣言しているのは、わかりやすくするためです。(といいながら、範囲は、"rng”にしてしまいましたが、Rangeから連想できるので大丈夫ですね。)
    Dim rng As Range
    Dim 商品リスト As Worksheet
    Dim 変更データ As Worksheet
複数語句の置換3

変数を宣言しただけじゃダメなの?

rngや商品リスト、変更データは、操作の対象となるオブジェクトです。
オブジェクトを宣言するとそれが何の変数か明示することになります。
しかし値をセットしたわけではないため、実体がありません。いわばまだ、空の箱状態です。
Set ステートメント
そこで、Setステートメントを使用して実体のシートをオブジェクト変数に格納することで利用できるようにします。
    Set 商品リスト = ThisWorkbook.Worksheets("新商品")
    Set 変更データ = ThisWorkbook.Worksheets("データ")
ここまでのコードをまとめましょう。
Sub リストによる文字列の置換()
    Dim 前文字列1 As String, 後文字列2 As String
    Dim rng As Range
    Dim 商品リスト As Worksheet, 変更データ As Worksheet 
    Set 商品リスト = ThisWorkbook.Worksheets("新商品")
    Set 変更データ= ThisWorkbook.Worksheets("データ")
あともう少しです。
スポンサーリンク
スポンサーリンク

Replaceメソッドで何がしたいのか

前回同様、指定した範囲の中から検索した文字列を他の文字列に置換するにはReplaceメソッドを利用します。
Replace メソッド
Replace メソッド
引数LookAt, SearchOrder, MatchCase, MatchByteは先に使用した値が使用されるため、2回目以降の処理では省略できます。
Replaceメソッドで何がしたいのかといえば、たったこれだけのことです。
 rng.Replace What:=前文字列1, Replacement:=後文字列2

変数iはカウンター

変更データの前文字列1、後文字列2を区別するために変数iをカウンターにして、For~Nextで指定回数分のループを実行します。
あ
最終的なコードはこのようになります。一見複雑そうですが、これまでの説明で変数を指定して、それがオブジェクトの場合は、Excelに実体をつかませるためにSetステートメントを使っているということが理解できると思います。
Sub リストによる文字列の置換()
    Dim 前文字列1 As String, 後文字列2 As String
    Dim rng As Range
    Dim 商品リスト As Worksheet, 変更データ As Worksheet
    Set 商品リスト = ThisWorkbook.Worksheets("新商品")
    Set 変更データ = ThisWorkbook.Worksheets("データ")
    Dim i As Integer
    For i = 2 To 7
        前文字列1 = 変更データ.Cells(i, 1)
        後文字列2 = 変更データ.Cells(i, 2)
        Set rng = 商品リスト.Columns("B:C")
        rng.Replace What:=前文字列1, Replacement:=後文字列2
    Next i
End Sub
コードを実行すると以下のようになります。
あ

今日の講義は以上です。お疲れ様でした。
スポンサーリンク
スポンサーリンク