Immediate window not displaying correct values. Scoping issue?

Question

I'm using VBA in Excel 2013. I have been trying to suppress some Microsoft message boxes, without success so far. I am trying to use the "Application.DisplayAlerts" property to achieve this.

However, whilst looking at this I saw some odd behaviour. The value shown in the immediate window for this property does not match the value show by debug.print?

To reproduce create a blank worksheet and add the code below to the VBA for sheet1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.DisplayAlerts = False

    Debug.Print Application.DisplayAlerts

End Sub

Add a break point to the Debug.print line.

Now select a cell on sheet one. The code should run & break at the breakpoint.

In the immediate window type:

?Application.DisplayAlerts

It will show True.

Now step past the debug.Print. It will show False for the same property!!

Am I missing something? The immediate window should be in scope with the running code? Shouldn't it?

Answer 1

It will show True.

Now step past the debug.Print. It will show False for the same property!!

Am I missing something? The immediate window should be in scope with the running code? Shouldn't it?

Well, this is really hard to understand (and to explain), unfortunately I have not enough background informations to explain that in details, especially this code is executed from within an event...

But I'm experienced enough to understand what's going on behind the scenes, so I'll try to bring a little light into this.

At first of all the immediate window is correct.

There are 2 circumstances that you have to know:

a) https://msdn.microsoft.com/en-us/library/office/ff839782(v=office.15).aspx

If you set this property to False, Microsoft Excel sets this property toTrue when the code is finished, unless you are running cross-process code.

b) When you debug your code means not that the rest of the system stops and does nothing.

So when the debugger code "stops" your code means Excel executes codes in the background and sets the property to True in the Application, but that doesn't affect your code, because in your code this property is False.

Sounds crazy, but all code on your PC runs in that way.

Let us start with a different code, so you can see what's going one:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim Wb As Workbook

  Set Wb = Workbooks.Add

  Wb.ActiveSheet.Range("A1") = "Hello World"

  'Application.DisplayAlerts = False

  Wb.Close

End Sub

Select a cell and you can see the created workbook and Excel asks you to if you want to save the file.

Now remove the comment sign and enable Application.DisplayAlerts = False

Select a cell and you just see a little screen flickering, the created workbook is closed, Excel don't asks you.

Now place a breakpoint on Wb.Close and select a cell.

The code stops, when you hover the mouse over Application.DisplayAlerts you can see that VBA shows True.

Press F5 to continue the code and Excel doesn't asks you to save the file.

That means IMHO at this point this property is false in the code/data segment in where your code is executed. But Excel is executed in a different code/data segment. I assume that the VBA-Debugger creates a copy of the code/data segment, stops the code, but then gives the other tasks timeslice to execute there codes.

Select a cell again and the code stops again at Wb.Close

Now open Word and paste this sub into a regular module and execute it.

Sub Test()

  Dim xlApp As Object

  Set xlApp = GetObject(, "Excel.Application")

  Debug.Print Now, xlApp.DisplayAlerts

End Sub

As you see the property is True.

We can see the same behavior with this code in a regular module in Excel:

Private Sub Test1()

  Dim Wb As Workbook

  Set Wb = Workbooks.Add

  Wb.ActiveSheet.Range("A1") = "Hello World"

  Application.DisplayAlerts = False

  Wb.Close

End Sub

Place a breakpoint at Wb.Close run the code, switch to Word and execute Test => the property is True.

So IMHO my assumption is correct.

Now we came to the interesting part, execute this in Excel:

Private Sub Test2()

  Dim wdApp As Object

  Dim Wb As Workbook

  Set Wb = Workbooks.Add

  Wb.ActiveSheet.Range("A1") = "Hello World"

  Application.DisplayAlerts = False

  Set wdApp = GetObject(, "Word.Application")

  wdApp.Run "Test"

  Wb.Close

End Sub

Switch to Word, look into the immediate window, the property is False!

Also when we give Excel a chance to execute other code:

Private Sub Test3()

  Dim wdApp As Object

  Dim Wb As Workbook

  Dim i As Integer

  Set Wb = Workbooks.Add

  Wb.ActiveSheet.Range("A1") = "Hello World"

  Application.DisplayAlerts = False

  For i = 1 To 5

    DoEvents

    Application.Wait Now + TimeSerial(0, 0, 1)

  Next

  Set wdApp = GetObject(, "Word.Application")

  wdApp.Run "Test"

  Wb.Close

End Sub

The immediate window in Word shows False.

State of the art, strange things happens from time to time in our asynchronous world. :-)

Andreas.

Answer 2

Thanks for the detailed response Andreas.

I really do think that  the immediate window should be looking at the same copy of code/variables as is running in the debugger.

If not it makes debugging very  difficult. 

Something for Microsoft to address in future 

Keith