【VBA】テキストボックス(Shape)内の文字列を取得する方法

テキストボックス内の情報が取り出せなくて困っていませんか?
困るのも無理ありません
セルと比べてテキストボックスをVBAで扱うのは難易度が高いですから
そんな困りごとを解決するため、簡単に扱えるプログラムを紹介しようと思います。
この記事ではテキストボックス内の文字列を取得するプログラムをお見せします。
テキストボックスの文字列を取得
Sub GetTextBox()
'全てのテキストボックスの文字列⇒Debug.Print
Dim oneShp As Shape
For Each oneShp In ThisWorkbook.ActiveSheet.Shapes
If oneShp.Type = msoTextBox Then'テキストボックスのみ処理
Debug.Print oneShp.TextFrame2.TextRange.Text
End If
Next
End Sub
このプログラムではアクティブシート上にあるテキストボックスの文字列を順番にDebug.Printします。
テキストボックスの文字列を取得しているのは、
oneShp.TextFrame2.TextRange.Textです。
例えば、シート上に順番に書き出していきたいのであればカウンタ変数(row_i)を作って、
cells(row_i,1)=oneShp.TextFrame2.TextRange.Text
row_i=row_i+1
などとアレンジしてみてください。
oneShp.TextFrame2.TextRange.Textの構造は?
この行ではShapeオブジェクトからTextFrame2オブジェクト、TextRangeオブジェクトを取り出しています。
TextFrame2オブジェクト
このオブジェクトには、レイアウト枠の配置や向きを制御するプロパティやメソッドだけでなく、レイアウト枠のテキストが含まれます。 TextFrame2 オブジェクトを取得するには、TextFrame2 プロパティを使用します。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.textframe2
これはテキストのレイアウトのオブジェクトです。
図形などのオブジェクトにテキストを埋め込む場合だと、余白調整などを行うプロパティを保有しています。
TextRangeオブジェクト
図形に付加されたテキスト、およびテキストを処理するプロパティやメソッドが含まれます。
https://docs.microsoft.com/ja-jp/office/vba/api/powerpoint.textrange
これはテキスト自体を処理するためのオブジェクトです。
テキストを処理するためのプロパティ/メソッド(例:Copy/Cut/Delete/Length)を保有しています。
今回はこのオブジェクトのTextプロパティを使用して、文字列を取得しています。
テキストボックスをフォント付きでアウトプットする方法
ここからはテキストボックスをフォントとともにアウトプットする方法を紹介します。

画像のようにシート上に存在するテキストボックス内のテキストをフォント含めてシート上に書き出していきます。
Sub OutTextBox()
'テキストボックスの中身を同じ書式でシート上にアウトプット
Dim ws As Worksheet: Set ws = ActiveSheet
Dim oneShp As Shape
Dim row_i As Long: row_i = 1
For Each oneShp In ws.Shapes
If oneShp.Type = msoTextBox Then 'テキストボックスのみ処理
Call OutText(oneShp, ws.Cells(row_i, 2))
row_i = row_i + 1
End If
Next
End Sub
Private Sub OutText(shp As Shape, outCell As Range)
'shpオブジェクトの中身を同じ書式でシート上にアウトプット
'テキストボックス名/テキストを貼付け
outCell.Offset(, -1) = shp.Name
outCell = shp.TextFrame2.TextRange.Text
Dim tmpTR As TextRange2
Dim wsFont As Font
Dim fromLeft As Long: fromLeft = 1
Dim run_i As Long
'書式を反映(文字色/サイズ/太字/下線)
For run_i = 1 To shp.TextFrame2.TextRange.Runs.count
Set tmpTR = shp.TextFrame2.TextRange.Runs(run_i)
Set wsFont = outCell.Characters(fromLeft, tmpTR.Length).Font
With tmpTR.Characters.Font
wsFont.Color = .Fill.ForeColor
wsFont.Size = .Size
If .Bold = msoTrue Then wsFont.Bold = True
If .UnderlineStyle = msoUnderlineSingleLine Then wsFont.Underline = True
End With
fromLeft = fromLeft + tmpTR.Length
Next
End Sub
Private SubのOutTextがメインの処理を行っています。
outCell = shp.TextFrame2.TextRange.Text
この部分で先ほどと同じようにテキストボックス内のテキストをすべて書き出しています。
テキストをすべて書き出した後に、書式を反映させていっています。
ポイントはTextRangeのRunsプロパティ
このプログラムのポイントはTextRangeのRunsプロパティです。
TextRangeはテキストボックスのテキスト自体を処理するためのオブジェクトでしたね
RunsプロパティはTextRangeを同じ書式のブロックに切り分ける役割を果たします。

今回の場合は、あい、う、え、おの4種類がブロックに分割されます。
返り値はTextRangeオブジェクトで、この分割されたブロックをTextRangeオブジェクトとして返します。
For run_i = 1 To shp.TextFrame2.TextRange.Runs.count
Set tmpTR = shp.TextFrame2.TextRange.Runs(run_i)
Set wsFont = outCell.Characters(fromLeft, tmpTR.Length).Font
For文の後に、Runsプロパティを用いて分割されたTextRangeオブジェクトをSetしています。
そして、その次の行で分割されたTextRangeオブジェクトに対応するセルの文字列のフォントをSetしています。
(Charactersプロパティが分からない場合は公式リファレンスを読んでみてください)
残りの処理はTextRangeのフォントとセルの文字列のフォントを一致させているだけです。
ちょっと理解が難しいプログラムなので、Forループの部分をステップ実行して観察してみてください