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()
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
