マクロで文字列の置換はVBA関数のReplace|4つの方法を比較

お知らせ記事には広告が含まれておりますがExcelのスキルUPに繋がる様コンテンツ自体は手を抜かずに作成しております

マクロで文字列の置換はVBA関数のReplace関数1択

本記事では「文字列の置換」について説明します。VBAでも置換は可能です。

VBA初心者でもワークシート関数に詳しい方であれば比較的簡単なコードで作業を構築できます。しかし問題もあります。

置換についてはどの方法を使えばよいのか分からないという質問が多いです。実際に方法は複数存在します。

まずは問い合わせがあった方法を4点並べてみます。結論から申し上げますと「どれでもOK」です。

  1. VBA関数のReplace関数
  2. RangeオブジェクトのReplaceメソッド
  3. ワークシート関数のReplaceメソッド
  4. ワークシート関数のSubstituteメソッド
EnjoyExcel
EnjoyExcel

私個人としてはVBA関数のReplace関数1択です。

ポイントは「コードの書きやすさ」と「配列」です。

本記事ではまず4つの方法を深堀りします。そのあとで情報を総括して結論を出します。

最後に4つの中からおすすめの「VBA関数のReplace関数」をピックアップして事例を紹介します。

関連記事

置換と一緒に行う作業はやはり「転記」ですよね。

コピーして値を加工後(置換後)貼り付け先に転記するという行為は多くの皆様が経験されている事でしょう。

関連記事としてコピペや転記の記事を用意させていただきました。本記事と合わせてご覧ください。

コピー&ペーストがわかるVBA×コピペ|値のみ貼り付け&簡単高速にコピーできる方法を公開

ブック間の転記についてVBAで転記|ブック間で文字列をコピペするマクロの作り方を教えます

関連書籍

こちらの書籍ではReplace関数をシンプルに説明しています。

加えて事例形式で便利な機能をたくさん紹介しているのでおすすめ書籍として紹介させていただきました。

仕事をマクロに置き換える事ができる

おすすめは電子書籍です。私大半の書籍は電子書籍で購入しています。

文字列を置換のアイキャッチ画像

文字列を置換する4つの方法

まずは一目で情報を整理できる様に表を用意しました。こちらをご覧ください。

番号大分類小分類採用可否詳細(使い時など)
1VBA関数Replace関数採用コーディング時の書きやすさと汎用性の高さから採用
2RangeオブジェクトReplaceメソッド不採用4つの方法の中で唯一配列と一緒に使えないので不採用
3ワークシート関数Replaceメソッド不採用1番の下位互換なので不採用
4ワークシート関数Substituteメソッド不採用1番と使い方が同じなので不採用
表の中にリンクを用意しています。必要に応じて説明を読んでください。

以降1から4番の情報を順番に説明していきます。結論としては1番の方法しか使わないのですが比較の為すべて説明します。

1_VBA関数のReplace関数

まずは構文とパラメータです。Microsoftのドキュメントから引用します。

構文

Replace(expressionfindreplace, [ start, [ count, [ compare ]]])

パーツ説明
expression必須。 置換処理を行うサブ文字列が格納されている 文字列式
find必須。 検索するサブ文字列。
replace必須。 置換後の文字列。
start省略可能。 検索して返される  の部分文字列の開始位置。 省略した場合は、1 が使用されます。
count省略可能。 実行するサブ文字列置換の回数。
省略すると、既定値は -1 になります。つまり、可能なすべての置換を行います。
compare省略可能。 サブ文字列を評価するときに使用する比較の種類を示す数値です。
値については、「設定」セクションを参照してください。
Microsoft:Replace 関数より引用

引用先にはパラメータ(compare)の詳細が掲載されています。必要に応じてご覧ください。

ポイント:一括で文字列を置換/指定した文字列だけ置換

基本的にはパラメータ3つだけで置換が可能です。非常にわかりやすくコードもシンプルです。

パラメータexpressionの中にあるパラメータfindで指定した文字列をパラメータreplaceに一括で置換します。

比較的簡単に文字列を置換したい時におすすめの方法です。

加えて配列との相性も良いので私はこの方法を採用しています。

さらにパラメータfindと一致している文字がいくつかあるときは個別に置換することも可能です。汎用性も高いです。

個別に置換する方法は以降事例の中で紹介します。

2_RangeオブジェクトのReplaceメソッド

構文とパラメータです。Microsoftのドキュメントから引用します。

構文

Replace (What、 Replacement、 LookAt、 SearchOrder、 MatchCase、 MatchByte、 SearchFormat、 ReplaceFormat)

