【VBAで文字列を一括置換】Excelのセルとオートシェイプ図形

あなたは日々のお仕事で

『Excelの図形の文字をチョット置換したいだけ』

なんだけど…

『置換する数が膨大でウンザリ！』

といった経験はないでしょうか？

こんな時は、プログラムで一気に置換してササッと仕事を終わりたいですよね！

ササッと終わる手段として、職場でインストールされてるExcelのVBAが使えます。

『VBAでExcelの文章や図形テキストを一括置換する方法は？』

こんな疑問に答えます。

SE歴25年のオッサンが解説するぞい！

ＳＥおっさん

VBAでExcel・オートシェイプ図形のテキスト文字列を一括置換

Excelに書かれた文字は、大きく分けて２パターンあります。

1. セル内に書かれた文字
2. オートシェイプに書かれた文字
   （オートシェイプ＝図形）

文字を置換する２つのパターンです。

この２パターンの解決策を組み合わせて、Excelのセルや図形テキストの文字を一括置換します。

※Replace関数とReplaceメソッドは、名前は似ていますが全く別物です。

ＳＥおっさん

メソッドとは…
オブジェクトが持つ関数のコトじゃ

メソッドはオブジェクトを介して
呼び出されるということだね！

たろちゃん

ＳＥおっさん

そうじゃ！
関数はオブジェクトを介さず、直接呼び出すぞい

また、

Excelファイル操作するメソッドも併せて使用します。

こちらもテキストファイル操作のステートメントとは全く別物です。

Excel専用のファイル操作命令があるってコトだね

たろちゃん

Excelのオートシェイプ図形のテキスト文字列を置換

Excelに入力する文字はセルだけではありません。

オートシェイプの図形でも文字を頻繁に入力します。

図形の文字はExcelの機能で一括置換できません。
（セルの文字置換は、１ファイルだけならExcelの機能で置換可能）

図形の文字を置換したいけど、置換箇所が大量の場合は『超メンドクサイ』ですよね。

図形の文字を一括置換するには、VBAを使用すれば解決できます。

VBAの一括置換は『プログラム設計書で図形のテキスト文字をチョット置換したいだけど、置換対象が大量にある』というシーンでも有効です。

VBAでExcelの図形（オートシェイプ）のテキスト文字列を置換する流れです。

1. 図形テキストより文字を取得
2. 取得した文字列をReplace関数で置換
3. 置換した文字列を図形テキストに設定

まず、図形テキスト文字の置換を行うためには、図形テキスト文字を取得する必要があります。

ＳＥおっさん

順番に説明するぞい♪

図形テキストより文字を取得する

VBAではExcelの図形（オートシェイプ）は、Shape オブジェクトです。

図形テキストより文字を取得するには…

Shape オブジェクトのTextFrame2プロパティのTextRangeプロパティのTextプロパティを使用します。

わっ、長っ
呪文みたい…

たろちゃん

ＳＥおっさん

１つ１つは簡単じゃよ
いまから詳しく説明するぞい

まず、Shapeオブジェクトから説明します。

Shape オブジェクト

Shape オブジェクトは、図形などの描画オブジェクトを表します。
（描画オブジェクト…オートシェイプ／フリーフォーム／OLE オブジェクト、図など）

Shape オブジェクトは、 Shapes コレクションのメンバーです。

Shapes コレクションには、ブックのすべての図形が含まれます。

つまり…

Excelブックにある図形の数だけ、Shapes コレクションにShapeオブジェクトが格納されている

ということです。

難しいな…

たろちゃん

ＳＥおっさん

大丈夫じゃ！
コレクションは後で詳しく説明するぞい

また、

ShapeオブジェクトのTextframe2プロパティでTextframe2オブジェクトを取得できます。

オブジェクトの中にオブジェクトを持てる
ってコトだね！

たろちゃん

ＳＥおっさん

そうじゃ！
オブジェクトのプロパティやメソッドで
オブジェクトを取得できるから階層化される訳じゃ

TextFrame2 オブジェクト

TextFrame2 オブジェクトは、Shape／ShapeRange／chartformat オブジェクトのレイアウト枠を表します。

このオブジェクトには、レイアウト枠の配置およびアンカーを制御するプロパティやメソッドだけでなく、レイアウト枠のテキストが含まれています。

