VBA - Connection Range Count Incorrect With Second Window Maximized

Question

Take a file, with a pivot table and some data/filter:

Place the following method in the "BeforeClose" method:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    'ThisWorkbook.connections(1).Ranges.count

End Sub

Now if you close this file(and set a breakpoint), you will notice that commented out statement has the following value:

1

And that's as we would expect, because we have one connection, one pivot table, fairly straight forward. Now repeat the same steps, but when you close the file, first open a blank excel file, and close the main file when it's minimized:

Now you get:

0

Now, how can this be? This isn't my understanding of how this property works. I thought "ThisWorkbook" was:

"The ThisWorkbook property is much easier to understand as it simply references the Excel Workbook in which the VBA code is executing."

What am I getting wrong here? The "connection" is found but the range property appears to be incorrect when a second file is active. My original intent was to determine where the connection was being used. Maybe I'm going about it the wrong way?

Update 1

Oops, sorry about all the confusion with the MsgBox, it appears as though(in my previous verion of the question) the MsgBox is making the window active again and displaying 1 rather than the value present in the code. However if you put a break point on the line you will see the issue before the window becomes active:

So the issue is still real, just hard for me to reproduce this in a way that easily visible(and communicable on stack overflow). Maybe a potential work around would be to make the current window the active window before closing. Still I would like to understand to root cause of this strange behavior.

I've updated the original question with this clarification, and removed the confusing MsgBox issue. Thanks everyone for the good catch.

Answer 1

I reproduced your problem. We can prove that it is looking at the correct object by using

Debug.Print ThisWorkbook.Connections(1).Parent.Name
Debug.Print ThisWorkbook.Connections(1).Name

And yet the count will change to zero when minimized. This looks like a bug. Report it to Microsoft. You can click on File > Feedback then look for the frown face to submit feedback.

You can also start a thread about the issue in the MS forums where the issue can be escalated to developers: https://answers.microsoft.com/en-us