MENU
Excel Macro Laboratory
  1. ホーム
  2. ExcelVBA解説編
  3. 【VBA】14.3 Replaceメソッドを使ってみよう！

【VBA】14.3 Replaceメソッドを使ってみよう！

ExcelVBA解説編
目次

Replaceメソッドの概要

Replaceメソッドは、ワークシート上のオブジェクト（セルなど）に対して、文字列の置換を行うメソッドです。
Excelの置換コマンドと同等の機能となります。

置換ウィンドウ
置換ウィンドウ

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

機能名解説
Replaceメソッド1回の置換で、ワークシート上の以下のようなオブジェクト全体に対して置換を行います。
　セル
　行全体
　列全体
　RangeやCellsなどで指定した範囲、等

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

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

一般的にReplaceメソッドのように大量＆高速な置換はできませんが、以下のような場合にはReplace関数を使うことになります。
　マクロ内で作成した変数に対する置換
　ループ文でワークシート上のセルを走査し、セルの値に応じて置換処理を細かく
　　制御する必要がある場合

参考情報 Replace関数に関する詳しい解説はこちら→【VBA】13.1 Replace関数を使ってみよう！

[overdoserの経験則] Replaceメソッドを実際の業務で使用するケース

Replaceメソッドは、Excel上のワークシートの編集で利用されていると思いますので、イメージしやすいかと思いますが、実際の業務で使用するExcelマクロでは、以下のようなケースが想定されます。

  • データのフォーマット変換を行うマクロで、元データのある列について、ある特定のキーワードのみ一律置換するような場合。

  • 社内システムのトランザクションデータのついて、ファイルベースで他システムにI/FするデータをExcelマクロでフォーマット変換で置換を行う必要がある場合に、トランザクションデータの件数が多く、処理時間も短くするような要件である場合。

Replaceメソッドの構文

Replaceメソッドの構文は、以下の通りです。

  • Worksheet変数.Range(“設定値”).Replace What:=.”設定値”, Replacement:=”設定値”, LookAt:=”設定値”, MatchCase:=”設定値”, MatchByte:=”設定値”,

上記の構文では、置換対象をWorksheet変数.Range(“設定値”)の領域にある全てのセルとし、各種条件に従って、Whatに設定された文字列をReplacementに設定された文字列で置換する、という処理を行います。

引数名必須/ 任意解説
What必須置換をする際の、置換したい文字列を指定します。
Replacement必須置換をする際の、置換後の文字列を指定します。
LookAt任意（＊）以下のいずれかの値を設定します。
xlPart：置換する文字列が、検索対象文字列に部分一致した場合に置換を実行
xlwhole：置換する文字列が、検索対象文字列に完全一致した場合に置換を実行
MatchCase任意（＊）以下のいずれかの値を設定します。
True：大文字と小文字を区別する場合
False：大文字と小文字を区別しない場合
MatchByte任意（＊）以下のいずれかの値を設定します。
True：全角と半角を区別する場合
False：全角と半角を区別しない場合

＊：省略した場合は、前回に置換処理を行った際の設定を用いて置換処理を行います。

注意 Replaceメソッドの引数には上記以外に、「SearchOrder」、「SearchFormat」、「ReplaceFormat」もありますが、使用頻度が低いため、割愛しました。

Replaceメソッドの使用例

Replaceメソッドを実際に使用した例は、以下の通りです。

    ws_employeelist.Columns(5).Replace What:="A01001", Replacement:="B01001", LookAt:=xlwhole

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

  • 社員一覧シート（ws_employeelist）の5列目の部署コードの値が”A01001″に完全一致した場合は、”B01001″に置換する。

[応用編] Replaceメソッドを用いたコーディング例

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

  • 来年度から、部署コードが以下のように変更になるため、社員一覧シートの部署コードの値を置換する。
    “A01001” → “B01001”
    “A11001” → “B11001”

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

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 ws_positionlist As Worksheet    '入力ファイルの役職一覧シートのワークシート変数
    Dim path_inputfile As String        '入力ファイルのフルパスを設定する変数
    Dim wb_outputfile As Workbook       '出力ファイルのワークブック変数
    Dim ws_outputfile As Worksheet      '出力ファイルのワークシート変数
    Dim path_outputfile As String       '出力ファイルのフルパスを設定する変数
    
    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("社員一覧")
    Set ws_positionlist = ActiveWorkbook.Worksheets("役職コード一覧")
    
    If Right(ws_macro.Range("D4"), 1) <> "\" Then
        path_outputfile = ws_macro.Range("D4") & "\" & ws_macro.Range("C4")
    Else
        path_outputfile = ws_macro.Range("D4") & ws_macro.Range("C4")
    End If
    
    ws_employeelist.Columns(5).Replace What:="A01001", Replacement:="B01001", LookAt:=xlWhole

    ws_employeelist.Columns(5).Replace What:="A11001", Replacement:="B11001", LookAt:=xlWhole

    ws_employeelist.Copy
    Set wb_outputfile = ActiveWorkbook

    wb_inputfile.Close SaveChanges:=False
    
    wb_outputfile.Close SaveChanges:=True, Filename:=path_outputfile
    
    MsgBox "部署コードの置換が完了しました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

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

Replaceメソッド実行結果

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

開始行数終了行数処理概要
2029マクロシートに記載された入力ファイルをOpenし、
入力ファイル用のワークブック変数、ワークシート変数を設定する。
3135出力ファイルを保存するための出力パスの編集を行っている。
3737役職コードに対して、”A01001″ → “B01001″の置換を実行している。
3939役職コードに対して、”A11001″ → “B11001″の置換を実行している。
4142入力ファイルで置換を行ったが、今年度の社員一覧シートと置換後の来年度の社員一覧シートは別々に保存しておくため、入力ファイルを新規のExcelファイルとして別ファイルを作成し、そこに置換を行った社員一覧シートをコピーしている。
4444入力ファイルを保存せずにCloseしている。
4646出力ファイルを保存してCloseしている。
4854後処理を行っている。
ExcelVBA解説編

この記事を書いた人

overdoserのアバター overdoser

某IT企業に勤務。

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

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

目次