名前必須 / オプションデータ型説明
What必須バリアント型Microsoft Excel で検索する文字列。
Replacement必須バリアント型 (Variant)置き換える文字列を指定します。
LookAt省略可能Variant次のXlLookAt定数:xlWholeあるいはxlPartのいずれかになります 。
SearchOrder省略可能Variant次のXlSearchOrder定数:xlByRowsあるいはxlByColumnsのいずれかになります 。
MatchCase省略可能Variant大文字と小文字を区別するには、True を指定します。
MatchByte省略可能バリアント型この引数は、Microsoft Excel で 2 バイト言語サポートを選択またはインストールした場合にのみ使用します。
2 バイト文字が 2 バイト文字とだけ一致するようにする場合は、True を指定します。
2 バイト文字が同等の 1 バイト文字とも一致するようにする場合は、False を指定します。
SearchFormat省略可能バリアント型 (Variant)メソッドの検索書式を指定します。
ReplaceFormat省略可能バリアント型 (Variant)このメソッドの置換書式。
Microsoft:Range.Replace メソッド (Excel)から抜粋

引用先にはより詳しい情報が掲載されています。必要に応じてご覧ください。

ポイント:配列が使えない

たくさんパラメータ(引数)を持っているので複雑なことも出来ますが致命的な欠点があります。

Rangeオブジェクトに所属するメソッドなのでパラメータWhatはセルに用意された文字列になります。

よって配列との相性が悪いので私は不採用にしています。配列を使わない方には関係ないので必要に応じてご使用ください。

配列について知りたい方はこちらの記事をご覧ください。

配列初級編【検索にも使える】VBAの2次元配列で作業の高速化を実現させる

こちらの記事には1つの処理に対して配列を使ったコードと配列を使わないコードを用意しています。

コピペで使えるようにしてありますので簡単に配列の処理速度を体感することが出来ます。きっと驚きますよ。

3_ワークシート関数のReplaceメソッド

構文とパラメータです。Microsoftのドキュメントから引用します。

構文

Replace (Arg1、 Arg2、 Arg3、 Arg4)

名前必須 / オプションデータ型説明
Arg1必須文字列型 (String)置き換えを行う文字列を指定します。
Arg2必須DoubleArg4 と置き換える Arg1 内の文字の位置を指定します。
Arg3必須DoubleReplace メソッドで Arg4 と置き換える Arg1 内の文字の数を指定します。
Arg4必須StringArg1 の一部と置き換える文字列を指定します。
Microsoft:WorksheetFunction.Replace メソッド (Excel)から抜粋

引用先のドキュメントには事例も掲載されています。必要に応じてご覧ください。

ポイント:VBA関数のReplace関数の下位互換

一番シンプルで分かりやすいのがこの方法の最大のメリットです。加えて配列との相性も良いです。

  1. パラメータArg1の中に置換対象の文字が1つ
  2. 置換対象の文字位置に規則性があり毎回同じ位置

2つの要件が成立している時にはこの方法を採用しても良いのですがVBA関数のReplace関数でも同じことが出来ます。

よって積極的には使いません。基本は不採用です。

ワークシート関数なのでコーディング時は文頭に「WorksheetFunction・・・」と書く必要があります。

字数も多くコードも読みにくくなるので使わない事が多いです。やはり基本は不採用です。

4_ワークシート関数のSubstituteメソッド

構文とパラメータです。Microsoftのドキュメントから引用します。

構文(一部加工しました)

Substitute(Arg1、 Arg2、 Arg3、 Arg4)

名前必須 / オプションデータ型説明
Arg1必須文字列型 (String)文字列: 置き換える文字を含む文字列を指定します。
目的の文字列が入力されたセル参照を指定することもできます。
Arg2必須StringOld_text – 置き換えるテキスト。
Arg3必須StringNew_text – old_textに置き換えるテキスト。
Arg4省略可能バリアント型 (Variant)置換対象: 文字列に含まれるどの検索文字列を置換文字列に置き換えるかを指定します。
置換対象を指定した場合、文字列中の置換対象番目の検索文字列だけが置き換えられます。
この指定を行わないと、文字列中のすべての文字が新しい文字に置き換えられます。
Microsoft:WorksheetFunction.Substitute メソッド (Excel)から抜粋

ポイント:VBA関数のReplace関数と同じ

ワークシート関数のSubstituteメソッドはVBA関数のReplace関数と同じ機能なので不採用です。

VBA関数のReplace関数を選ぶ理由はコーディング時の文字数です。

3番のコードと一緒でSubstituteメソッドもワークシート関数です。文頭に「WorksheetFunction・・・」が必要です。

1番のコードはVBA関数なので「WorksheetFunction・・・」が不要です。

総括:VBA関数のReplace関数を使いましょう

あくまで私の考えですが置換は「VBA関数のReplace関数」1択で良いです。

理由は各方法を説明する中で解説していますのでここでは省略します。

以降事例の中で実際にVBA関数のReplace関数を使ってみます。

備考:ワークシート関数はパラメータの表記が同じ

