【VBA】13.1 Replace関数を使ってみよう!

目次

Replace関数の概要

Replace関数は、セルや変数にセットされた文字列の置換を行う関数です。

Replace関数に似た機能として、Replaceメソッドがありますが、この2つは以下のような違いがあります

機能名解説
Replace関数Replaceメソッドと異なり、1回の置換で1つのセルや変数の値の置換を行います。

一般的にReplaceメソッドのように大量&高速な置換はできませんが、以下のような場合にはReplace関数を使うことになります。
 マクロ内で作成した変数に対する置換
 ループ文でワークシート上のセルを走査し、セルの値に応じて置換処理を細かく
  制御する必要がある場合
Replaceメソッド1回の置換で、ワークシート上の以下のようなオブジェクト全体に対して置換を行います。
 セル
 全体
 全体
 RangeやCellsなどで指定した範囲、等

ワークシート上のオブジェクト以外のマクロ内で作成した変数などには使用できません。

行・列全体や範囲指定をして置換を実行した場合、対象の領域に対して、条件に合致する文字列を一括置換するため、ワークシート上の広い領域の置換では、Replace関数よりReplaceメソッドの方が、一般的に短いコードで高速に処理します

参考情報 Replaceメソッドに関する詳しい解説はこちら→【VBA】14.3 Replaceメソッドを使ってみよう!

[overdoserの経験則] Replace関数を実際の業務で使用するケース

Replace関数は一つずつ置換処理を行う機能であるため、以下のようなケースに使用することが想定されます。

  • Forループ文でワークシート上の任意の列の値を上から順に取得し、何らかの条件で分岐し、Replace関数でそれぞれの条件に応じた置換を行う。

  • 置換したい文字列がワークシート上のオブジェクトにセットされた値ではなく、変数にセットされた値である場合、Replaceメソッドでは置換できないため、Replace関数で置換する。

Replace関数の構文

Replace関数の構文は、以下の通りです。

  • 戻り値 = Replace(Expression, Find, Replace, Start, Count, Compare)

上記の構文では、Start以降の引数の条件に従って、引数 Expressionの文字列を引数 Findの文字列で検索し、見つかった場合は、その文字列を引数 Repalceで置換する、という処理を行います。
Repalce関数の戻り値は、引数 Startで指定した検索開始位置から末尾までの置換後の文字列です。

引数名必須/ 任意解説
Expression必須置換対象の文字列を含む文字列を指定します。
Find必須置換をする際の、置換したい文字列を指定します。
Replace必須置換をする際の、置換する文字列を指定します。
Start任意引数 Expressionに指定した文字列に対して、検索開始位置の文字数を
指定します。省略した場合は、「1(文字列の先頭)」となります。
Count任意置換する文字列の数を指定します。省略した場合は、見つかった全ての
文字列を置換します。
Compare任意文字列を検索する時の比較モードを以下の2つから指定します。
vbBinaryCompare(省略時はこちらが指定される)
vbTextCompare

Repace関数の使用例

Replace関数を実際に使用した例は、以下の通りです。

    For num_row = 2 To ws_employeelist.Cells(Rows.Count, 1).End(xlUp).Row
        If IsDate(ws_employeelist.Cells(num_row, 9)) Then
            ws_employeelist.Cells(num_row, 4) = Replace(ws_employeelist.Cells(num_row, 4), "@emalabo.com", "@gmail.com")
        End If
    Next num_row

上記のサンプルコードは、以下の処理を行っています。

  1. 社員一覧シート(ws_employeelist)の2行目から最終行までを繰り返す。
  2. 処理行の9列目の退職日のセルの値が日付の場合、3.の処理を行う。
  3. 処理行の4列目のメールアドレスを「@emalabo.com」→「@gmail.com」に置換する。

[応用編] Replace関数を用いたコーディング例

今回のコーディング例は、以下の要件を実現するものです。

  • 既に退職した社員については、メールアドレスのドメイン名を「@emalabo.com」→「@gmail.com」に修正する。

以下のソースコードは、上記のコーディング例をサンプルプログラムに組み込み、実際に動作するようにしたものです。

Option Explicit

Sub replace_employeelist()

    Dim ws_macro As Worksheet           '社員データ抽出マクロファイル内の社員データ抽出マクロシートのワークシート変数
    Dim ws_work As Worksheet            '社員データ抽出マクロファイル内のworkシートのワークシート変数
    Dim wb_inputfile As Workbook        '入力ファイルのワークブック変数
    Dim ws_employeelist As Worksheet    '入力ファイルの社員一覧シートのワークシート変数
    Dim path_inputfile As String        '入力ファイルのフルパスを設定する変数
    Dim num_row As Long                 'Forループ分のカウンター
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("社員データ置換マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_inputfile = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_inputfile = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    Workbooks.Open Filename:=path_inputfile
    Set wb_inputfile = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets("社員一覧")

    For num_row = 2 To ws_employeelist.Cells(Rows.Count, 1).End(xlUp).Row
        If IsDate(ws_employeelist.Cells(num_row, 9)) Then
            ws_employeelist.Cells(num_row, 4) = Replace(ws_employeelist.Cells(num_row, 4), "@emalabo.com", "@gmail.com")
        End If
    Next num_row
    
    wb_inputfile.Close SaveChanges:=True
    
    MsgBox "メールアドレスの置換が完了しました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

上記のマクロを実行すると、以下の結果となります。

メールアドレス置換結果
メールアドレス置換結果

[応用編] ソースコードの解説

開始行数終了行数処理概要
1725マクロシートに記載された入力ファイルをOpenし、
入力ファイル用のワークブック変数、ワークシート変数を設定する。
2731要件で指定された条件で、メールアドレスのドメイン名の置換を行っている。
3333入力ファイルを保存してCloseしている。
3541後処理を行っている。

この記事を書いた人

某IT企業に勤務。

2年間の休職を経て、一から独学でExcel VBAを習得し、半年でExcel VBA エキスパート資格のスタンダードに一発合格。

社内の非効率的な作業を効率化するExcelマクロの開発を、全行程まるっと一人で請け負い、これまでに約100個のExcelマクロを開発し、現在も増産中。

目次