VBA Excelのコメントを検索・置換する方法

2021.04.08 2021.04.08

VBAでExcelのコメントを検索・置換する方法です。

Excelのセルに追加されたコメントは、検索はできるものの置換することができません。すべてが同じコメントなら１つ修正して他へコピペなんてことも可能ですが、一部の文字を置換したい場合には通常機能ではどうしようもありません。

リボン(ホーム)の検索と選択からコメントを選ぶと全コメントセルを選択してくれる。
Ctrl+Fの検索ボックスでコメントを選ぶと検索できる。

めんどくさがりな筆者は検索して見つけて修正なんて２回以上はやりたくありません。なのでマクロで解決しましょう。

ここでは VBAでExcelのコメントを検索・置換する方法 を紹介します。

1 単純なコメント置換
2 オートシェイプのテキストを変更する
- 2.1 検証
3 まとめ

単純なコメント置換

単純なコメント置換をするなら下記のコードを標準モジュールに貼り付けて実行してください。

Sub ReplaceComments()
  Dim Src As String: Src = "テスト"
  Dim Rep As String: Rep = "TEST"
  For Each com In Cells.SpecialCells(xlCellTypeComments)
    com.Comment.Text Text:=Replace(com.Comment.Text, Src, Rep)
  Next com
End Sub

ただ、これを実行すると全て太字になるので注意してください。

本来のExcelコメントは、作成者名とコメント本文で書式が違います。

どうもテキストを入れ替えるとそれが失われてしまうようですね。

オートシェイプのテキストを変更する

実はExcelコメントの実態はオートシェイプと同じShapeオブジェクトなのです。

つまり、オートシェイプと同様にShapeオブジェクトを操作すれば良いということになります。ShapeオブジェクトではCharactersにBold設定ができるので、置換文字列の位置が太字かどうかチェックし、そこだけを再設定すればOKです。

具体的には下記のようなコードになります。

Option Explicit

Private ReplaceCount As Long   ' 置換件数

'
' 関数名：コメント内文字列の検索と置換
' 引数  ：なし
' 戻り値：なし
'
Public Sub SearchAndReplaceOfShapeComment()

  Dim SearchText As String      ' 検索する文字列
  Dim ReplaceText As String     ' 置換後の文字

  ' 検索文字列入力用InputBoxを表示
  SearchText = InputBox("検索する文字列")

  ' 検索文字列の入力がなければ処理終了
  If SearchText = "" Then
    Exit Sub
  End If

  ' 置換文字列入力用InputBoxを表示
  If ReplaceText = "" Then
    ReplaceText = InputBox("置換後の文字列")
  End If

  ' 置換処理
  ReplaceCount = 0
  If ReplaceOfShapeComment(ActiveSheet.Shapes, SearchText, ReplaceText) Then
    MsgBox ReplaceCount & " 件を置換しました。", vbInformation
  Else
    MsgBox "置換対象が見つかりません。", vbExclamation
  End If

End Sub

'
' 関数名：コメント文字列の置換
' 引数１：Shapes コメントオブジェクト
' 引数２：SearchText 検索文字列
' 引数３：ReplaceText 置換文字列
' 戻り値：True：置換成功、False：置換対象なし
'
Private Function ReplaceOfShapeComment(ByRef Shapes As Object, ByRef SearchText As String, ByRef ReplaceText As String) As Boolean
  
  Dim Ret As Boolean        ' 処理結果
  Dim Shape  As Shape       ' コメントオブジェクト
  Dim ShapeText As String   ' コメント内の文字列
  Dim Pos As Long           ' 文字列位置
  Dim Bold As Boolean       ' 太字か？
  
  ' 初期値設定
  Ret = False
  
  ' シート内のコメントを検索
  For Each Shape In Shapes
  
    ' コメントの時
    If Shape.Type = msoComment Then
    
      ' コメント内の文字列を置換
      Do While (1)
      
        ' コメント内の文字列を取得
        ShapeText = Shape.TextFrame.Characters.Text
        
        ' コメント内の文字列から検索文字列位置を取得
        Pos = InStr(ShapeText, SearchText)
        
        ' 検索文字列が見つからない場合は処理終了
        If Pos = 0& Then
          Exit Do
        End If
        
        ' 置換先の太字設定を取得
        Bold = Shape.TextFrame.Characters(Pos, Len(SearchText)).Font.Bold
        
        ' 検索文字列を置換する
        'Shape.TextFrame.Characters.Text = Replace(ShapeText, SearchText, ReplaceText) ' ←これでは全て太字になる。
        Shape.TextFrame.Characters(Pos, Len(SearchText)).Text = ReplaceText
        
        ' 太字設定
        Shape.TextFrame.Characters(Pos, Len(ReplaceText)).Font.Bold = Bold
        
        Ret = True
        ReplaceCount = ReplaceCount + 1
        
      Loop
    End If
  Next
  
  ' 処理結果を返す
  ReplaceOfShapeComment = Ret
End Function

文字列の置換にはShape.TextFrame.Characters(開始位置, 終了位置).Textに対して置換後の文字列を入れ替えています。このままでは全て太字になってしまうので、事前に置換位置が太字かどうか保持しておきます。

太字の設定はShape.TextFrame.Characters.Font.Boldで取れます。CharactersはCharacters(開始位置, 終了位置)のようにするとその箇所の情報が取得・設定できます。