Function To Check If a Worksheet Exists Or Not

Ease of Use


Version tested with


Submitted by:

Jacob Hilderbrand


This User Defined Function can be used to check if a worksheet exists or not. 


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). 


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:

  1. Open Excel.
  2. Alt + F11 to open the VBA.
  3. Insert | Module.
  4. Paste the code there.

Test the code:

  1. In another macro add the following code:
  2. x = DoesWorkSheetExist("Sheet1") '{Where Sheet1 is the name of the worksheet you want to test for}. Or...
  3. 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 207 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express