Macro Only Finishes If Run Though VBA Application or Macro List

Question

I have a 3 master sheets that I frequently want open (May, June, and July). I have multiple other macros that grab data from these master sheets.

I've created a macro (OpenFiles) that opens them. I want to be able to call OpenFiles at the start of a macro if I know I will be referencing one of these master sheets.

Sub OpenFiles calls another sub(zzGetIt) that checks if the workbook is already open, otherwise it opens it. It works perfectly if I run it from the VBA application, or by choosing it in the macro list in Excel. But if I use a hotkey to call it, or if it is called through another macro, it exits out of all subs after opening a single file (and ends on that workbook instead of activating my original active workbook).

It will open a workbook if one of them is missing, but it will never open more than one (if, say I have only 1/3 workbooks open- 2 should open). And the only scenario where the macro will continue to the msgbox at the end is if all three files are already open.

Please help- I think this must be super obvious since the macro runs fine if I run it from VBA.

I've tried the following:

Removed any error handling that could be hiding a problem with the sub/function
Set Tools>Options>"Break on all Errors" and I still don't receive any errors when the sub ends early.
Went through the whole sub with F8- it runs perfectly when I use that.
Call OpenFiles multiple times in a macro but the sub ends after the sub is called the first time so the rest never even run.

    Sub zzGetIt(sfullname As String)

    Dim ZGetIt As Workbook
    Dim wb As Workbook

    Dim ReadOnly As Boolean    
    Dim o As Boolean

    Dim sFile As String

    sFile = Dir(sfullname)

        MsgBox ("Trying to fetch")
        For Each wb In Application.Workbooks
        If wb.Name = sFile Then
            o = True
            Exit For
        End If
        Next wb

        If o = False Then
        Set zGetIt = Workbooks.Open(sfullname, ReadOnly:=ReadOnly)
        End If
        'reset o
        o = False
        MsgBox ("Finished fetching " & sFile)
    End Sub

    Sub OpenFiles()

    Dim Current As Worksheet
    Set Current = ActiveSheet

    Dim May As String
    Dim Jun As String
    Dim Jul As String

    May = "A:\Files\My Stuff\05 May 2019 - Master.xlsx"
    Jun = "A:\Files\My Stuff\06 June 2019 - Master.xlsx"
    Jul = "A:\Files\My Stuff\07 July 2019 - Master.xlsx"

    Call zzGetIt(May)
    Call zzGetIt(Jun)
    Call zzGetIt(Jul)

    Current.Activate
    Set Current = Nothing

    Msgbox("I can only get this msgbox if I run from macro list or 
    VBA application OR if all 3 workbooks were already open before I ran the 
    macro")

    End Sub

If May needs to be opened it will stop at May so I do not receive the msgbox after the sub is called for the first time.

I want the macro to open any of the three workbooks that are not already open and I need it to continue until the msgbox at the very end pops up

David Zemens David Zemens · Accepted Answer · 2019-08-13T17:14:54

I don't see anything obviously wrong with your code that might cause the observed behavior. But I would still do it differently. Perhaps this will help. I've revised your procedures that check for the file already open/open the file if not already open, but apart from that the main difference is that I'm calling this procedure in a loop from OpenFiles.

Option Explicit

Sub OpenFiles()

Dim Current As Worksheet
Set Current = ActiveSheet
Dim files As New Collection
Dim file

files.Add "A:\Files\My Stuff\05 May 2019 - Master.xlsx"
files.Add "A:\Files\My Stuff\06 June 2019 - Master.xlsx"
files.Add "A:\Files\My Stuff\07 July 2019 - Master.xlsx"

For Each file In files
    Debug.Print "Fetching file " & file
    If isFileOpen(CStr(file)) Then
        Debug.Print file & " is already open :) "
    Else
        Call GetFile(CStr(file), False)
    End If
Next

Current.Activate
Set Current = Nothing

MsgBox ("Finished!")

End Sub
Private Function isFileOpen(fullPath$) As Boolean
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(Dir(fullPath))
If Err.Number = 0 Then isFileOpen = True
End Function

Private Sub GetFile(fullPath$, readOnly As Boolean)
' No error handling here, this SHOULD raise an error if the file can't 
' be opened for any reason (invalid path, locked/in-use unless readOnly=True, etc. 
Debug.Print "Attempting to open " & fullPath
Workbooks.Open fullPath, readOnly:=readOnly

End Sub

Macro Only Finishes If Run Though VBA Application or Macro List

2 Answers