Excelにはシェイプ内の文字列置換機能がない
Excelには、シェイプ内の文字列の置換どころか検索機能がありません。(Wordにはあった気がするけど)
困る。とても、困る。
というわけで作りました。折角なので、正規表現も使えるようにしました。
シェイプ内の文字列が改行されている場合を考慮して、~行ごとに分割してから置換処理をかけてます。
そのため正規表現での「^」「$」もちゃんと使えます。
ちなみにシェイプ内(セル内も)の改行は「\n」なのでChr(10)です。vbNewLineじゃダメだった。
.MultiLine = Trueにしているので、シェイプ内が改行を含む文字列でも改行の後の「^」「$」がちゃんと利きます。
正規表現オブジェクトに複数行オプションあるじゃん気付かなかったの恥ずかし…
シェイプ内の文字列置換自体は、こちらを参考にしました。
とても便利なコードをありがとうございます。
図形内の文字を置換するExcelマクロ - Qiita
実行イメージ
標準モジュールにコードをコピペして、ユーザー定義関数を作成します。
使用時は、セル内に数式を記述するだけ。
セルが確定すると実行され、シェイプ内の文字列が置換されます。
置換後はセル内の数式は不要なため、削除してしまって構いません。
関数名：ReplaceShapeText
引数：
- 検索パターン
- 置換パターン
- 大/小文字の扱い(省略可)(デフォルトはFalse(大/小文字を区別する))
セル内容を確定させると置換が実行されます。
後から考えれば正規表現例は^(bc)dのがよかったな…
正規表現で一致した場所のみ置換。一致すればシート状のシェイプ全部置換します。
入力したセルは正常終了していれば「0」が表示されます。不要なんで消しちゃって構いません。
第3引数をTRUEにすると、こんな感じ。
VBAコード
標準モジュールに以下のコードをコピペしてください。
別にユーザー定義関数じゃなくてもいいし、呼び出し用のリボンとかメニューとか入力用にフォームとか作ってもいいんだけど、参考記事見たときにユーザー定義関数で機能呼び出してもいいんか！って割と衝撃だったんですよ。
今までクイックアクセスツールバーとかリボンから呼び出す感じで作ってたよ…昔ユーザー定義コマンドバーを作ってた名残で…ユーザー定義関数だとシート名取得とかファイル名取得とかくらいしか作ったことなかったの…
Public Function REPLACESHAPETEXT(ByRef findStr As String, ByRef repStr As String, Optional ByRef ignoreCase As Boolean = False)
Application.ScreenUpdating = False
Dim sh As Shape: For Each sh In ActiveSheet.shapes
With sh.TextFrame2
If .HasText = msoTrue Then
.TextRange.text = ReplaceSplitNewLine(.TextRange.text, findStr, repStr, ignoreCase)
Call VBAUtils.CheckEvents
End If
End With
Next
Application.ScreenUpdating = True
End Function
Private Function ReplaceSplitNewLine(ByVal value As String, ByVal findStr As String, ByVal repStr As String, ByVal ignoreCase As Boolean) As String
With CreateObject("VBScript.RegExp")
.Global = True
.ignoreCase = ignoreCase
.MultiLine = True
.pattern = findStr
ReplaceSplitNewLine = .Replace(value, repStr)
End With
End Function
自分用のアドイン集みたいの作ってる場合は、そこにぶち込むのがいいっすね。
私も2003の時代からメンテして使い続けてる自分用アドイン集があります。
これなしでは作業がクソ遅くなる…
これを応用すると置換じゃなくてシェイプの検索もできなくはないんだけど、ヒットしたシェイプで処理を止めるとか、止めないならシェイプに色を付けてくとか、そのあたりどうしっよかなーってなったので実際には作ってない。ここまで出来てるからすぐ作れるとは思うんだけども。
そのうち多分別記事書きます。
ふと思った応用案。
顔文字とか「★」とか文中で使ってねえだろこれえ〜！って文字を適当に置換というより追加して検索にヒットしたかどうか目視になっちゃうけど確認することは可能ですね…あとで第2引数を空文字に置換すれば消えるし。
検索機能個別に作らんでも使えなくないですな。
おまけ(ユーザ定義関数の機能制限)
同じような処理で置換対象のセル範囲を引数で渡したら、ユーザー定義関数からセルの正規表現を使った処理できるんじゃない！？と思ったのですが…ユーザー定義関数の制限に引っかかってダメでしたね。
別のセルの値の変更。
ユーザー定義関数以外からの呼出しにしないとダメっぽい。
ちえーっ。InputBoxかユーザーフォームから引数渡すようなSub作ろうかな。