Error handling with if...then VBA

Question

Is there any way to use if...then for error handling (without On Error... or GoTo!!!!)?

I have the below code, but it's stuck at the if statement.

Sub test()

            If IsError(Workbooks.Open("C:\Users\Desktop\Test\journals.xlsx")) = True Then

                'Do something

            End If

  End Sub

Thanks!

Possible duplicate of [Good Patterns For VBA Error Handling](http://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling) — vacip, Jan 03 '17 at 11:18
Error handling in VBA is done with the On Error statement. Period. In some cases, you can find problem specific solutions. Maybe you need to change the title, because it is misleading; this is not error handling, this is checking to see if a file exists. (Which question also has 10000 answers here at SO...) — vacip, Jan 03 '17 at 12:07
So now it is a duplicate of this: http://stackoverflow.com/questions/11573914/check-if-the-file-exists-using-vba — vacip, Jan 03 '17 at 12:08
I could have used other statements within IsError(). I just wanted to know if we can replace On Error with If Then somehow. — Vinnie, Jan 03 '17 at 12:12
The quick answer is no, there is no general replacement for On Error statements (unfortunately). IsError is an Excel function that is checking error values among cell values. See here too: http://stackoverflow.com/questions/18562252/if-iserror-in-vba — vacip, Jan 03 '17 at 12:14

score 4 · Accepted Answer · answered Jan 03 '17 at 11:18

4

you could use Dir() function

If Dir("C:\Users\Desktop\Test\journals.xlsx") = "" Then
    'do something
Else
    Workbooks.Open "C:\Users\Desktop\Test\journals.xlsx"
End If

answered Jan 03 '17 at 11:18

user3598756

28,893
4
18
28

score 3 · Answer 2 · edited Jan 03 '17 at 11:37

3

You can turn off error handling and then check if an error number was generated from the attempt at opening the workbook.

Dim wb As Workbook

On Error Resume Next

Set wb = Workbooks.Open("C:\Users\Desktop\Test\journals.xlsx")

If Err.Number > 0 Then

    '' there was an error with opening the workbook

End If

On Error GoTo 0

Edit 1: Since you are already doing so nicely, directly setting it to a wb object, why not use it's capabilities ?

If wb Is Nothing Then

edited Jan 03 '17 at 11:37

Shai Rado

33,032
6
29
51

answered Jan 03 '17 at 11:17

luke_t

2,935
4
22
38

1

Allow me to offer an alternative, – Shai Rado Jan 03 '17 at 11:35

score 2 · Answer 3 · answered Jan 03 '17 at 11:24

The simplest answer is no, it's expected that you'll use On Error in some fashion, either:

On error resume next
Workbooks.Open("C:\Users\Desktop\Test\journals.xlsx")
If Err.Number <> 0 then
' the workbook is not at that location
Err.Clear
On Error Goto 0
End If

or in a traditional error handler:

errhandler:

If Err.Number <> 0 then
    If Err.Number = 1004 Then
        ' the workbook is not at that location, do something about it, then resume next
    End If
End If

However, you can use the FileSystemObject to test if a file exists:

Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")
fileExists = fso.fileExists("C:\Users\Desktop\Test\journals.xlsx")

Error handling with if...then VBA

3 Answers3