I have test many codes which check if a sheet exists (based on name) and if not create one. Some of them loop all sheets and some refer to sheet and if create an error means that sheet does not exist. Which is the most appropriate - orthodox - faster way achieve this task?
Currently I'm using:
Option Explicit
Sub test()
Dim ws As Worksheet
Dim SheetName As String
Dim SheetExists As Boolean
SheetName = "Test"
SheetExists = False
With ThisWorkbook
'Check if the Sheet exists
For Each ws In .Worksheets
If ws.Name = SheetName Then
SheetExists = True
Exit For
End If
Next
If SheetExists = False Then
'If the sheet dont exists, create
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = SheetName
End If
End With
End Sub
This is what I use. No need to loop. Directly try to assign to an object. If successful then it means that sheet exists :)
Function DoesSheetExists(sh As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Sheets(sh)
On Error GoTo 0
If Not ws Is Nothing Then DoesSheetExists = True
End Function
USAGE
Sub Sample()
Dim s As String: s = "Sheet1"
If DoesSheetExists(s) Then
'
'~~> Do what you want
'
Else
MsgBox "Sheet " & s & " does not exist"
End If
End Sub
Sub solution1()
If Not sheet_exists("sheetnotfound") Then
ThisWorkbook.Sheets.Add( _
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = _
"sheetnotfound"
End If
End Sub
Function sheet_exists(strSheetName As String) As Boolean
Dim w As Excel.Worksheet
On Error GoTo eHandle
Set w = ThisWorkbook.Worksheets(strSheetName)
sheet_exists = True
Exit Function
eHandle:
sheet_exists = False
End Function
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With