CODEWITHSUNDEEP
excelvba
Scott Severance
Scott Severance

Reputation: 953

Auto-open Excel workbook, but only if it isn't already open

I have a series of Excel workbooks that depend on a separate workbook for some of their data. So, I need the data workbook to open automatically when a main workbook is opened. To keep things simple for my non-technical end users, I'm keeping the data workbook hidden. However, I need to be able to work on both workbooks simultaneously. So, I tried to set a flag (links_opened) to specify whether the data workbook was already opened when the main workbook was opened. For the record, this is my first time messing with VBA, and I'm finding it really odd.

Here's the relevant portion of my code:

Option Explicit

Dim w As Workbooks
Dim links_opened As Boolean
links_opened = False ' This is where VBA reports an error

Function IsWorkBookOpen(FileName As String)
    ' Code here tests whether the workbook is already open
    ' Omitted for brevity
End Function

Private Sub Workbook_Open()
    Dim fn As String
    fn = ThisWorkbook.Path & "\Work Instructions\Links.xlsx"
    If !IsWorkBookOpen(fn) Then
        Set w = Workbooks
        Application.ScreenUpdating = False
        w.Open FileName:=fn, UpdateLinks:=False, ReadOnly:=True
        ActiveWindow.Visible = False
        ThisWorkbook.Activate
        Application.ScreenUpdating = True
        links_opened = True
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If links_opened Then
        w.Item(2).Saved = True
    End If
End Sub

When I try to run this code, VBA says, indicating the line I marked above:

Compile error:

Invalid outside procedure

I understand that VBA for some reason doesn't allow variables to be set at the top level. But I don't know any other way to do this. It works fine in the other languages I use regularly.

How can I set a flag to track whether the data file has been opened by my code or manually? Or is there a better way to do this?

Upvotes: 1

Views: 609

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

links_opened = False ' This is where VBA reports an error

That's an assignment operation, which is an executable statement. In VBA, non-declarative executable statements are illegal outside of a procedure scope. Besides, a Boolean variable is automatically initialized to False, so the assignment would be no-op anyway.

If !IsWorkBookOpen(fn)

This will be the next compile error; ! does exist in VBA, but it's either a type hint or a bang operator, neither of which have anything to do with negating a Boolean. You want to use the Not keyword for this:

If Not IsWorkbookOpen(fn)

And then have the IsWorkbookOpen function return a Boolean:

Private Function IsWorkBookOpen(ByVal FileName As String) As Boolean
    On Error Resume Next
    Dim wb As Workbook
    Set wb = Application.Workbooks(FileName) ' throws error 9 if non-existing
    Err.Clear
    On Error GoTo 0
    IsWorkbookOpen = Not wb Is Nothing
End Function

Upvotes: 4

Related Questions