TextFrame2 オブジェクトのHasTextプロパティは、指定したテキストフレームにテキストがあるかどうか（ある：True／なし：False）を返します。

矢印の図形はテキストを設定できません。

矢印のShapeオブジェクトからTextプロパティを参照しようとすると、エラーとなります。

エラーを回避するためには、図形（シェイプ）がテキスト設定可能か判断する必要があるので、HasTextプロパティで確認する訳ですね。

TextFrame2オブジェクトのTextRangeプロパティでTextRange2オブジェクトを取得できます。

ＳＥおっさん

TextRangeプロパティでTextRange2オブジェクトを取得じゃ
ここだけは、ややこしいのぅ…

TextRange2オブジェクト

TextRange2オブジェクトのTextプロパティを使って図形テキスト文字の取得・設定を行います。

たろちゃん

確かに１つずつ見れば解る！
落ち着いてプログラミングだね♪

オートシェイプ図形のテキスト文字列をReplace関数で置換

Shapeオブジェクトより取得した文字列を、Replace関数で置換します。

詳細は下記を参照ください。

【VBAで文字列を一括置換】複数のテキストファイルを１回で変更

あなたは日々のお仕事で 『テキストファイルの中身をチョット置換したいだけ』 なんだけど… 『置換するファイル数が膨大でウンザリ！』 といった経験はないでしょうか.....

置換した文字列をExcelのオートシェイプ図形のテキストに設定

置換した文字列をExcelのオートシェイプ図形のテキストに設定します。

図形テキストに文字列を設定するには…

Shape オブジェクトのTextFrame2プロパティのTextRangeプロパティのTextプロパティを使用します。

オートシェイプのテキスト文字列の取得と同じだね！

たろちゃん

次は、Excelのセルに入力された文字列を置換するReplaceメソッドについて説明いたします。

Excelのセル文字をReplaceメソッドで置換

Excelのセルに記載された文字を置換するには、RangeオブジェクトのReplaceメソッドを使用します。

Replaceメソッドの基本形です。

ReplaceメソッドはRangeオブジェクトの持ち物ってことだね

たろちゃん

Replaceメソッドの引数です。

任意引数は引数３（LookAt）と引数５（MatchCase）が重要です。
引数４と引数６～８は、ほとんど使用しません。

VBAのReplace関数とExcelのReplaceメソッドを使用して、Excel内の文字列と図形テキストを置換して出力する方法をご紹介します。

Replaceメソッドの使用例

Replaceメソッドの使用例です。

Worksheets("Sheet1").UsedRange.Replace What:="おはよう", Replacement:="こんにちは", LookAt:=xlPart

Excetシート「Sheet1」に記載された全ての”おはよう”を”こんにちは”に置換します。

UsedRangeプロパティは、シート内のセルに入力された全データのセル範囲を取得します。

つまり、Excetシート「Sheet1」にある全セルのデータが対象になる訳です。

ＳＥおっさん

ここまでが、
セル文字と図形文字を置換する方法じゃ
わかったかのぅ

※VBAでExcelファイルの操作ができる方
ここまでの情報を組み合わせて一括置換することが出来ると思います。
次の章は飛ばして一括置換するサンプルコードへ

※VBAでExcelファイルの操作を知らない方
いきなり一括置換するサンプルコードを見ても
『わけわからん！』
かもなので、これからご説明いたします。

VBAでExcelファイルを操作するメソッド

一括置換に必要なExcelファイルを操作するメソッドを説明します。

Openメソッド（Excelファイルを開く）

WorkboksコレクションのOpenメソッドを使用して、Excelブックを開きます。

つまり、Excelファイルを開くということは…
Workbooksコレクションに、開いたExcelをWorkbookオブジェクトとして追加することです。

ExcelはWorkbooksコレクションで
管理されてるんだね！

たろちゃん

WorkbooksコレクションのOpenメソッドの基本形です。

Openメソッドの引数です。

Openメソッドの使用例

以下のOpenメソッドでCドライブに存在するSample_BookというExcelファイルを開きます。

Workbooks.Open "C:Sample_Book.xlsx"

SaveAsメソッド（名前を付けて保存する）

WorkbookオブジェクトのSaveAsメソッドで、Excelファイルを保存します。

SaveAsメソッドの基本形です。

SaveAsメソッドの引数です。

Closeメソッド（Excelファイルを閉じる）

