エクセルVBAで複数の文字列・単語をまとめて連続置換する方法

こんにちは～。「デザインもできるシステムエンジニア」のあつもり(@atumori17)です。今日も業務効率化のアイデアを発信していきますっ！

エクセルには文字列を置き換える「置換」という機能があるのはご存知だと思います。日常業務の中でとても頻繁に使われる機能のひとつですよね。

さてこの「置換」ですが、1つの単語だけではなく、複数の単語を1度に置き換えたいと思ったことはありませんか？

例えば

洋服の商品マスタがあり、そのデータを整形するような場合です。色にBK、WHなどの略字が使われているので正規の表現に戻したい。
エクセルでマニュアルを作ったけど、句読点やカッコに半角、全角が入り混じっているのでどちらかに統一したい。

どちらも置換した単語が複数個あるので、何度も置換しなければならず不便ですよね。

こういったケースの場合、下図のような変換表どおりに連続置換できたら便利ですよね。というわけで今回は「複数の文字列・単語をまとめて連続置換する方法」を紹介したいと思います。

1 複数の単語をまとめて連続置換プログラムの作成
2 プログラムコードの解説
3 複数の単語を連続置換するプログラムの動作確認
4 まとめ

複数の単語をまとめて連続置換プログラムの作成

それではさっそくエクセルVBAを使って連続置換プログラムを作っていきましょう。

エクセルファイルにボタンを追加する

まずは「連続置換.xls」という名前のファイルを作りデスクトップに保存します。

ワークシートに起動ボタンを配置します。

作った起動ボタンのクリックイベントに下記のようなプログラムコード書きます。プログラムコードを書く場所はシートモジュールの中です。

Private Sub CommandButton1_Click()
    Load UserForm1
    UserForm1.Show vbModeless    
End Sub

Private Sub CommandButton1_Click()

Load UserForm1

UserForm1.Show vbModeless

End Sub

続いてワークシートに下図のような置換表を作ります。シート名を「置換表」にして下さい。

ユーザーフォームの作成

今度はユーザーフォームを作っていきます。新規でユーザーフォームを作成しボタンを配置します。ボタンの表示は「連続置換」にしてください。

ここまででユーザーインターフェース部分の作成は終わりです。

サンプルコードを貼り付ける

次にプログラムコードをモジュール内に貼り付けます。

「連続置換」ボタンをダブルクリックしてコードウィンドウを開きます。「CommandButto1_Click()」の下に以下のコードを貼り付けます。

'連続置換
Private Sub CommandButton1_Click()

Dim tmp
Dim Col As Long
Dim Row_Start As Long
Dim Row_End As Long
Dim strCelldata As String
Dim y As Long
Dim strRep As String
'置換表のワークシート
Dim WS_RepList As Worksheet
Set WS_RepList = ThisWorkbook.Sheets("置換表")
Dim beforeConv As String
Dim afterConv As String
Dim y_RepList As Long

    '処理対象シートの処理開始行
    Row_Start = 2
    
    '最終行を取得
    With ActiveSheet.UsedRange
     Row_End = .Rows(.Rows.Count).Row
    End With

    'キャンセルが押された場合は処理を終了する
    On Error GoTo myError
    tmp = Application.InputBox("列を選択して下さい", "列の選択", Type:=8).Address
    Range(tmp).Select
    
    '選択した列番号
    Col = Selection.Column
                       
    '処理対象シートのループ
    For y = Row_Start To Row_End
        strCelldata = Cells(y, Col).Value
        
        '置換表シートの処理開始行
        y_RepList = 2
        
        '置換表の置換項目がなくなるまでループする
        Do Until WS_RepList.Cells(y_RepList, 1).Value = ""
        
            '置換前文字列
            beforeConv = WS_RepList.Cells(y_RepList, 1).Value
            '置換後文字列
            afterConv = WS_RepList.Cells(y_RepList, 2).Value
            '文字列の置換え
            strCelldata = Replace(strCelldata, beforeConv, afterConv)
            '行番号を1つ増やす
            y_RepList = y_RepList + 1
        Loop
        
        Cells(y, Col).Value = strCelldata
    Next y    
myError:
End Sub

'連続置換

Private Sub CommandButton1_Click()

Dim tmp

Dim Col As Long

Dim Row_Start As Long

Dim Row_End As Long

Dim strCelldata As String

Dim y As Long

Dim strRep As String

'置換表のワークシート

Dim WS_RepList As Worksheet

Set WS_RepList = ThisWorkbook.Sheets("置換表")

Dim beforeConv As String

Dim afterConv As String

Dim y_RepList As Long

