Wauvel

Excel like a finance pro.

← All VBA snippets

Check a sheet exists before you touch it

Stop "subscript out of range" — the most common runtime error in VBA, and it's always this.

1What it does

`Worksheets("Output")` fails with error 9 the moment someone renames the tab, adds a space, or sends you the file with that sheet deleted. There's no `Exists` method, so the idiom is a small helper function that tries to grab the sheet with errors switched off for exactly one line, then reports whether it got one. Once you have it, the macro can create the sheet instead of failing. The same page covers the other half of the problem: deleting a sheet pops a confirmation dialog that stops an unattended run until somebody clicks it.

2The code

' Returns True if the sheet is there. Put this in a normal module.
Function SheetExists(sheetName As String, Optional wb As Workbook) As Boolean
    Dim ws As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook

    On Error Resume Next          ' the ONE line allowed to fail
    Set ws = wb.Worksheets(sheetName)
    On Error GoTo 0               ' switched straight back off

    SheetExists = Not ws Is Nothing
End Function

Sub RefreshOutput()
    Dim ws As Worksheet

    If SheetExists("Output") Then
        Set ws = ThisWorkbook.Worksheets("Output")
        ws.Cells.ClearContents
    Else
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "Output"
    End If

    ws.Range("A1").Value = "Ready"
End Sub

Sub DeleteQuietly(sheetName As String)
    If Not SheetExists(sheetName) Then Exit Sub

    Application.DisplayAlerts = False      ' no "are you sure" prompt
    ThisWorkbook.Worksheets(sheetName).Delete
    Application.DisplayAlerts = True       ' ALWAYS put it back
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Rebuild an output tab whether or not it already exists.
  • Skip a month's sheet that hasn't been created yet instead of failing.
  • Delete a temporary sheet on an unattended run.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Workbook tabs

P&LBalance SheetOutput v2

5Pitfalls

`On Error Resume Next` left on for the rest of the procedure, so every later error is silent too.

Fix: `On Error GoTo 0` on the line straight after the one you were protecting — exactly as above.

DisplayAlerts left False because the macro failed between switching it off and back on.

Fix: Combine this with the error-handling pattern and restore it under the Cleanup label.

Sheet names compare case-insensitively but not space-insensitively — "Output " is a different sheet.

Fix: `Trim()` the name, and be suspicious of names that came from a cell.

A sheet that exists but is hidden still passes this check, then the code fails on activating it.

Fix: Don't activate. Work through the `ws` object directly — you rarely need a sheet to be visible.

6No-code alternatives

  • On Error Resume Next around the whole macro — Hides the error and every other one after it. You get no failure and no result.
  • Looping every sheet to compare names — Works and is explicit, but it's more code for the same answer.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.