WorkbookオブジェクトのCloseメソッドで、開いているExcelファイルを閉じます。

Closeメソッドの基本形です。

Excelファイル操作の使用例も欲しいナ…

たろちゃん

ＳＥおっさん

使用例を紹介するぞい

VBAでExcelファイルを操作するメソッドの使用例

VBAでExcelファイルを操作するメソッドの使用例です。

Dim wb As Workbook

'Excelブックを開く
Workbooks.Open Filename:="C:Sample_Book1.xlsx"
Set wb = ActiveWorkbook

'出力先へExcelブックを保存
wb.SaveAs Filename:="C:Sample_Book2.xlsx"

'Excelブックを閉じる
wb.Close

VBAでExcelのセルと図形文字を一括置換！サンプルコードとフローチャート

VBAでExcel文章や図形テキストを一括置換して保存する関数（サンプルソース）とフローチャートです。

ソースコードを見ても理解できない時、フローチャートが理解を助けてくれます。

プログラミング初心者にフローチャートは必要【実証済のメリット５つ】

【記号は５つ】フローチャートの書き方【初心者向け簡単マニュアル】

VBAでExcel文章や図形テキストを一括置換して保存するフローチャート

VBAでExcel文章や図形テキストを一括置換して保存するフローチャートです。

ＳＥおっさん

フローチャートで流れは掴めたかのぅ

VBAでExcel文章や図形テキストを一括置換して保存する関数

VBAでExcel文章や図形テキストを一括置換して保存するサンプルコードです。

関数名は（XLS_IN_OUT）としています。

＃サンプルソースはGithubにあります。

'入力ブックから出力ブックへ文字列を置換して出力
'引数１：入力Excelブックパス
'引数２：出力Excelブックパス
'引数３：置換前文字列
'引数４：置換後文字列
Private Sub XLS_IN_OUT(ByVal path_i As String, ByVal path_o As String, search_str As String, replace_str As String)

	Dim wb As Workbook
	Dim ws_io As Worksheet
	Dim spShape As Shape

	'Excelブックを開く
	Workbooks.Open Filename:=path_i
	Set wb = ActiveWorkbook

	'開いたブック内の全シート分ループ
	For Each ws_io In wb.Worksheets
		'ワークシートの文字置換（xlPart：セルの部分一致）
		ws_io.UsedRange.Replace What:=search_str, Replacement:=replace_str, LookAt:=xlPart

		'図形（シェイプ）の文字置換
		For Each spShape In ws_io.Shapes
			'テキストを持つ図形か判断
			If spShape.TextFrame2.HasText Then
			    spShape.TextFrame2.TextRange2.Text = Replace(spShape.TextFrame.TextRange2.Text, search_str, replace_str)
			end if
		Next spShape
	Next ws_io

	'出力先へExcelブックを保存
	wb.SaveAs Filename:=path_o

	'Excelブックを閉じる
	wb.Close

End Sub

図形（シェイプ）の文字置換の前に、TextFrame2.HasTextプロパティでテキストを持つ図形か確認してから、テキスト置換を行っています。
⇒確認しなければ、矢印などのテキストを持たない図形でエラーが発生するため

上記の自作関数（XLS_IN_OUT）の呼び出し例です。

call XLS_IN_OUT("D:sample_input.xlsx", "D:sample_output.xlsx", "おはよう", "こんにちは")

sample_input.xlsxに記載された全ての”おはよう”を”こんにちは”に置換してsample_output.xlsxに出力します。

ＳＥおっさん

ここまでが、VBAでExcel内の文章や図形を一括置換して保存する方法じゃよ！
わかったかのぅ

VBAでExcelのセルと図形文字を一括置換！まとめ

いかがでしたでしょうか？

VBAを利用したExcelのセルと図形文字を一括置換する方法をご紹介しました。

ポイントを少し振り返ってみましょう。

VBAの置換で、速く仕事が終われば良いですね！

＃サンプルソースはGithubにあります。

ＳＥおっさん

ここまで読んでくれてありがとう！

「記事を読んでもわからないトコがある」「内容が変だよ」
という時は、お気軽にコメントください♪

「もっとＳＥおっさんに詳しく聞きたい。何かお願いしたい！」
という時は、ココナラまで。メッセージもお気軽に♪

LINEでのお問合わせも受付中！
LINE公式アカウント

メッセージをお待ちしています！

Follow @koisyo_ok