If sheet exist, don't add another one

Question

If the sheet "Data" does not exist, the code works perfectly, if it does exist however I get the error "Name already exists, try a different name". I've simply fixed this with an On Error GoTo ErrorHandler, the problem however is that after the code runs and the macro triggers the errorhandler msgbox , it still creates a new sheet regardless (with the names 'Sheet1,2,3,...').

Snippet:

Sub AddWorkSheet()
    Dim wb As Workbook, shtDest As Worksheet
    On Error GoTo ErrorHandler
    Sheets.Add.Name = ("Data")
    Set shtDest = Sheets("Data")

ErrorHandler:
    MsgBox ("Something went wrong."), vbCritical

End Sub

I'd like the code to simply return the message box and not create any additional sheets if the sheet "Data" already exists.

Possible duplicate of [Test or check if sheet exists](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) — Pᴇʜ, Nov 07 '18 at 09:35

score 1 · Answer 1 · answered Nov 07 '18 at 09:48

Before adding a sheet, check if it exists:

Function ShtExist(ShtName As String) As Boolean
On Error Resume Next
    ShtExist = Len(ThisWorkbook.Sheets(ShtName).Name) > 0
On Error GoTo 0
End Function

Sub AddWorkSheet()

    Dim wb As Workbook, shtDest As Worksheet
    If ShtExist("Data") Then
    MsgBox ("Something went wrong."), vbCritical
    Else
    Sheets.Add.Name = ("Data")
    Set shtDest = Sheets("Data")
    End If

End Sub

score 1 · Answer 2 · answered Nov 07 '18 at 10:15

When you try to assign a non-exist worksheet to a variable, VBA will return error 9. From my POV, I think it's useful to trap that error and then create the worksheet if needed.

So I would use:

Sub AddWorkSheet()
Dim wb As Workbook, shtDest As Worksheet

On Error GoTo ErrorHandler
Set shtDest = Sheets("Data")


'<--rest of your code-->
'
'
'
'
'
'
''<--rest of your code-->

Set shtDest = Nothing
Set wb = Nothing

Exit Sub


ErrorHandler:
If Err.Number = 9 Then
    'must create worksheet DATA
    Sheets.Add.Name = ("Data")
    Set shtDest = Sheets("Data")
    Resume Next
Else
    'different error. MSgbox with new error
    MsgBox Err.Description, vbCritical, Err.Number
    Stop
End If
End Sub

Please, note that right before the ErrorHanlder part, I added Exit Sub to avoid this part of code being executed every time the sub runs.

Unfortunately this doesn't seem to work though. I still get the same error (1004, via the else function in the errorhandler), but right after that another numbered sheet gets created. — G. Koen, Nov 09 '18 at 08:16
You get error 1004 in line `MsgBox Err.Description, vbCritical, Err.Number`? — Foxfire And Burns And Burns, Nov 09 '18 at 09:43

Egalth · Answer 3 · 2018-11-07T16:04:42.000

0

(EDIT: A previous version of this answer produced the wrong result, details in comments.)

This should work. Note the use of On Error Resume Next combined with On Error GoTo 0 instead of On Error GoTo ErrorHandler. (GoTo statements are commonly associated with so-called "spaghetti code", so it could be a good habit to avoid them when possible.)

Sub AddWorkSheet()
    Dim wb As Workbook, ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets("Data")
    If Err.Number <> 9 Then ' 9 means subscript out of range
        MsgBox ("Sheet already exists."), vbCritical
    Else
        Sheets.Add After:=Worksheets(Worksheets.Count)
        Sheets(Worksheets.Count).Name = "data"
    End If
    On Error GoTo 0
End Sub

In your original code, you could add an Exit Sub statement before the error handler; without it ErrorHandler will always be called. (You might want to have a look at the documentation.).

edited Nov 07 '18 at 16:04

answered Nov 07 '18 at 09:39

Egalth

962
9
22

1

I hardly recommend to read: [What's the deference between “end” and “exit sub” in VBA?](https://stackoverflow.com/questions/36491908/whats-the-deference-between-end-and-exit-sub-in-vba). `End` will kill your whole VBA execution. • And your code will still add a sheet even if sheet `data` exists. – Pᴇʜ Nov 07 '18 at 09:47
Indeed, that was sloppy of me, I've edited accordingly. Thanks for the correction. (As for `End` vs. `Exit Sub`, I'm perfctly aware of the difference, that's irrelevant after the edit, but the choice should obviously be made depending on context.) – Egalth Nov 07 '18 at 10:19
1

It will fail if eg a chart sheet named `Data` exists because you check only for existing worksheets. Note that `Sheets <> Worksheets`. `Sheets` can contain all type of sheets like `Charts`, `Worksheets`, etc. – Pᴇʜ Nov 07 '18 at 10:32
Thanks again, good point. Always learning. I'll leave the post as is for now. – Egalth Nov 07 '18 at 10:35

If sheet exist, don't add another one

3 Answers3