Execution wrongly stops before Debug.Assert

B

BCVolkert

New Member
Joined
Dec 19, 2014
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code: 
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
What about the the code for IsExcelWorkbook(sFilepath)? Have you debugged that?
 
Upvote 0
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".
 
Upvote 0
Dan_W said:
What about the the code for IsExcelWorkbook(sFilepath)? Have you debugged that?
Click to expand...
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

VBA Code: 
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
 
Upvote 0
BCVolkert said:
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

VBA Code: 
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
Click to expand...
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.
 
Upvote 0
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.
 
Upvote 1
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:

VBA Code: 
If TypeName(Expression) = "Workbook" Then

Isn't going to return true, is it, because you're only ever passing it a string.
 
Upvote 1
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:
VBA Code: 
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?
 
Upvote 1
Solution
Dan_W said:
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.
Click to expand...
That was definitely worth a test. So I tried:
VBA Code: 
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:
VBA Code: 
Debug.Assert IsExcelWorkbook(sFilepath, OpenIfClosed:=True) = True

Doing this on your phone is a bit masochistic, but I do appreciate the dedication.
 
Upvote 0
So I just double checked, and the official documentation confirm the point that debug assert stops the code when the expression is false:
VBA Code: 
Conditionally suspends execution when booleanexpression returns False at the line on which the method appears.
learn.microsoft.com

Assert method (Visual Basic for Applications)

Office VBA reference topic
learn.microsoft.com
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top