wrote :: 2006.01.17
シート名に設定できる文字列かどうか判定する
任意のセルに入力された文字列を使って、その名前の新しいワークシートを作成するには、たとえば次のようにします。下記のサンプルは、セル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メソッドを実行しています。
なお、Worksheets.Add.Name = Range("A1") で、Worksheets.Add 部分がエラーになる可能性はほとんどありません。もっとも、理屈っぽい"VBA研究家"の方々は「可能性はある」と、現実的ではないケースを例にして揚げ足を取るかもしれません。そんなときは、Err1 の中でErr.DescriptionやErr.Numberを調べればいいんです。ちなみに、シート名の変更に失敗したときのErr.Number(エラーコード)は1004です。
Copyright(C) Office TANAKA 1995-2008. All rights reserved.