ワークブック内のシート検索・新規作成
主な機能
- シートの、シート名での検索
- 先頭一致、完全一致対応
- 呼び出し元でWorksheetオブジェクト変数を渡す必要あり
引数に関して
- (ByRef/String)sheetName: 検索したいシート名(先頭一致可
- (ByRef/Worksheet)sheetObj: 後で利用したいWorksheetオブジェクト
- (Optional/Boolean)likeSearch: 先頭一致検索フラグ(指定無しなら完全一致)
- (Optional/Boolean)makeNewSheet: シートが無い時に作成するフラグ(指定無しなら作る)
- (Optional/Workbook)wkBook: 対象ワークブック(指定無しならアクティブなWorkbook)
FindSheet.bas
Public Function FindSheet(ByRef sheetName As String, ByRef sheetObj As Worksheet, _
Optional ByVal likeSearch As Boolean = False, Optional ByVal makeNewSheet As Boolean = True, _
Optional wkBook As Workbook = Nothing) As Boolean
Dim ws As Worksheet
Dim wb As Workbook
FindSheet = False
DoEvents
Set wb = IIf(wkBook Is Nothing, ActiveWorkbook, wkBook)
For Each ws In wb.Worksheets
If likeSearch = True And ws.Name Like sheetName & "*" Then
sheetName = ws.Name
FindSheet = True
ElseIf ws.Name = sheetName Then
sheetName = ws.Name
FindSheet = True
End If
If FindSheet = True Then
Set sheetObj = Worksheets(sheetName)
Exit Function
End If
Next ws
If makeNewSheet = True Then
Set sheetObj = Worksheets.Add
sheetObj.Name = sheetName
FindSheet = True
End If
End Function