ワークシート関数を理解せずにVBAで使う時は注意が必要です。

ワークシート関数はパラメータが全てArg表記です。おそらくArgumentの略ですね。引数という意味です。

注意が必要な理由はこれです。コーディング時にパラメータが全てArg表記になってしまうという事です。

例えばパラメータが4つの時はVBEでのコーディング時に自動クイックヒントの表記がArg1,Arg2,Arg3,Arg4と表示されます。

これは上段で紹介した各方法のパラメータの名前と同じ表記で自動クイックヒントが生成されているだけです。

よって想定通りの表記なのですがちょっと不親切な感じを持ちます。

一方パラメータに固有名詞を持っているワークシート関数以外の要素であればどうでしょうか。

Microsoftのドキュメントに書かれている通りの情報が返ってきます。自動クイックヒントは以下画像をご覧ください。

一見情報量が多く感じるこの表記でも「パラメータが分かってなければ一緒でしょ?」という意見もあります。

最終的にはユーザー様の感じ方次第ですが私はこの点からも使用についてはVBA関数の方が良いなと感じました。

事例

Replace関数を使用するために事例を用意しました。

ワークシートの状態(文字列置換前)

セルに書き込むタイプのSubstitute関数の使用例の中にあった文字列を少し加工してサンプルとして使います。

ワークシートのセルB5に「2024 第 1 四半期」という文字列を用意しています。

全角、半角、スペース、数字、漢字という色々な要素が入っているので採用した次第です。

アルファベット(大文字、小文字)は含まれていませんが機能を知るうえでは良いサンプルです。

事例:作業前

この文字列をオートフィルすると以下画像の様に文頭の年だけ進んでしまいます。

各年で第1四半期から第4四半期に値が変わってから年が1つ進むという具合にオートフィルは動いてくれない様です。

事例:オートフィル

ワークシートの状態(文字列置換後)

この後紹介するコードを用いることで以下画像の様に5年分の文字列を用意することが出来ます。

Replace関数を使うことで赤枠内の文字列を作り出しています。

事例:作業後

コード

コードはボタンでコピーすることが出来ます。必要に応じてご利用下さい。

Sub VBA関数のReplace関数()

Dim r As Long '行数を格納

Dim d_vle As Long 'default_value 1から4の値を格納
Dim f_vle As Long 'final_value 変数d_vleに1を足した値を格納

Dim d_stg As String 'default_string 最終行の値を格納
Dim f_stg As String 'final_string 加工後セルに代入する値を格納

'5年分の文字列を用意するためのループを用意
For r = 6 To 24

    '最終行の値を変数に格納する
    d_stg = Cells(r - 1, 2)
    
    '変数f_vleの値で処理を分岐する
    If f_vle = 4 Then
        
        '数字を数値で取り出す
        d_vle = Val(Mid(d_stg, 1, 4))

    '数値同時で足し算
        f_vle = d_vle + 1
        
        '数字として戻す(年を進めた文字列を変数に格納する)
        f_stg = Replace(d_stg, d_vle, f_vle)
        
        '1に戻す(第 1 四半期に戻す)
        f_stg = Replace(f_stg, "第 4", "第 1")
    
    Else
    
        '数字を数値で取り出す
        d_vle = Val(Mid(d_stg, 9, 1))

    '数値同時で足し算
        f_vle = d_vle + 1
        
        '数字として戻す(四半期を1つ進めた文字列を変数に格納する)
        f_stg = Replace(d_stg, "第 " & d_vle, "第 " & f_vle)
        
    End If
        
    'セルに値を戻す
    Cells(r, 2) = f_stg

Next

End Sub

初心者向けの記事になることを想定してコメントを多めに書いています。

コメントを減らして行間を詰めればそんなにたくさんのことは書いてないです。

解説

ポイントは4つです。各ポイント毎に説明していきます。

  1. For~Nextステートメント
  2. Ifステートメント
  3. Mid関数
  4. Replace関数

1_For~Nextステートメント

5年分のデータを用意するためにループを準備します。今回はループの始点を「6」、終点を「24」に設定しました。

最初の文字列はセルB5に書いてあるので残りの19個の文字列を用意する必要がある為です。(1年で4行×5年-1)

For~Nextステートメントが分からない方はこちらの記事をご覧ください。

ローカルウインドウの使い方を含め丁寧に解説しています。For~Nextステートメントが苦手な方におすすめの記事です。

初心者向おすすめVBA-12繰り返し処理をFor~Nextステートメントで学習する

2_Ifステートメント

各年の第4四半期まで文字列を用意することが出来たら次の行は年を1つ進める必要があります。

年を1つ進めるためのスイッチを条件Trueの処理が担当しています。変数f_vleの値が4の時は以下処理を実行します。

