新規挿入可能なシート名の判定
マクロ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
:\/?*[]
:\/?*[]\
シートの存在確認:存在すればシートのオブジェクトを返す
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
①~⑨

これらが区別されません。
全角半角は先のStrConvで変換していますが、丸数字の2種類は別途Functionで変換してチェックしなければなりません。
この変換の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 ・・・新着記事一覧を見る
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)
PIVOTBY関数(縦軸と横軸でグループ化して集計)|エクセル入門(2023-11-19)
GROUPBY関数(縦軸でグループ化して集計)|エクセル入門(2023-11-18)
PY関数(Pythonコードをセル内で実行)|エクセル入門(2023-11-17)
画像「セルに配置」のVBAについて(365の新機能)|VBA技術解説(2023-11-13)
スピルのゴーストの範囲を選択するVBA|ツイッター出題回答 (2023-10-31)
エクセル試験3:月間の所定労働時間|エクセル練習問題(2023-10-04)
エクセル試験2:所得税の計算|エクセル練習問題(2023-10-04)
エクセル試験1:曜日別の平均客単価|エクセル練習問題(2023-10-04)
列全体を指定する時のRangeとColumnsの違い|ツイッター出題回答 (2023-09-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.条件分岐(IF)|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.マクロとは?VBAとは?VBAでできること|VBA入門
10.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 新規挿入可能なシート名の判定
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。