2
If Error(Sheets(ws_str)) = True Then Exit Sub

Returns run-time error 9. I know this sheet does not exist. How do I do errorhandling in cases where the worksheet does not exist?

Community
  • 1
  • 1
  • You can use the answers given to create an **UDF** (User Defined Function) and have the worksheet as input. – danieltakeshi Oct 18 '17 at 11:31
  • Possible duplicate of [Test or check if sheet exists](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) – Indent Oct 18 '17 at 14:10

3 Answers3

2

Try the little piece of code below:

Option Explicit

Sub SheetExits()

Dim ws As Worksheet
Dim ws_str As String

ws_str = "aaa" ' for test

On Error Resume Next
Set ws = Worksheets(ws_str)
On Error GoTo 0
If ws Is Nothing Then Exit Sub    

End Sub
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
1

This will solve your problem:

Sub ErrorHandling()
On Error GoTo ExitSub
Dim ws As Worksheet

Set ws = Worksheets("NonExistingSheet") 'it will throw an error
MsgBox ("This won't be displayed")

ExitSub:
End Sub

Basically, at the beginning define, where your code should resume in case of an error. To satisfy your requirement, place the resuming point right before End Sub, so it goes directly there in case of an error.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
1
 Function SheetExists(sheetname as string) as boolean
 On error goto whoops
 dim ws as worksheet
 set ws = worksheets(sheetname)
 set ws = nothing
 sheetexists = true
 exit function
 whoops:
 sheetexists = false
 end function
Harassed Dad
  • 4,669
  • 1
  • 10
  • 12