9

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

enter image description here

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:

enter image description here

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:

enter image description here

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.

0m3r
  • 12,286
  • 15
  • 35
  • 71
David Rogers
  • 2,601
  • 4
  • 39
  • 84
  • Which OS + Excel version? Can't reproduce on W7+Excel 2016 64bit. `ThisWorkbook` indeed refers to the workbook containing the code and in minimized scenario with second file open, the `MsgBox` gives me `1`. – Rik Sportel Nov 05 '18 at 09:55
  • Windows 7 - 32-bit - Office 365 Version 1808 – David Rogers Nov 05 '18 at 12:44
  • I could not reproduce the issue on Excel2016 or 2013. I have same results as @RikSportel – TomJohn Nov 05 '18 at 15:26
  • 1
    Sorry guys, yes the original method of reproducing the issue was incorrect, apparently "MsgBox" threw it off. Could you guys trying reproducing it while debugging? Hopefully someone else can see this, I've seen it on multiple systems(All 32-bit though). – David Rogers Nov 05 '18 at 15:51
  • 2
    More continent way to reproduce the problem is to use `Workbook_WindowDeactivate` method (either use msgbox .or Debug.Print) . It appears when ever a new book opened or simply switch window. Related to your other Featured question I think It is specific for OLAP Cube only. I failed to reproduce the error with other connections. – Ahmed AU Nov 06 '18 at 00:37

1 Answers1

4

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

HackSlash
  • 4,944
  • 2
  • 18
  • 44
  • Excellent suggestion, [I've cross posted here](https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_365hp/vba-connection-range-count-incorrect-with-second/f9713455-1fba-43ff-a338-53602b96d4a4?tm=1541787019398). – David Rogers Nov 09 '18 at 18:11
  • Cool, I gave it a +1 – HackSlash Nov 09 '18 at 18:12