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:

  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 206 times.

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