VBA技術解説
新規挿入可能なシート名の判定

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2020-02-06

新規挿入可能なシート名の判定


マクロVBAで、
シートの存在確認およびシートとして使える文字列かどうかの判定は、
厳密に行おうとするとかなり大変になってきます。


この件についてTwitterでやり取りがあり、それらを踏まえた上でのVBAコードを参考として公開しておきます。
その時のツイートでのやり取りは、こちら

シート名として使える文字列か判定



Function PermitName(ByVal argName As String) As Boolean
  PermitName = False
  If Len(argName) > 31 Then Exit Function
  If argName = "履歴" Then Exit Function
  If Left(argName, 1) = "'" Then Exit Function
  
  Dim 禁止文字 As Variant
  禁止文字 = Array(vbNullChar, _
           ":", "\", "/", "?", "*", "[", "]", _
           ":", "\", "/", "?", "*", "[", "]", "\")
  Dim i As Long
  For i = LBound(禁止文字) To UBound(禁止文字)
    If InStr(argName, 禁止文字(i)) > 0 Then
      Exit Function
    End If
  Next
  PermitName = True
End Function

シート名の制限として、
・31文字以内
・「履歴」は予約語として使えない
・先頭「'」シングルクォーテーションは使えない
・以下の文字は使用不可
 vbNullChar
 :\/?*[]
 :\/?*[]\

バックスラッシュが全角だけなので、VBAではStrConvを使わずに半角全角の文字を直接記述しています。

シートの存在確認:存在すればシートのオブジェクトを返す

Function SheetExists(ByVal argName As String, _
           Optional ByVal wb As Workbook = Nothing) As Object
  Dim sht As Object
  If wb Is Nothing Then Set wb = ThisWorkbook
  For Each sht In wb.Sheets
    If StrConv(LCase(sht.Name), vbNarrow) = StrConv(LCase(argName), vbNarrow) Then
      Set SheetExists = sht
      Exit Function
    End If
  Next
  Set SheetExists = Nothing
End Function

シート名は、全角・半角、大文字・小文字を区別しない、
つまり、全角・半角違いや、大文字・小文字違いの同名シートは作成できない。
存在判定だけなら、戻り型はBooleanにしたいところだが、
多くの場合、存在した場合はそのシートを使う事になるので、そのシートを戻りとしています。
従って、存在しない場合は、Nothingを戻しています。
また、グラフシート等の場合もあり得るので、データ型としてはObjectにしています。

On Error を使った簡単な存在確認

この場合は全角半角違いが正しく判定できません。



Function SheetExists(ByVal argName As String, _
           Optional ByVal wb As Workbook = Nothing) As Object
  If wb Is Nothing Then Set wb = ThisWorkbook
  On Error Resume Next
  Set SheetExists = wb.Sheets(argName)
End Function

"Sheet1"のシートが存在している場合、
"Sheet1"(全角)は、「インデックスが有効範囲にありません。」のエラーになり、存在しない判定になります。
しかし、"Sheet1"が存在する場合、"Sheet1"のシートは作成できません。
このような全角違いの問題を考慮する必要が無い場合であれば、On Errorでの判定は簡単で良いでしょう。

後日追加

シート名では、
0,1~9
0,1~9
①~⑨
VBA マクロ シート名
これらが区別されません。
全角半角は先のStrConvで変換していますが、丸数字の2種類は別途Functionで変換してチェックしなければなりません。
この変換のVBA自体は難しくありませんが、「?,?~?」これらはshift-jisにない為、VBAで固定文字として各国衙できません。
ChrW関数で文字コードを指定する必要があります。
さすがに、ここまで必要になる事はあまり無いとは思いますが、一応サンプルVBAを掲載しておきます。

Function ChangeName(ByVal argName As String) As String
  Dim i As Long, j As Long
  Dim RoundNumbers1
  RoundNumbers1 = Array("0", "①", "②", "③", "④", "⑤", "⑥", "⑦", "⑧", "⑨")
  Dim RoundNumbers2(9)
  RoundNumbers2(0) = ChrW(9450)
  For i = 1 To 9
    RoundNumbers2(i) = ChrW(9460 + i)
  Next
  For i = 0 To 9
    argName = Replace(argName, RoundNumbers1(i), i)
    argName = Replace(argName, RoundNumbers2(i), i)
  Next
  ChangeName = StrConv(LCase(argName), vbNarrow)
End Function

必要であれば、先のSheetExistsの、
If StrConv(LCase(sht.Name), vbNarrow) = StrConv(LCase(argName), vbNarrow) Then
これを↓
If ChangeName(sht.Name) = ChangeName(argName) Then
こちらに変更してお使いください。

シートの挿入:存在すればシートのオブジェクトを返す

上記の
PermitName
SheetExists
この二つのFunctionの使い方のサンプルをかねて、
シートを新規挿入する場合のサンプルになります。

Sub sample()
  Dim sht As Object
  Set sht = SheetsAdd("te'st")
  If sht Is Nothing Then
    MsgBox "指定のシート名は使用できません"
  End If
End Sub

Function SheetsAdd(ByVal argName As String, _
          Optional ByVal wb As Workbook = Nothing) As Object
  Set SheetsAdd = Nothing
  If Not PermitName(argName) Then Exit Function
  
  If wb Is Nothing Then Set wb = ThisWorkbook
  Set SheetsAdd = SheetExists(argName, wb)
  If Not SheetsAdd Is Nothing Then Exit Function
  
  Set SheetsAdd = wb.Worksheets.Add
  SheetsAdd.Name = argName
End Function

SheetsAddについては、
引数にシートの挿入位置や、既存シートの場合は削除後に新規挿入するかどうか等々、
もう少し機能アップしたFunctionにするのも良いでしょう。



同じテーマ「マクロVBA技術解説」の記事

Applicationを省略できるApplicationのメソッド・プロパティ一覧
PowerQueryの強力な機能をVBAから利用する方法
ShapesとDrawingObjectsの相違点と使い方
新規挿入可能なシート名の判定
VBAにおける配列やコレクションの起点について
VBAのマルチステートメント(複数のステートメントを同じ行に)
クリップボードに2次元配列を作成してシートに貼り付ける
ユーザー定義型の制限とクラスとの使い分け
シングルクォートの削除とコピー(PrefixCharacter)
空文字列の扱い方と処理速度について(""とvbNullString)
VBAにおける変数のメモリアドレスについて


新着記事NEW ・・・新着記事一覧を見る

多階層フォルダ(ディレクトリ)の作成|VBAサンプル集(7月31日)
VBAのインデントについて|VBA技術解説(7月16日)
「VBA Match関数の限界」についての誤解|エクセル雑感(7月15日)
省略可能なVariant引数の参照不可をラップ関数で利用|VBA技術解説(7月12日)
100桁の正の整数値の足し算|エクセル雑感(7月9日)
LSetとユーザー定義型のコピー(100桁の足し算)|VBA技術解説(7月9日)
Variant仮引数のByRefとByValの挙動違い|エクセル雑感(7月5日)
Variant仮引数にRange.Valueを配列で渡す方法|エクセル雑感(7月5日)
Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