スポンサーリンク
※今回の記事は、Excel VBAのコーディングを自力でやってて、Functionくらいまでは分かるレベルの人向けです。
Excel シート名の規則
今回はマクロ(VBA)の話だけど、その前に。
Excelのシート名を付ける際には、守らなければならない規則が3つある。
(2)
:\?[]*/
上記に薄い文字で書いてあるのは、間違い(というか情報不足)です。
確かにMicrosoftの公式ページとかでは上記のように発表されているはずなんですが、実際は7つどころじゃなくて下記の赤く書いてある文字が全て使用できません。
‘
*
/
:
?
[
\
]
’
＇
＊
／
：
？
［
＼
］
￥
(3)シート名は、全角半角問わず31文字まで
さて今回取り上げたいのは、Excel VBAで何か文字列bufを生成したとして、buf文字列が上記(1)～(3)の禁則に反しないよう整形したいという場合の効率的な方法だ。
そして整形したbufを、Excelのシート名として代入・利用したい。
まず規則(1)は、bufが文字列「履歴」と完全合致した場合だけ、別の文字列に変更すれば良いだけなので、どうにでもなるから省略する。
そこで、面倒な規則(2)をクリアした後で、割と簡単な規則(3)をクリアする方法を考えたい。
禁則文字をまとめて削除したい
さて、規則(2)について。
生成した文字列bufについて、多数の禁則文字のどれかが見付かったら、それを全て削除すれば良いわけだ。
削除するということは、何もない文字・いわゆるNull文字に置き換えるということで、そういうときに使う定番がReplaceだ。
たとえばコロン文字「:」を削除すると言うなら
buf = Replace(buf, “:” , “”)
というように、「:」文字をNull文字列に置換するというやり方だ。
さて問題は、禁則文字が「:」以外にも多数あるということで、それら全部を削除置換するって処理になる。まともに書くと
|
1 2 3 4 5 6 7 |
buf=Replace(buf,":","") buf=Replace(buf,"/","") buf=Replace(buf,"?","") buf=Replace(buf,"[","") buf=Replace(buf,"]","") buf=Replace(buf,"/","") buf=Replace(buf,"*","") |
という感じのを沢山の行にわたり書く羽目になり、見苦しくなる。
これをどうスマートに書こうかというのが今回の趣旨で、シート名の禁則云々はオマケみたいなもんです。
ParamArrayで不特定多数の禁則文字を受け取る
ではこれをスマートにするにはどうすれば良いか。
まず上記の例は、1個の文字を削除replaceするのに1行消費してることでソースが肥大化してるんで、削除したい禁則文字をいっぺんに10個でも20個でもまとめて指定できるようにしたい。
そのために、次のようなFunctionを作った。
|
1 2 3 4 5 6 7 8 9 10 |
Function fncDeleteStrings(buf As String, ParamArray arrDeleteStr()) As String '文字列から指定文字を削除する Dim var As Variant fncDeleteStrings = buf For Each var In arrDeleteStr '配列に指定された文字を削除していく fncDeleteStrings = Replace$(fncDeleteStrings, var, "") Next var End Function |
このfncDeleteStringsが、複数指定した禁則文字を削除した後の文字列を返すFunctionだ。
ここで使われている
ParamArray arrDeleteStr()
がミソ。
このParamArrayが、不特定多数の引数を受け取るための配列型であり、ここに禁則文字を好きなだけ放り込めば良い。
では本題。実際にシート名に使えない禁則文字を全て削除し、文字数を31文字までに直すソースを示す。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Function fncDeleteStrings(buf As String, ParamArray arrDeleteStr()) As String '文字列から指定文字を削除する Dim var As Variant fncDeleteStrings = buf For Each var In arrDeleteStr '配列に指定された文字を削除していく fncDeleteStrings = Replace(fncDeleteStrings, var, "") Next var End Function Function fncSheetNameModify(buf As String) As String 'シート名から使えない文字を削除する fncSheetNameModify = fncDeleteStrings$(buf, "'", "’", "＇", "*", ":", "?", "\", "￥", "＊", "／", "：", "？", "[", "［", "］", "]", "＼", "/") fncSheetNameModify = Left$(fncSheetNameModify, 31) 'シート名は31文字まで End Function |
fncSheetNameModifyが、メインとなるfunctionね。
fncDeleteStringsとLeftの後に付けてる「$」は、文字列だってことを明示指定しているわけ。
文字列だと断言できる場合でないとエラーになるけど、断言できる場合なら若干の高速化が図れる。
大した効果でもないので、消しておいて構わない。
このFunctionを具体的にどうマクロで使うかって、ごく簡単な例で示すと次の例のような感じだ。
A1セルの値をシート名に代入するという簡単なマクロで、禁則チェックを入れるようにしている。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub test() Dim ws As Worksheet Dim buf As String Set ws = ActiveSheet buf = ws.Cells(1, 1).Value buf = fncSheetNameModify$(buf) 'シート名から使えない文字を削除する ws.Name = buf End Sub |
また、全てのシート名を一括で変更しまくれるマクロというのも、このFunctionを使って書いてみた。
これはガチで役立つと思うので、読んでいただけると幸いです。
スポンサーリンク
