Check if a worksheet already exists in a workbook
Before adding a new sheet if you want to check if a sheet with the same name already exists then use the following function procedure. Download the attachment try the TestIt function in it in the module named 'mod_WorksheetExists'.
Paste the following code in a general module and use it from anywhere in the workbook.
Function blnWorksheetExists(strWorksheet As String) As Boolean Dim wksWorksheet As Worksheet On Error Resume Next Set wksWorksheet = ThisWorkbook.Worksheets(strWorksheet) If Err.Number = 0 Then blnWorksheetExists = True Else blnWorksheetExists = False End If On Error GoTo 0 Set wksWorksheet = Nothing End Function
| Attachment | Size |
|---|---|
| WorksheetExists.xls | 22.5 KB |
»
- Vishesh's blog
- Login or register to post comments
- 26791 reads
A couple of things, I don't
A couple of things, I don't think the set = nothing is necessary here. you haven't created a new pointer to an interface you are using one created elsewhere, but I'm happy to be corrected on that.
I think you try to avoid using error handling as logic, it may have unintended consquences elsewhere. The following function does that same:
Function DoesWorksheetExist(WorksheetName As String, Optional wbk As Workbook) As Boolean
Dim blnRetVal As Boolean '''The function return value
blnRetVal = False '''We assume the worksheet doesn't exist
Dim wks As Worksheet
Dim wbkTarget As Workbook
If wbk Is Nothing Then
Set wbkTarget = ActiveWorkbook
Else
Set wbkTarget = wbk
End If
'''Loop through all the worksheets in the active workbook
For Each wks In wbkTarget.Worksheets
'''Text compare the worksheet's names. If we have a match exit the loop.
'''Note we do not exit the function at this point. Reducing the number of exit points
'''increases readability
If StrComp(wks.Name, WorksheetName) = 0 Then
blnRetVal = True
Exit For
End If
Next wks
DoesWorksheetExist = blnRetVal
End Function
without the on error resume next, it does this at the expense of extra overhead.
My function runs approximately 50% slower.
Code shortened further
Function blnWorksheetExists(strWorksheet As String) As Boolean
On Error Resume Next
blnWorksheetExists = Not (ThisWorkbook.Worksheets(strWorksheet) Is Nothing)
On Error GoTo 0
End Function