文字列からシート名に使えない文字を消去する

ワークシート名には条件があり、この条件に違反すると、

入力されたシートまたはグラフの名前が正しくありません。次の点を確認して修正してください。

? 入力文字が 31 文字以内であること
? 次の使用できない文字が含まれていないこと: コロン (:)、円記号 (\)、スラッシュ (/)、疑問符 (?)、アスタリスク (*)、左角かっこ ([)、右角かっこ (])
? 名前が空白でないこと

というエラーメッセージで怒られます。

↓のコードのように、セル値などを使って、
機械的にシート名をつけようとしたときに発生しがちなエラーですね。

Worksheets(1).Name = Cells(R, 2)

このうち文字数の方は簡単で、

Worksheets(1).Name = Left(Cells(R, 2), 31)

このようにLeft関数で31文字以降をカットしてしまえばOKなのですが、
禁則文字の方はちょっと面倒です。
　

Dim シート名 As String
シート名 = Cells(R, 2)
シート名 = Replace(シート名, ":", "")
シート名 = Replace(シート名, "\", "")
　…

と、該当の文字を1個ずつ消してあげる必要があります。

これをいちいち書くのは面倒なので、以下のようなコードを書くと、
禁則文字の消去コードをスッキリ書くことができます。

Dim シート名 As String
シート名 = Cells(R, 2)

Dim Arr禁則文字リスト
Arr禁則文字リスト = Split("',’,＇,*,:,?,\,￥,＊,／,：,？,[,［,］,],＼,/,<,>", ",")
Dim 禁則文字
For Each 禁則文字 In Arr禁則文字リスト
    シート名 = Replace(シート名, 禁則文字, "")
Next

Worksheets(1).Name = Left(シート名, 31)

このコードで、変数に入れたシート名から禁則文字を全て消すことができます。

禁則文字が1文字ずつ格納された配列を作って、
その要素すべてにReplaceを繰り返し実行するコードです。

配列の作成は、

Arr禁則文字リスト = Array("'", "’", "＇", "*", …

とArray関数でストレートに作ってもいいのですが、
「"」を大量に打つのが面倒なときは

Arr禁則文字リスト = Split("',’,＇,*,:,?,\,￥,＊,／,：,？,[,［,］,],＼,/,<,>", ",")

と、カンマごとにテキストを区切って配列を作ってくれる、Split関数さんにやってもらうと楽ができます。

余談ですが、この配列を見てわかる通り、禁則文字はエラーメッセージに書いてあるものがすべてではありません。

公式のドキュメントなんだし、ちゃんと全部書いてほしいですよね(´∀｀;)

汎用関数にする

今回作った

Dim Arr禁則文字リスト
Arr禁則文字リスト = Split("',’,＇,*,:,?,\,￥,＊,／,：,？,[,［,］,],＼,/,<,>", ",")
Dim 禁則文字
For Each 禁則文字 In Arr禁則文字リスト
    シート名 = Replace(シート名, 禁則文字, "")
Next

このコードは、もう定数しか出てきていないようなコードですので、
完璧にコピペで使いまわせます。

どうせコピペでいいなら、わざわざこんな6行もメインコードに使わないよう、
汎用関数にしてしまいましょう。

Function シート名禁則文字を削除(ByVal 元テキスト As String) As String
    Dim Arr禁則文字リスト
    Arr禁則文字リスト = Split("',’,＇,*,:,?,\,￥,＊,／,：,？,[,［,］,],＼,/,<,>", ",")
    Dim 禁則文字
    For Each 禁則文字 In Arr禁則文字リスト
        元テキスト = Replace(元テキスト, 禁則文字, "")
    Next
    シート名禁則文字を削除 = 元テキスト
End Function

' 実行例
Worksheets(1).Name = シート名禁則文字を削除(Cells(R, 2))

実行例を見てわかるように、メインコードが短くわかりやすくなりましたね。

メインコード上では一時変数がなくなっているので、管理する変数が減るのもなかなかありがたいメリットです。

特にメインコードから配列を消せたため、配列慣れしていない人にとっては、
コードを読む際の負担をだいぶ下げることができるはずです。

プログラミング全般に言えることですが、コードの長さとコードの重要性は全く比例しません。

今回のような、メインの処理からは外れた、割とどうでもいい部分でも、以外と行数がかかったりしますからね。

こういった、メインの処理とは関係の薄い、Nameプロパティさん固有の問題みたいなことを「無関係の下位問題」と呼ぶそうです。

無関係の下位問題は、こうやって関数に隠して、臭いものに蓋をしてしまいましょう。
　

Worksheets(1).Name = シート名禁則文字を削除(Cells(R, 2))

と1行にしてしまえば、どうでもいい処理に脳のリソースを食わなくて済む分、重要な処理に集中できるようになりますよ。