Execution wrongly stops before Debug.Assert

[BCVolkert]

The following VBA Sub stops at the fourth Debug.Assert statement; but, will continue if I press F5 in the VBA editor. To my thinking, it should just continue because the expression "IsExcelWorkbook(sFilepath) = True" is true.

I've tried adding a Wait function and several DoEvents statements without any change in behavior.

When the code stops at the fourth Debug.Assert statement, the Test.xlsx workbook is open as expected. The VBA function IsExcelWorkbook(sFilepath) does return True after I click F5 or F8 in the IDE when execution conditionally stops waiting on the Debug.Assert. I've stepped through the code and it all works as expected; except that I hear a tone after clidking F8 on that fourth Debug.Assert statement. A second click on that statement allows execution to continue and "IsExcelWorkbook_test passed." shows up in the Immediate Window.

In the code below, the various procedures ReportStart, WaitAFewSeconds, ReportError, and ReportFinish do exactly what they imply and are functioning normally. The three Debug.Assert statements that precede the one commented as "Always stops here." also work as expected.

I'd appreciate any suggestions offered. Thanks for your time and consideration.

Bruce

Private Sub IsExcelWorkbook_test()

   Const zsProcedure As String = "IsExcelWorkbook_test"
   ReportStart zsProcedure, m_zsModule, m_zsLibrary, m_zbDebugging, m_zbLogging
 
#If LateBinding Then
   Dim oXlApp As Object
#Else
   Dim oXlApp As Excel.Application ' Requires a reference:  Microsoft Excel xx.x Object Library
#End If

   Dim sFilepath As String
   Dim sTestResult As String

   On Error GoTo ErrorHandler

   sTestResult = zsProcedure & " failed."

   sFilepath = "P:\TestFiles\Test.xlsx"

   Debug.Assert IsExcelWorkbook(sFilepath) = False
   DoEvents
   Debug.Assert IsExcelWorkbook(sFilepath, OpenIfClosed:=False) = False
   DoEvents
   Debug.Assert IsExcelWorkbook(sFilepath, OpenIfClosed:=True) = True
   ' WaitAFewSeconds 10 ' Did not help.
   DoEvents ' Did not help.
   DoEvents ' Did not help.
   DoEvents ' Did not help.
   DoEvents ' Did not help.
   DoEvents ' Did not help.
   DoEvents ' Did not help.
   Debug.Assert IsExcelWorkbook(sFilepath) = True ' Always stops here. F5 allows sub to complete without error.
   DoEvents ' Did not help.

   sTestResult = zsProcedure & " passed."  ' After F5 this shows in the Immediate Window as expected.
  
Finally:
   Debug.Print sTestResult
   ReportFinish zsProcedure, m_zsModule, m_zsLibrary, m_zbDebugging, m_zbLogging
   On Error GoTo 0
   Exit Sub
     
ErrorHandler:
   ReportError zsProcedure, m_zsModule, m_zsLibrary, m_zbDebugging, m_zbLogging
   Select Case True
      Case Else
         If m_zbDebugging Then
            '@Ignore StopKeyword
            If m_zbEnableStops Then Stop
            Continue
         Else
            ' Silent return
            Continue
         End If
   End Select
   Resume Finally

End Sub

 

[Dan_W]

What about the the code for IsExcelWorkbook(sFilepath)? Have you debugged that?

 

[Dan_W]

It would help to see that function too, because looking at the other debug assert statements, they seem to be doing something slightly different and actually, I can envisage a scenario where the first three work but the fourth "fails".

 

[BCVolkert]

What about the the code for IsExcelWorkbook(sFilepath)? Have you debugged that?

Yes. It works. It even works as executed in IsExcelWorkbook_test when I step through beginning in IsExcelWorkbook_test and stepping right through IsExcelWorkbook. The VBE stops on that fourth Debug statement. When I click F8, execution proceeds to the "sTestResult = zsProcedure & " passed." statement indicating that the fourth Debug.Assert detected "True".

It all works; except that it pauses with the fourth Debug statement highlighted in yellow.

I've included the IsExcelWorkbook function just in case I've missed something; but, from my perspective it is working as intended.

Thanks for looking at this.

Bruce

Public Function IsExcelWorkbook(ByRef Expression As Variant, Optional ByVal OpenIfClosed As Boolean = False) As Boolean

   Const zsProcedure As String = "IsExcelWorkbook"
   ReportStart zsProcedure, m_zsModule, m_zsLibrary, m_zbDebugging, m_zbLogging
  
#If LateBinding Then
   Dim oXlApp As Object
   Dim oXlWkb As Object
#Else
   Dim oXlApp As Excel.Application ' Requires a reference:  Microsoft Excel xx.x Object Library
   Dim oXlWkb As Excel.Workbook ' Requires a reference:  Microsoft Excel xx.x Object Library
#End If
   
   Dim bWasRunning As Boolean
   
   On Error GoTo ErrorHandler
   
   If TypeName(Expression) = "Workbook" Then
      IsExcelWorkbook = True
   Else
      On Error Resume Next
      If OpenIfClosed Then
         Set oXlApp = ExcelApp(CreateAppInstanceIfClosed:=True, WasRunning:=bWasRunning)
         Set oXlWkb = oXlApp.Workbooks.Open(CStr(Expression)) ' Try to open the Expression as a workbook
         On Error GoTo ErrorHandler
        
         If Not oXlWkb Is Nothing Then
            IsExcelWorkbook = True
         Else
            IsExcelWorkbook = False
            If Not bWasRunning Then oXlApp.Quit
         End If
      Else
         IsExcelWorkbook = False
      End If
   End If
   
