Use VBA to determine if an Excel spreadsheet is locked

I am using the following function to determine if an Excel Worksheet is locked for editing. The spreadsheet is saved on Google Drive and is updated routinely by another user. The function works most of the time part, but if I run the function and it responds indicating the sheet is locked (usually when Google Drive is uploading the file) then it will continue reporting that the sheet is locked until I manually open the spreadsheet with Excel and save it (without making changes). Clearly something is not resetting. Can anyone help me identify what I need to do to make it run reliably?

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long
    On Error Resume Next
    ff = FreeFile()
    Open FileName For Binary Access Read Write Lock Read Write As #1
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
        Case 0:     IsWorkBookOpen = False
        Case 70:    IsWorkBookOpen = True
        Case 75:    IsWorkBookOpen = True 'readonly
        Case Else: Error ErrNo
    End Select
End Function
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:

The "On Error Goto 0" statement reset the err object, regardless an error occured or not.
Instead of checking if an error occured mixed with the function logic, it is better to separate with an error handler:
Public Function IsWorkBookOpen(FileName As String) As Boolean
On Error Goto Error
	 Dim ff As Long
	 ff = FreeFile()
	 Open FileName For Binary Access Read Write Lock Read Write As ff
	 Close ff
		'// no error occured, function return false
	 IsWorkBookOpen = False
Exit Function
Error:		'// Error handler start here
	Select Case Err.Number
	Case 70, 75
		IsWorkBookOpen = True
	Case Else
		err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext
	End Select
 End Function 

Open in new window

Couple more mistakes I noticed:
- You declare and initialize a file number, but never use it to open the file, then close the file number. Duno what the behavior is in this case, either undefined or always successfull (thus generating no error, and your function returning false).
- There are no semi-colons in select-case statements
- You don't need to copy the error number in a separate variable.
- If nothing can handle the error, better raise (or escalate) it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob4077Author Commented:
Hmmm. I didn't do a very good job of such a small function,  did I?

Thanks for highlighting all the errors
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.