Office TANAKA - Excel VBA Tips[シート名に設定できる文字列かどうか判定する]

任意のセルに入力された文字列を使って、その名前の新しいワークシートを作成するには、たとえば次のようにします。下記のサンプルは、セルA1に入力された文字列を名前に持つ新しいワークシートを挿入します。

Sub Sample1()
    Worksheets.Add.Name = Range("A1")
End Sub

余談ですが、新しいワークシートの名前は必ず「Sheet？」となります。「？」には任意の数が入り、それはExcelが管理しています。つまり、いきなり「"tanaka"という名前のワークシートを挿入」することはできないのです。任意の名前のワークシートを挿入するには、
　(1)新しいワークシートを挿入する
　(2)そのワークシートの名前を変更する
という二段階の処理が必要になります。上記のサンプルは

Sub Sample1()
    Dim buf As String
    buf = Range("A1")
    Worksheets.Add
    ActiveSheet.Name = buf
End Sub

と同じですが、Addメソッドが「挿入したシートを返す」という特徴を利用しています。

さて、上記のコードは完全ではありません。たとえば、セルA1が空欄だったらどうしましょう。あるいは、セルA1の文字列に、シート名に指定できない文字が含まれていたらどうでしょう。ほかにも、すでに同じ名前のシートが存在しているかもしれません。

これらの心配を、すべて判定するのなら次のようなコードになります。

Sub Sample2()
    Dim buf As String, c, ws
    buf = Range("A1")
    ''(1)空欄かどうか
    If buf = "" Then Exit Sub
    ''(2)不正な文字があるか
    For Each c In Array(":", "\", "/", "?", "*", "[", "]")
        If InStr(buf, c) > 0 Then Exit Sub
    Next c
    ''(3)文字数が31文字以内か
    If Len(buf) > 31 Then Exit Sub
    ''(4)同じ名前が存在するか
    For Each ws In Worksheets
        If ws.Name = buf Then Exit Sub
    Next ws
    ''すべてクリアした
    Worksheets.Add.Name = buf
End Sub

なんだか面倒くさいですね。こんな苦労をしなくても、発想を変えれば簡単に解決できます。セルA1に「適正な文字」が入力されているかどうかを判断するのではなく、セルA1の文字で名前の変更を試みて「エラーになるかどうか」を判定すればいいんです。

Sub Sample3()
    On Error GoTo Err1
    Worksheets.Add.Name = Range("A1")
    Exit Sub
Err1:
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub

不正な名前を設定しようとするとエラーが発生します。On Error GoTo Err1は、エラーが発生したら「Err1」にジャンプせよという命令です。先にも書きましたが、任意の名前を持つワークシートを挿入するときは、
　(1)新しいワークシートを挿入する
　(2)そのワークシートの名前を変更する
という二段階の処理になります。今回のケースでエラーになるのは、このうち(2)の部分です。(1)の処理は正常に終わっていますので、すでに新しいワークシートは挿入されていることになります。エラーが発生したら、すでに挿入してしまった新しいワークシートを削除します。そのとき「削除しますか？」というメッセージを表示させないために、DisplayAlertsプロパティにFalseを設定してからDeleteメソッドを実行しています。