Excel VBA 不特定多数の不要文字をまとめてParamArrayで削除置換するマクロ→シート名に使えない文字を削除して文字列整形

Excel マクロ、VBA

スポンサーリンク

※今回の記事は、Excel VBAのコーディングを自力でやってて、Functionくらいまでは分かるレベルの人向けです。

Excel シート名の規則

今回はマクロ(VBA)の話だけど、その前に。
Excelのシート名を付ける際には、守らなければならない規則が3つある。

Excel シート名の命名規則
(1)「履歴」というシート名は使えない(変更履歴機能でExcelが使う専用名称として予約されているから)。

(2)下記の7つの文字は使用できない。

:
\
?
[
]
*
/

上記に薄い文字で書いてあるのは、間違い(というか情報不足)です。
確かにMicrosoftの公式ページとかでは上記のように発表されているはずなんですが、実際は7つどころじゃなくて下記の赤く書いてある文字が全て使用できません。

*
/
:
?
[
\
]









(3)シート名は、全角半角問わず31文字まで

さて今回取り上げたいのは、Excel VBAで何か文字列bufを生成したとして、buf文字列が上記(1)~(3)の禁則に反しないよう整形したいという場合の効率的な方法だ。
そして整形したbufを、Excelのシート名として代入・利用したい。

まず規則(1)は、bufが文字列「履歴」と完全合致した場合だけ、別の文字列に変更すれば良いだけなので、どうにでもなるから省略する。

そこで、面倒な規則(2)をクリアした後で、割と簡単な規則(3)をクリアする方法を考えたい。

禁則文字をまとめて削除したい

さて、規則(2)について。
生成した文字列bufについて、多数の禁則文字のどれかが見付かったら、それを全て削除すれば良いわけだ。

削除するということは、何もない文字・いわゆるNull文字に置き換えるということで、そういうときに使う定番がReplaceだ。


たとえばコロン文字「:」を削除すると言うなら
buf = Replace(buf, “:” , “”)

というように、「:」文字をNull文字列に置換するというやり方だ。
さて問題は、禁則文字が「:」以外にも多数あるということで、それら全部を削除置換するって処理になる。まともに書くと

という感じのを沢山の行にわたり書く羽目になり、見苦しくなる。

これをどうスマートに書こうかというのが今回の趣旨で、シート名の禁則云々はオマケみたいなもんです。

ParamArrayで不特定多数の禁則文字を受け取る

ではこれをスマートにするにはどうすれば良いか。
まず上記の例は、1個の文字を削除replaceするのに1行消費してることでソースが肥大化してるんで、削除したい禁則文字をいっぺんに10個でも20個でもまとめて指定できるようにしたい。

そのために、次のようなFunctionを作った。

このfncDeleteStringsが、複数指定した禁則文字を削除した後の文字列を返すFunctionだ。
ここで使われている
ParamArray arrDeleteStr()
がミソ。

このParamArrayが、不特定多数の引数を受け取るための配列型であり、ここに禁則文字を好きなだけ放り込めば良い。

では本題。実際にシート名に使えない禁則文字を全て削除し、文字数を31文字までに直すソースを示す。

fncSheetNameModifyが、メインとなるfunctionね。

fncDeleteStringsとLeftの後に付けてる「$」は、文字列だってことを明示指定しているわけ。
文字列だと断言できる場合でないとエラーになるけど、断言できる場合なら若干の高速化が図れる。

大した効果でもないので、消しておいて構わない。


このFunctionを具体的にどうマクロで使うかって、ごく簡単な例で示すと次の例のような感じだ。
A1セルの値をシート名に代入するという簡単なマクロで、禁則チェックを入れるようにしている。

また、全てのシート名を一括で変更しまくれるマクロというのも、このFunctionを使って書いてみた。
これはガチで役立つと思うので、読んでいただけると幸いです。

Excel VBA 全てのシート名を一括で変更しまくれるマクロ 個人用マクロブックに組み込めるよ

2018年11月18日

スポンサーリンク