'処理対象シートの処理開始行

Row_Start = 2

'最終行を取得

With ActiveSheet.UsedRange

Row_End = .Rows(.Rows.Count).Row

End With

'キャンセルが押された場合は処理を終了する

On Error GoTo myError

tmp = Application.InputBox("列を選択して下さい", "列の選択", Type:=8).Address

Range(tmp).Select

'選択した列番号

Col = Selection.Column

'処理対象シートのループ

For y = Row_Start To Row_End

strCelldata = Cells(y, Col).Value

'置換表シートの処理開始行

y_RepList = 2

'置換表の置換項目がなくなるまでループする

Do Until WS_RepList.Cells(y_RepList, 1).Value = ""

'置換前文字列

beforeConv = WS_RepList.Cells(y_RepList, 1).Value

'置換後文字列

afterConv = WS_RepList.Cells(y_RepList, 2).Value

'文字列の置換え

strCelldata = Replace(strCelldata, beforeConv, afterConv)

'行番号を1つ増やす

y_RepList = y_RepList + 1

Loop

Cells(y, Col).Value = strCelldata

Next y

myError:

End Sub

プログラムコードの解説

それではプログラムコードの解説をしていきます。重要なポイントに絞って説明していきます。全体の流れは過去に掲載した記事と同じですので、参考にしてみてください。

エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法

ワークシートのセル内にある改行を消したいって思ったことありませんか？データを別のアプリケーションに取り込んだり、データベースに登録したりするときに改行が邪魔になることがあります。そんな時に改行を一括で削除できるツールがあったら便利ですよね。

asatte.biz

2017-06-24 08:36

プログラム全体の流れは以下のようになります。

処理対象列を選択する
処理対象ワークシートのデータを１行ずつ取り出す
置換表ワークシートの数だけ置換を繰り返す

ワークシートをオブジェクト変数に入れる

ワークシートをオブジェクト変数に入れることでプログラムの可視性が上がります。ワークシート自体を変数として扱えるため短いコードでプログラムを書くことができます。

オブジェクト変数を使った場合

 Do Until WS_RepList.Cells(y_RepList, 1).Value = ""        
   beforeConv = WS_RepList.Cells(y_RepList, 1).Value

1 2	Do Until WS_RepList.Cells(y_RepList, 1).Value = "" beforeConv = WS_RepList.Cells(y_RepList, 1).Value

オブジェクト変数を使わない場合

 Do Until ThisWorkbook.Sheets("置換表").Cells(y_RepList, 1).Value = ""    
   beforeConv = ThisWorkbook.Sheets("置換表").Cells(y_RepList, 1).Value

1 2	Do Until ThisWorkbook.Sheets("置換表").Cells(y_RepList, 1).Value = "" beforeConv = ThisWorkbook.Sheets("置換表").Cells(y_RepList, 1).Value

オブジェクト変数を使ったほうがコードが短くなりすっきりしていますよね。オブジェクトを変更したときのメンテナンスも楽になります。

Setステートメントでオブジェクト変数を使う

まずは変数の宣言ですが次のようにします。

Dim オブジェクト変数 As オブジェクト型

オブジェクト型はWorksheetのほかにWorkbook、Rangeなどがあります。

続いてSetステートメントを使ってオブジェクト変数にワークシートを格納します。

Set オブジェクト変数 = オブジェクト

このようにすることでオブジェクト変数を使ってワークシートを操作することができるようになります。

Do Loopステートメントを使って置換項目ぶん繰り返す

Do 条件式
処理
Loop

このようにすることで条件式が真になるまで処理を繰り返し実行させることができます。

今回のプログラムを見てみましょう。上記コードの42行目にあたります。

Do Until WS_RepList.Cells(y_RepList, 1).Value = ""

1	Do Until WS_RepList.Cells(y_RepList, 1).Value = ""

この場合、置換表の1列目のデータがなくなるまで処理を続けるということになります。

プログラムコードの解説は以上です。

複数の単語を連続置換するプログラムの動作確認

それでは実際に出来上がったプログラムを動かしてみましょう。

ワークシートの起動ボタンを押します。

処理対象のシートに移動します。連続置換ボタンを押し、処理をしたい列を選択します。ダイアログボックスのOKボタンを押します。

置換表のルールに従って文字が置換されました。

まとめ

いかがでしたか？今回はエクセルVBAで複数の文字列・単語をまとめて連続置換する方法を紹介しました。

今回の内容は以下のようになります。

置換表をワークシート上に作成する
ワークシートをオブジェクト変数に入れる
Do until文でセルのデータがなくなるまでループさせる

それではまた～。