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”

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()
    Dim ws, newWS As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If Not wsExists("worksheet1") Then
            Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets("Sheet8"))
            newWS.Name = "worksheet1"
        End If
    Next ws
End Sub
Function wsExists(wksName As String) As Boolean
    On Error Resume Next
    wsExists = Len(Worksheets(wksName).Name)
    On Error GoTo 0
End Function

Outbound References

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

Excel VBA Custom Function check if worksheet exists