Excel VBA Custom Function check if worksheet exists

This tutorial explains how to use Excel VBA Custom Function to check if worksheet exists, and add new worksheet if worksheet doesn’t exist

Excel VBA Custom Function check if worksheet exists

Sometimes we want to manipulate worksheet in a workbook but we are not sure if the worksheet exists. For example, we want to add a worksheet, if it already exists, skip the adding worksheet code.

VBA code – Custom Function to check if worksheet exists

There are many Functions on the internet that do the same job. The below custom Function is the shortest possible version modified by me, I believe it should be the most efficient version.

Function wsExists(wksName As String) As Boolean
    On Error Resume Next
    wsExists = Len(Worksheets(wksName).Name)
    On Error GoTo 0
End Function

Explanation of VBA code – Custom Function to check if worksheet exists

Step 1: Worksheets(wksName).Name – Return the name of wksName if the worksheet exists, otherwise it would return an error “Run-time error ‘9’: Subscript out of range”

worksheet_exists

Step 2: Len(Worksheets(wksName).Name – Return a number which is the length of the worksheet name if worksheet exists, otherwise continue to return error

Although the above code return a number and an error, the Boolean Function can return TRUE FALSE (when Excel does not have a normal choice for TRUE and FALSE, number is regarded as TRUE, ERROR is regarded as FALSE), but you need to use On Error Resume Next statement because the error would cause the code to stop running.

Syntax – Custom Function to check if worksheet exists

wsExists(wksName)

wksName is the worksheet name in Text

This Function returns TRUE and FALSE

Example – Custom Function to check if worksheet exists

The below code adds “worksheet1” if worksheet doesn’t exist.

Sub createWS()
    If Not wsExists("worksheet1") Then
         Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets("Sheet8"))
         newWS.Name = "worksheet1"
     End If
End Sub
Function wsExists(wksName As String) As Boolean
    On Error Resume Next
    wsExists = Len(Worksheets(wksName).Name)
    On Error GoTo 0
End Function

If you plan to delete and recreate a new worksheet anyway, then

Public Sub createWS()
    On Error Resume Next 
        Application.DisplayAlerts = False
        Sheets("worksheet1").Delete
        Application.DisplayAlerts = True
    On Error GoTo 0        
        Set newWs = ActiveWorkbook.Worksheets.Add
        newWs.Name = "worksheet1"
End Sub

Outbound References

http://www.excelforum.com/excel-programming-vba-macros/488529-check-if-a-worksheet-exists.html

Leave a Reply

Your email address will not be published. Required fields are marked *