How To Check If A Worksheet Exists Using VBA

2017-02-22

VBA

There may come a time when you need to know if a sheet in a workbook exists either during VBA code execution or as a result within the workbook. You may be creating and deleting sheets with your VBA code and will need to test if a sheet exists before it’s created/deleted to avoid run-time errors. You may also have functions in your workbook that depend on sheets you’re creating or deleting and you need to check if they exist.

A User Defined Function To Check If A Sheet Exists Within The Current Workbook

 

 

This is a simple VBA function that will return true if the current workbook contains a sheet with the exact name passed through the function and returns false otherwise. This function is not case sensitive so Sheet1 and SHEET1 are considered to be the same (sheet names in Excel are not case sensitive). Here the VBA is formatted as a user defined function.

 

Function WorksheetExists(SheetName As String) As Boolean
    
Dim TempSheetName As String

TempSheetName = UCase(SheetName)

WorksheetExists = False
    
For Each Sheet In Worksheets
    If TempSheetName = UCase(Sheet.Name) Then
        WorksheetExists = True
        Exit Function
    End If
Next Sheet

End Function

 

With this code we can use =WorksheetExists(B3) to test any text string to see if it exists as a sheet name in the current workbook.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

1 Comment

  1. PEter Mole

    This didn’t work for me but I got it to run by adding;

    1. Dim ws = worksheet
    2. Change all references from ‘Sheet’ to ws

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This