VBAの全てがここにある!

VBA Replace関数について ~関数の解説と使用例~

Shirota

開発エンジニア/AccessVBA/ExcelVBA/ボールド歴1年

VBAで文字列を置換するときには、[Replace関数] を使うと簡単に実現することができます。ここでは、引数の使い方と違いについてサンプルコードを使って詳しく説明していきます。


1.Replace関数について

1-1.Replace関数は文字列の一部分を置換する「関数」です

Replace関数とはVBAにあらかじめ用意されている「関数」です。Replace関数は、文字列の一部分を置換したい場合に使用します。文字列の中にある検索文字列の“A”を“B”に置き換え、その結果を返します。

例えば住所データにある電話番号、“03(9999)9999”“03-9999-9999”“(03)9999-9999” などのバラバラの標記をすべてハイフンに統一したい場合はReplace関数を使用します。また、住所データにある住所、合併により「浦和市」を「さいたま市」に置換したい場合はReplace関数を使用します。

1-2.Replace関数の構文について

【書式】

Replace (文字列 , 検索文字列 , 置換文字列 [, 開始位置] [, 置換回数] [, 比較方法])
戻り値は文字列(string)形式

引数については次のとおりです。

引数 説明 省略値
文字列  (必須) もととなる文字列を指定する
検索文字列(必須) 検索する文字列を指定する
置換文字列(必須) 置換する文字列を指定する
開始位置(省略可能) 開始する位置(省略すると1となる) 1
置換回数(省略可能) 置換する回数(省略するとすべてを置換する) -1
比較方法(省略可能) 比較方法を示す数値。値については、「比較方法 定数」を参照してください。 0

比較方法については次のとおりです。

比較方法 定数 説明
vbUseCompareOption Option Compare ステートメントの設定を使用して比較する -1
vbBinaryCompare バイナリモードで比較する。
半角と全角、ひらがなとカタカナ、アルファベットのUPPERlower区別します
0
vbTextCompare テキストモードで比較する。
半角と全角、ひらがなとカタカナ、アルファベットのUPPERlower区別しない
1
vbDatabaseCompare
(Accessのみ)
DBに格納されている設定で比較する 2

vbUseCompareOption」は、モジュール宣言セクションに指定した値で比較します。

1-3.モジュール宣言セクションに比較方法を設定する

Option Compare ステートメントを使用して比較方法を設定することができます。比較方法はExcelは「Option Compare Binary」、「Option Compare Text」の2つ。Accessは「Option Compare Database」をプラスして3つです。モジュールの先頭、モジュールの宣言セクションにいずれかを記述します。

モジュール作成の際の初期値は ExcelAccessでは異なりますので注意が必要です。

【ExcelVBAの場合】

excelの場合

【AccessVBAの場合】

Accessの場合


2.Replace関数の使用例

2-1.動作確認する場合はイミディエイトウィンドウを利用する

コード実行中の変数の値をDebugオブジェクトのPrintメソッドを使ってイミディエイトウィンドウに出力させることができます。

利用するには実行する前にあらかじめイミディエイトウィンドウを開いておきます。([表示]メニュー→[イミディエイトウィンドウ]を選択します)コードウィンドウ内に「Sample2_1」のコードを貼り付けてコード内で実行する(F5)とイミディエイトウィンドウへ次のように出力されます。

イミディエイト

動作確認する場合は、Debug.Printを使いイミディエイトウィンドウを利用します。

2-2.Replace関数の基本的な使用例

2-2-1.置換する時にアルファベットのUPPERとlowerを区別する

