Excel

Test for existence of a WorkSheet

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

MWE

Description:

xlSheetExists tests for the existence of either a worksheet or a chartsheet in any open workbook 

Discussion:

Many Excel procedures operate on worksheets (or chartsheets) other than the active sheet. Such actions require that the procedure explicitly define the target worksheet in some way. If the defining action refers to a sheet that does not exist, an error is generated. The procedure can test for the error, but it is better to test for the existence of the worksheet first. xlSheetExists will test for the existence of a worksheet or chartsheet. There are two passed arguments: the name of the sheet to be ?tested? is REQUIRED; the name of the workbook is OPTIONAL. If the workbook name is not supplied, xlSheetExists assumes the target workbook to be the active workbook. If the workbook name is supplied, xlSheetExists assumes that the workbook is open. The sample demonstrates both uses. Neither argument is case sensitive. 

Code:

instructions for use 

			
Option Explicit 
 
Sub Test_xlSheetExists() 
     
     '   Demonstration:  demonstrates the use of xlSheetExists; prompts user for workbook
     '                   name and worksheet name, and displays return from xlSheetExists
     '                   (0, 1, 2)
     
    Dim xlRtn As Integer 
    Dim xlBookName As String, xlSheetName As String, MsgTitle As String 
     
    MsgTitle = "Demo of xlSheetExists" 
     '
     '           prompt user for workbook name
     '
    xlBookName = InputBox("enter name of workbook to be tested." & vbCrLf & _ 
    "[leave empty for active workbook]" & vbCrLf & _ 
    "[name is not case sensitive]", MsgTitle) 
     '
     '           prompt user for sheet name
     '
    xlSheetName = InputBox("enter name of sheet to be tested." & vbCrLf & _ 
    "[name is not case sensitive]", MsgTitle) 
    If xlSheetName = "" Then Exit Sub 
     '
     '           test xlBookName.xlSheetName
     '
    xlRtn = xlSheetExists(xlSheetName, xlBookName) 
     '
     '           display book name, sheet name and return from xlSheetExists
     '
    If xlBookName = "" Then xlBookName = ActiveWorkbook.Name 
     
    Select Case xlRtn 
    Case 0 
        MsgBox "chart or sheet name entered = " & xlSheetName & vbCrLf & _ 
        "xlBookName entered (or implied) = " & xlBookName & vbCrLf & _ 
        xlSheetName & " does not exist", _ 
        vbInformation & vbOKOnly, MsgTitle 
    Case 1 
        MsgBox "chart or sheet name entered = " & xlSheetName & vbCrLf & _ 
        "xlBookName entered (or implied) = " & xlBookName & vbCrLf & _ 
        xlSheetName & " is a worksheet", _ 
        vbInformation & vbOKOnly, MsgTitle 
    Case 2 
        MsgBox "chart or sheet name entered = " & xlSheetName & vbCrLf & _ 
        "xlBookName entered (or implied) = " & xlBookName & vbCrLf & _ 
        xlSheetName & " is a chartsheet", _ 
        vbInformation & vbOKOnly, MsgTitle 
    End Select 
     
End Sub 
 
Function xlSheetExists(SheetName As String, Optional WorkBookName As String) As Integer 
     '       Function:   tests if SheetName is the name of any type of sheet in
     '                   the target workbook.  Function value of proc on return:
     '           0   not the name of a recognized sheet type
     '           1   traditional worksheet
     '           2   chart sheet
     '
     
    Dim xlobj As Object 
     '
     '           test for WorkBookName, if null, use ActiveWorkBook name
     '
    If WorkBookName = vbNullString Then WorkBookName = ActiveWorkbook.Name 
     '
     '           test for worksheet
     '
    On Error Resume Next 
    Set xlobj = Workbooks(WorkBookName).Worksheets(SheetName) 
    If Err = 0 Then ' is work sheet
        xlSheetExists = 1 
        Exit Function 
    End If 
     '
     '           test for chart sheet
     '
    On Error Resume Next 
    Set xlobj = Workbooks(WorkBookName).Charts(SheetName) 
    If Err = 0 Then ' is chart sheet
        xlSheetExists = 2 
        Exit Function 
    End If 
     '
     '           neither chart nor work sheet, set function value
     '           to zero
     '
    xlSheetExists = 0 
     
End Function

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file.
 

Test the code:

  1. The attached file contains xlSheetExists, a simple testing macro that prompts the user for the workbook name and a worksheet name to tested, several worksheets and one chartsheet. To test the code:
  2. Open the sample file
  3. Also open any other spreadsheet if testing for the existence of a worksheet or chartsheet in another open workbook is to be done
  4. With the sample spreadsheet active, click on the yellow box OR go to Tools | Macro | Macros (or Alt-F8) and double-click on Test_xlSheetExists. You will be prompted for the workbook name (no entry tells Test_xlSheetExists that the active workbook should be used) and the worksheet name.
  5. (N.B. xlSheetExists is a function to be called by a higher level procedure, further testing will depend on how the function is used).
 

Sample File:

xlSheetExists.zip 12.22KB 

Approved by mdmackillop


This entry has been viewed 213 times.

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