|
|
|
|
|
|
|
|
|
Excel
|
Function To Check If a Worksheet Exists Or Not
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2002
|
|
Submitted by:
|
Jacob Hilderbrand
|
|
Description:
|
This User Defined Function can be used to check if a worksheet exists or not.
|
|
Discussion:
|
Perhaps you want to rename a worksheet, but if the sheet exists you would get an error. This function will return a TRUE or FALSE if the worksheet exists or not. If you are not refering to a worksheet in the active workbook, you can supply the workbook name to the function as well (workbook must be open).
|
|
Code:
|
instructions for use
|
Option Explicit
Public Function DoesWorkSheetExist(WorkSheetName As String, Optional WorkBookName As String)
Dim WS As Worksheet
On Error Resume Next
If WorkBookName = vbNullString Then
Set WS = Sheets(WorkSheetName)
Else
Set WS = Workbooks(WorkBookName).Sheets(WorkSheetName)
End If
On Error GoTo 0
DoesWorkSheetExist = Not WS Is Nothing
End Function
|
|
How to use:
|
- Open Excel.
- Alt + F11 to open the VBA.
- Insert | Module.
- Paste the code there.
|
|
Test the code:
|
- In another macro add the following code:
- x = DoesWorkSheetExist("Sheet1") '{Where Sheet1 is the name of the worksheet you want to test for}. Or...
- x = DoesWorkSheetExist("Sheet1", "Book1.xls") '{Where Sheet1 is the name of the worksheet in the workbook Book1.xls you want to test for}.
|
|
Sample File:
|
No Attachment
|
|
Approved by Anne Troy
|
|
This entry has been viewed 221 times.
|
|
|