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

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日:2019-09-26

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


マクロ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にしています。

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

上記の
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 ・・・新着記事一覧を見る

SQL関数と演算子|SQL入門(12月1日)
データの取得:集約集計、並べ替え(DISTINKT,GROUP BY,ORDER BY)|SQL入門(11月30日)
データの取得:条件指定(SELECT,WHERE)|SQL入門(11月29日)
データの挿入:バルクインサート|SQL入門(11月28日)
データの挿入(INSERT)と全削除|SQL入門(11月26日)
テーブル名変更と列追加(ALTER TABLE)とテーブル自動作成|SQL入門(11月25日)
テーブルの作成/削除(CREATE TABLE,DROP TABLE)|SQL入門(11月24日)
データベースに接続/切断|SQL入門(11月23日)
SQLiteのインストール|SQL入門(11月22日)
SQL入門:VBAでデータベースを使う|エクセルの神髄(11月22日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


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



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