CODEWITHSUNDEEP
vbaexcelfile-io
Tawm
Tawm

Reputation: 545

VBA: Two methods of seeing if a file is open

I've got two methods which I feel should tell if a file is open or not.

Method 1 (Error: Subscript out of range):

If Not Workbooks(filename) Is Nothing Then
    Workbooks.Open (filename)
End If

Method 2:

If Not IsWorkbookOpen(filename) Then
    Workbooks.Open (filename)
End If

Where IsWorkbookOpen() is:

Private Function IsWorkbookOpen(wbname) As Boolean

Dim wBook As Workbook
Set wBook = Nothing

On Error Resume Next
Set wBook = Workbooks(wbname)

If wBook Is Nothing Then
    IsWorkbookOpen = False
    Else: IsWorkbookOpen = True
End If

End Function

Aside from On Error Resume Next, Method 1 appears to be nearly the same as Method 2.

Could anyone please explain why Method 1 gives the error it does?

Thank you.

Upvotes: 2

Views: 6492

Answers (3)

Cohan
Cohan

Reputation: 4544

VBA tries to evaluate all the parts before it evaluates the conditional statement. So if I have a variable myvar = "xyz" and try to run the following lines...

If IsNumeric(myvar) And Round(myvar, 1) = 3 Then
    'you will get an error before the IF is evaluated
End If

it will not work. VBA will evaluate IsNumeric(myvar) fine, then try to evaluate Round(myvar, 1) = 3 and get an error before it checks the entire conditional. So VBA will let you know about the error before it performs the AND operator. If VBA had short circuit evaluation, it would work fine since the first part would evaluate to false.

But the following will work

If IsNumeric(myvar) Then
    If Round(myvar, 1) = 3 Then
        'second IF statement is not touched since first IF statement evaluates to false
    End If
End If

This works because IsNumeric(myvar) evaluates to false and therefore skips the nested statement.

So the error it throws on the Workbooks(filename) will just give the error unless you tell it to resume next. So the method I use is

    On Error Resume Next
    Set wb = Workbooks(file)
    If wb Is Nothing Then
        Set wb = Application.Workbooks.Open(dir & "\" & file, ReadOnly:=True)
    End If
    On Error GoTo 0

Edited to give more detail and correctly capture that the second example will not be evaluated as well as provide a useful solution for the question at hand.

Upvotes: 1

Hauke P.
Hauke P.

Reputation: 2823

Workbooks(filename) tries to get the element with the identifier (or 'index') filename from the collection Workbooks. If the collection does not contain such an element, you'll get the "Subscript out of range" error. (So in a nutshell: This code will fail whenever the file is not open. You probably don't want this.)

However, the knowledge that such an access will fail if the file is not open, i.e. an error is raised, is being made use of in the second method. The code tries to get the element with the identifier filename from the Workbooks collection and to assign it to the variable wBook. If it fails, the value of the variable wBook will stay Nothing. If it succeeds, the variable wBook will contain a reference to the respective Workbook object.

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33145

They both give a subscript out of range error. But in Method 2 you suppress that error with On Error Resume Next. 

Sub SeeError()

    On Error Resume Next
    Debug.Print Workbooks("DoesNotExist").Name

    Debug.Print Err.Description

End Sub

This prints "Subscript Out of Range" in the Immediate Window. The On Error statement doesn't stop the error from occurring, it just handles it. Method 1 doesn't handle errors so the default error handling (stop execution and report the error) is in effect.

Upvotes: 5

Related Questions