変数f_vleの値が「4」の時
  • 1
    数字を数値で取り出す

    Mid関数を使って数字(文字)を取り出した後にVal関数で数値にしてから変数d_vleに格納します。

  • 2
    数値同時で足し算

    1つ数値を進めたいので足し算をします。1を足す為に数値にした次第です。

  • 3
    数字として戻す

    Replace関数を使って文字列を置換します。

  • 4
    1に戻す

    年を1つ進めた時は必ず第1四半期から始まるのでここでは強制的に値を1に戻します。

処理を画像にしてみました。IfステートメントのIfからElseの間に書かれた処理です。

文字列の一部を置き換える-1
「数字」が文字列で「数値」が数です。ご注意ください。

一方変数f_vleの値が4以外の時は以下処理を実行します。

こちらのコードの集団は主に四半期の頭に配置される数値をコントロールするために用意されたコードになります。

変数f_vleの値が「4以外」の時
  • 1
    数字を数値で取り出す

    Mid関数を使って数字(文字)を取り出した後にVal関数で数値にしてから変数d_vleに格納します。

  • 2
    数値同時で足し算

    1つ数値を進めたいので足し算をします 1を足す為に数値にした次第です。

  • 3
    数字として戻す

    Replace関数を使って文字列を置換します。

条件Trueの時と同様に条件Falseのケースも画像にしました。IfステートメントのElseからEnd Ifの間に書かれた処理です。

文字列の一部を置き換える-2

Ifステートメントを通過した後にセルに値を戻す(代入する)様にしています。

処理は必ずどちらかの分岐を通ります。結果5年分の文字列がセルに入力されることになります。

Ifステートメントが分からない方はこちらの記事をご覧ください。

MsgBox(メッセージボックス)と一緒に使い方を勉強できるような記事構成にしています。必要に応じてご覧ください

初心者おすすめVBA-11マクロ内でMsgBoxとIFステートメントを組み合わせる

3_Mid関数

セルに書き込むタイプのMid関数と使い方は同じです。

Mid関数についてはこちらの記事で解説しています。使った事がない方はご一読ください。

Mid関数の使い方VBAで文字列を取り出す複数のかっこの中の値を1クリックで集める

4_Replace関数

Replace関数の構文は先ほど紹介しましたのでここでは上級者向けReplace関数の使い方(応用編)を紹介します。

指定した文字列だけ置換」という作業について解説します。

今回の事例では年と第1から第4四半期の情報を置換するというコードです。

この中で「2024 年第 2 四半期」2024 年第 3 四半期」に置換したい時に以下のようなコードを書いたとします。

2024 年第 2 四半期を第3四半期に置換

Debug.Print Replace(“2024 年第 2 四半期”, 2, 3)

Debug.Printなのでイミディエイトウィンドウで確認してみましょう。

「3034 年第 3 四半期」という文字列が返ってきますね。期待した文字列ではないです。

イミディエイトウインドウ:期待と異なる処理

「第 2 四半期」を「第 3 四半期」にしたいのですが文字列内の2が全て3に置き換えられています。

自分の思いとしては間違っているのですがコードとしては合っています。こんな時はどうしたら良いのでしょうか。

こんな時は「規則性がある周辺文字と一緒に置換」という方法を使います。

規則性がある周辺文字と一緒に置換

Debug.Print Replace(“2024 年第 2 四半期”, “第 2”, “第 3”)

「第」という文字とスペースを一緒にした”第 “という情報を数字を一緒に置き換えています。

この方法を使うことで2024年という文字列はそのままにして四半期の文字だけを変えることができました。

イミディエイトウインドウ:期待通りの処理

記事中段で紹介したコードもこの方法で置き換えを行っています。

コード内では変数と一緒に文字を使って置き換えを行っています。&(アンパサンド)を使うところがポイントです。

&の使い方VBAで文字列を比較Like演算子とワイルドカードで完全/部分一致を確認【一覧で表示】

アンパサンドの使い方を紹介している記事を掲載しました。この記事にはワイルドカードに関する説明も掲載されています。

ワイルドカードは文字列を扱う際には必須となる要素です。置換にも活用できる機能ですよ。

まとめ

VBAを使った文字列の置換について勉強してきました。

今回紹介した4つのコードの様にVBAでは1つの作業に対して方法が複数用意されています。

4つの中から私はVBA関数のReplace関数を提示しましたがどれかが正しいという事はないです。

基本的にはどれも正しい選択肢です。最終的にはご自身が理解しやすい方法でコーディングしてください。

使いこんでくると私と違うことに気付いて「こっちのコードの方が良い」となるかもしれません。

その一連の行為がスキルアップにつながります。まずは回数をこなしましょう。

自分でコードを書いて文字を置き換えてみることが何より一番重要です。是非トライしてくださいね。

EnjoyExcel

タイトルとURLをコピーしました