Finally:
   Set oXlWkb = Nothing
   Set oXlApp = Nothing
   ReportFinish zsProcedure, m_zsModule, m_zsLibrary, m_zbDebugging, m_zbLogging
   On Error GoTo 0
   Exit Function
      
ErrorHandler:
   ReportError zsProcedure, m_zsModule, m_zsLibrary, m_zbDebugging, m_zbLogging
   Select Case True
      Case Else
         If m_zbDebugging Then
            '@Ignore StopKeyword
            If m_zbEnableStops Then Stop
            Continue
         Else
            ' Silent return
            Continue
         End If
   End Select
   Resume Finally

End Function

 

[BCVolkert]

Yes. It works. It even works as executed in IsExcelWorkbook_test when I step through beginning in IsExcelWorkbook_test and stepping right through IsExcelWorkbook. The VBE stops on that fourth Debug statement. When I click F8, execution proceeds to the "sTestResult = zsProcedure & " passed." statement indicating that the fourth Debug.Assert detected "True".

It all works; except that it pauses with the fourth Debug statement highlighted in yellow.

I've included the IsExcelWorkbook function just in case I've missed something; but, from my perspective it is working as intended.

Thanks for looking at this.

Bruce

Public Function IsExcelWorkbook(ByRef Expression As Variant, Optional ByVal OpenIfClosed As Boolean = False) As Boolean

   Const zsProcedure As String = "IsExcelWorkbook"
   ReportStart zsProcedure, m_zsModule, m_zsLibrary, m_zbDebugging, m_zbLogging
 
#If LateBinding Then
   Dim oXlApp As Object
   Dim oXlWkb As Object
#Else
   Dim oXlApp As Excel.Application ' Requires a reference:  Microsoft Excel xx.x Object Library
   Dim oXlWkb As Excel.Workbook ' Requires a reference:  Microsoft Excel xx.x Object Library
#End If
  
   Dim bWasRunning As Boolean
  
   On Error GoTo ErrorHandler
  
   If TypeName(Expression) = "Workbook" Then
      IsExcelWorkbook = True
   Else
      On Error Resume Next
      If OpenIfClosed Then
         Set oXlApp = ExcelApp(CreateAppInstanceIfClosed:=True, WasRunning:=bWasRunning)
         Set oXlWkb = oXlApp.Workbooks.Open(CStr(Expression)) ' Try to open the Expression as a workbook
         On Error GoTo ErrorHandler
       
         If Not oXlWkb Is Nothing Then
            IsExcelWorkbook = True
         Else
            IsExcelWorkbook = False
            If Not bWasRunning Then oXlApp.Quit
         End If
      Else
         IsExcelWorkbook = False
      End If
   End If
  
Finally:
   Set oXlWkb = Nothing
   Set oXlApp = Nothing
   ReportFinish zsProcedure, m_zsModule, m_zsLibrary, m_zbDebugging, m_zbLogging
   On Error GoTo 0
   Exit Function
     
ErrorHandler:
   ReportError zsProcedure, m_zsModule, m_zsLibrary, m_zbDebugging, m_zbLogging
   Select Case True
      Case Else
         If m_zbDebugging Then
            '@Ignore StopKeyword
            If m_zbEnableStops Then Stop
            Continue
         Else
            ' Silent return
            Continue
         End If
   End Select
   Resume Finally

End Function

The ExcelApp function is also working. In the case being tested, Excel is already running with two other workbooks open. ExcelApp does return the Excel Application as oXlApp and bWasRunning is returned as true.

 

[Dan_W]

I'm looking at this on my mobile phone so it's a bit tricky so please bear with me but the first thing that occurs to me is that the fact that you can press f8 on the fourth debug assert line and it continues is not an indication I think that the result is true. Rather debug assert will stop the code when the result is false (meaning the overall expression, rather than the result of the function) but will nonetheless allow you to then continue with the code.

 

[Dan_W]

Again, apologies, I'm doing this in stages in case I accidentally deleted the text.

The function code is useful, thanks for posting it. Working through it, and based on your sample code, we can agree that the condition:

If TypeName(Expression) = "Workbook" Then

Isn't going to return true, is it, because you're only ever passing it a string.

 

[Dan_W]

The fourth debug asserts statement doesn't use the second parameter of your function (OpenIfClosed) so by default, when it is omitted, VBA will use false.

That being the case, it makes sense that VBA proceeds to the following line:

IsExcelWorkbook = False

Thus activating the debug assert function to stop your code from running, indicating that the result of the expression is not in fact True

Apologies for the stream of consciousness, but that logic tracks, doesn't it?

 

[BCVolkert]

I'm looking at this on my mobile phone so it's a bit tricky so please bear with me but the first thing that occurs to me is that the fact that you can press f8 on the fourth debug assert line and it continues is not an indication I think that the result is true. Rather debug assert will stop the code when the result is false (meaning the overall expression, rather than the result of the function) but will nonetheless allow you to then continue with the code.

That was definitely worth a test. So I tried:

Debug.Assert IsExcelWorkbook(sFilepath) ' Always stops here. F5 allows sub to complete without error.

Same behavior. The VBE pauses with the line above highlighted. A second or two later, I get the tone. Pressing F5 or F8 causes execution to continue without triggering the ErrorHandler and the Test.xls is already opened from the prior Debug statement:

Debug.Assert IsExcelWorkbook(sFilepath, OpenIfClosed:=True) = True

Doing this on your phone is a bit masochistic, but I do appreciate the dedication.

 

[Dan_W]

So I just double checked, and the official documentation confirm the point that debug assert stops the code when the expression is false:

Conditionally suspends execution when booleanexpression returns False at the line on which the method appears.

Assert method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com