Office■ 置換
勤め先でExcelマクロでいろいろ作っている人がいて、その人から「マクロで半角スペースを置換したいのだが、なぜか動作しない」という問合せを受けました。
ふむ？
よくよく聞くと、置換はRangeオブジェクトのReplaceメソッドを使っていて、それは「マクロの記録」で自動作成されたコードを使用しているらしいです。
本人曰く、マクロ記録のために実行した置換処理では動作したのに、マクロ（VBA）で実行すると置換されないということです（これは、後で嘘と発覚）。
ふーむ？
■ 実証実験
テストデータとして、下記の表を用意します。
次に、置換が出来ないという置換処理を作ります。
Sub 変換ダメ()
'半角スペースを除去する
Range("B3:B7").Replace _
What:=" ", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
End Sub
さて、これで作成した処理を実行すると、こうなりました。
先頭の２行は置換されたのですが、３行目以降が置換されません。
．．．この時点で原因の想像ができますね。
■ 原因
今度は、マクロを使わずにExcelの置換処理を使ってみます。
すると、３行目の置換で「その関数は正しくありません。」というエラーメッセージが表示されて、置換処理が終了してしまいました。
Excelはセルの先頭に@があると、関数の入力と理解します。そのため、置換結果があり得ない件数になった場合はエラーとなるようです（＠については、こちらの記事をご覧下さい：Excelの@（アットマーク）の動作を検証してみた）。
※ ＠が＝であっても、同じエラーが発生します。
さて、ここから想定できるのは、Replaceメソッドは、エラーが発生してもユーザーに通知せずに処理を打ち切るようになっているということです。
ここで、なんか話の辻褄が合わないと思って、問い合わせてきた人に聞いたところ、マクロを作る時に同じエラーが出ていたと白状しました。
．．．おい！正常に動作したって言ってただろ！
■ 対応
原因さえ分れば、対応方法はいくらでも思いつきます。
Replaceメソッドが異常終了しないという特性を利用して、For Eachを使って置換範囲内の全てのセルを一つずつ置換するようにします。
Sub 変換OK()
'変換用のRangeオブジェクト
Dim myRange As Range
'選択範囲内の全てのセルを順次処理する
For Each myRange In Range("B3:B7")
'半角スペースを除去する
myRange.Replace _
What:=" ", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Next
End Sub
この処理を実行した結果は、下記の通りです。
無事、置換が成功しました。
■ 対応の補足
他の手段として考えられるのは、事前に@（アットマーク）を別の文字（あるいは空白）に置換してやるというのも考えられます。
ただ、この方法だと、=（イコール）の置換も必要だし、セルの先頭以外に＠や＝があった時に置換されてしまうという問題があるし、置換結果でさらにエラーが発生しないとは保証できません。
他にも、書式を文字列にして見るとか、自動計算を無効にするとかも試したのですが、ダメなものはダメでした。
ってことで、素直に全セルを繰り返し処理する方式にしています。この方法で問題があるとすれば、大量のデータ（セル範囲）に対して置換を行った時の性能問題でしょうか？
っていうか、どうせ全セルを順番に置換するのなら、RangeオブジェクトのReplaceメソッドなど使わずに、Replace関数に書き換えてやっても良かったですね。
■ 後書き
この記事を読むと、サクッと解決したと思われそうですが、実際にはかなり手こずりました（まぁ、1～2時間程度ですが）。
抜粋したので簡単に見えますが、依頼者のマクロには他にもいろいろと処理が実装されている訳でして、さらに置換対象のデータが結構な件数だったので、置換処理が一部（＠***以降のセル）だけ失敗していると気付かなかったんですよ。
さらに、私自身が＠で関数が使えるってことを知らなかったりして．．．
Excel（VBA含む）は、それなりに熟練していると思っていたのですが、まだまだのようですね。
スポンサードリンク
0
0