バイナリモード(アルファベットのUPPERlower区別する

9行目解説、文字列“accessVBA” にある “a” “*” に置換します。1文字目からすべて置換します。比較方法が省略のため Option Compare ステートメントの「Option Compare Binary」となり、アルファベットのUPPERlower区別するとなります。実行する(F5)とイミディエイトウィンドウに *ccessVBAが出力されます。

2-2-2.置換する時にアルファベットのUPPERとlowerを区別しない

テキストモード(アルファベットのUPPERlower関係なく置換する

9行目解説、文字列“accessVBA” にある “a” “*” に置換します。1文字目からすべてを置換します。比較方法は Option Compare ステートメントの「Option Compare Binary」より、vbTextCompare の指定が優先され、アルファベットのUPPERlower区別しないとなります。実行する(F5)とイミディエイトウィンドウに *ccessVB*が出力されます。
※2行目を「Option Compare Text」へ修正もよいのですが、機能変更や追加の場合はモジュールレベル全体に影響されますので、Replace関数の比較方法へ指定しました

2-3.Replace関数の引数を使った使用例

2-3-1.置換開始位置を指定する

Replace  (文字列 , 検索文字列 , 置換文字列 [, 開始位置] [, 置換回数] [, 比較方法])

6行目解説、文字列accessVBA” にある “c” “*” に置換します。3文字目から取り出しを開始し すべてを置換します。比較方法はアルファベットのUPPERlower区別しないになります。実行する(F5)とイミディエイトウィンドウに *essVBAが出力されます。開始位置を 2にすると **essVBAが出力されます。

引数「開始位置」は、開始位置から置換するイメージでしたが、ここから取り出し、置換開始するという動きになります。個人的には「取り出し開始位置」という名前の方が適しています。mid関数の引数と同じ働きになります。

2-3-2.置換回数を指定する

Replace  (文字列 , 検索文字列 , 置換文字列 [, 開始位置] [, 置換回数] [, 比較方法])

6行目解説、文字列accessVBA” にある “a” “*” に置換します。1文字目から取り出しを開始し 1回のみ置換します。比較方法はアルファベットのUPPERlower区別しないとなります。実行する(F5)とイミディエイトウィンドウに *ccessVBAが出力されます。

8行目解説、置換回数を ゼロにすると accessVBAをそのまま返します。開始位置を指定し置換回数を ゼロにするとmid関数です。

10行目解説、置換回数を(-1)にすると *ccessVB*が出力し、すべて置換されます。回数を 2回、3回…と指定しても *ccessVB*が出力されます。置換回数を超える回数、例えば100回と指定しても *ccessVB*が出力されます。こちらはイメージ通りの動きです。

2-4.Replace関数の戻り値を確認する

戻り値は文字列(string)形式です。

2-4-1.長さゼロの文字列を指定した場合の戻り値を確認する

下記解説では長さゼロの文字列の引数位置を下線で表し、見やすくします。

Replace  (文字列 , 検索文字列 , 置換文字列 [, 開始位置] [, 置換回数] [, 比較方法])

9行目解説、長さゼロの文字列の場合の戻り値はどのようになるのか。実行する(F5)“” となります。今回、わかりやすくするためIf文で、””の場合長さゼロの文字列ですで表します。

Replace  (文字列 , 検索文字列 , 置換文字列 [, 開始位置] [, 置換回数] [, 比較方法])

13行目解説、ならば、文字列“”の中の“”“b”に置換できるか。実行する(F5)“” となります。置換することはできませんでした。文字列が“”の場合は戻り値は“”のようです。

Replace  (文字列 , 検索文字列 , 置換文字列 [, 開始位置] [, 置換回数] [, 比較方法])

19行目解説、検索文字列が長さゼロの文字列の場合戻り値は?。実行する(F5)とaccessVBAをそのまま返します。

Replace  (文字列 , 検索文字列 , 置換文字列 [, 開始位置] [, 置換回数] [, 比較方法])

22行目解説、置換文字列が長さゼロの文字列の場合戻り値はどのようになるのか。実行する(F5)とccessVBAとなります。指定した検索文字列を取り除くイメージになります。

まとめ

行数 文字列 検索文字列 置換文字列 戻り値
9 ”” a b 長さゼロの文字列(“”)
13 ”” “” b 長さゼロの文字列(“”)
19 accessVBA “” b 文字列accessVBAをそのまま返す
22 accessVBA a “” 文字列にある検索文字列”a”を””に置換し ccessVBAを返す

2-4-2.開始位置や置換回数にゼロや大きな値を指定した場合の戻り値を確認する

10行目解説、開始位置を“accessVBA”の文字列の長さ9とする。実行する(F5)と Aとなります。開始位置を…7,8,9 として実行すると、戻り値の文字数が1文字ずつ減っていくため、次の13行目の戻り値が””となることが想像できます。

13行目解説、開始位置を10にする。実行する(F5)“” となります。今回もわかりやすくIf文で、””の場合長さゼロの文字列ですで表します。10行目を確認するとこの戻り値に納得いきます。

17行目解説、開始位置のデータ型を確認するため、Long型の最大値プラス1を指定する。すると実行時エラー、オーバーフローとなります。21474836487とすると実行します。

21行目解説、置換回数の初期値は-1.それでは-2にするとどうなるのか。実行する(F5)とこちらも実行時エラー、オーバーフローとなります。

24行目解説、置換回数をゼロはどうなるのか。実行する(F5)と accessVBAをそのまま返します。

27行目解説、実行時エラー、オーバーフローとなります。21474836487とすると実行します。

まとめ

行数 文字列 検索文字列 置換文字列 開始位置 置換回数 戻り値
accessVBA a * 7 VBA
accessVBA a * 8 BA
10 accessVBA a * 9 A
13 accessVBA a * 10 (“”)長さゼロの文字列
17 accessVBA a * 2147483648 実行時エラー
accessVBA a * 2147483647 (“”)長さゼロの文字列
21 accessVBA a * 1 -2 実行時エラー
24 accessVBA a * 1 0 文字列accessVBAをそのまま返す
27 accessVBA a * 1 2147483648 実行時エラー

2-5.Replace関数を使ったVBA資格試験の出題を予想する

[Replace関数][Instr関数]です。VBA資格試験に両方出題されます。似たような意味の引数、かつ引数の順番が異なるため混乱を誘います。

  • Replace  (文字列 , 検索文字列 , 置換文字列 [, 開始位置] [, 置換回数] [, 比較方法])
  • Instr ([開始位置 ,]文字列 , 検索文字列 [, 比較方法])

5行目にある数字の 2 1 は開始位置なのか置換回数なのか、しっかりと理解していないと混乱します。

5行目解説、文字列“192.168.191.1” にある “1” “*” に置換します。2文字目から取り出しを開始し 1回置換します。比較方法はアルファベットのUPPERlower区別しないとなります。実行する(F5)とイミディエイトウィンドウに 92.*68.191.1が出力されます。

7行目解説、[Instr関数]です。文字列“192.168.191.1” にある “1” の見つかった位置を返します。但し2文字目から開始し、比較方法はアルファベットのUPPERlower区別しないとなります。実行する(F5)とイミディエイトウィンドウに 5が出力されます。

10行目と12行目は比較方法、バイナリモードとテキストモードの戻り値の違いの見直しになります。


3.さいごに

[Replace関数]を使用の場合は、引数 比較方法により、「半角と全角、ひらがなとカタカナ、アルファベットのUPPERlower区別する、しない」を指定できます。そして、比較方法はモジュールの宣言セクションを参照する場合がありますので注意が必要です。

最後に、VBEのコードを記述する際には、わかりやすい流れとすることがもっとも重要となります。

見た目も、インデントや、コメントを利用した覚書をしっかり行います。そして、エラーが仮に発生しても、容易にメンテナンスが可能なコードの記述を常に心がけていただきたいと思います。

私たちは、全てのエンジニアに市場価値を高め自身の望む理想のキャリアを歩んでいただきたいと考えています。もし、今あなたが転職を検討しているのであればこちらの記事をご一読ください。理想のキャリアを実現するためのヒントが見つかるはずです。

8/28(水)開催決定!
【テックジム×ENGINEER.CLUB】ゼロからはじめるPythonプログラミング入門講座

プログラミングは初めてだけどPythonから始めてみたいという方のために、無料のハンズオン開発講座をテックジム×ENGINEER.CLUBで共同開催することになりました。開催日時は以下の通りです。

  • 8月28日(水)19時〜21時 東京開催

本講座で学んでいただく「TechGYM方式」とは、基礎知識なしでも座学なしでプログラミングに専念できるように設計されたプログラミングのカリキュラムメソッドです。「まるで魔法にかかったようにプログラミンスキルが習得できる」と評判の本講座をぜひ一度体験してみてください。

『技術力』と『人間力』を高め市場価値の高いエンジニアを目指しませんか?

私たちは「技術力」だけでなく「人間力」の向上をもって遙かに高い水準の成果を出し、関わる全ての人々に感動を与え続ける集団でありたいと考えています。

高い水準で仕事を進めていただくためにも、弊社では次のような環境を用意しています。

  • 定年までIT業界で働くためのスキル(技術力、人間力)が身につく支援
  • 「給与が上がらない」を解消する6ヶ月に1度の明確な人事評価制度
  • 平均残業時間17時間!毎週の稼動確認を徹底しているから実現できる働きやすい環境

現在、株式会社ボールドでは「キャリア採用」のエントリーを受付中です。

まずは以下のボタンより弊社の紹介をご覧いただき、あなたの望むキャリアビジョンをエントリーフォームより詳しくお聞かせください。