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]

he 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".

So based on the MS documentation, that is not what this indicates. It stops because it detects the statement to be True and is letting you know this. You can then continue with the code if you like.

 

[Dan_W]

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

No, no. It's not a bit masochistic at all! It's completely and thoroughly masochistic!

 

[BCVolkert]

Note that the function IsExcelWorkbook accepts either a string or an object. It includes the code:

      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

It does return True and the workbook is left open.
In the evil fourth Debug.Assert, IsExcelWorkbook is called again with the same string. This time it gets to the Else clause:

Else
    IsExcelWorkbook = False
 End If

And does return False. So that's my problem. I just need to check for an open workbook having the correct path.

Thanks. I'm off to the races.

I'll post the corrected code when I get it working.

Thanks for your help.

 

[Dan_W]

Ok, things are getting semi-serious here because I've now managed to get a laptop out. It's a Mac though, and I have no idea how to use Mac and VBA together, so who knows what's going to happen.

Note that the function IsExcelWorkbook accepts either a string or an object.

I did see that, and that was the point (I thought) I was making here (Execution wrongly stops before Debug.Assert). In the code you've posted above, you're only ever passing is sFilePath (a string), so this is not going to return true for an object. What you could do, is test if it's "String" rather than "Workbook", then check to see if it's a string, test to see if the file exists (If Len(Dir(Expression)) Then), and if so you could then check to see if a workbook with that filepath is open.

But I would add one thing - I'm 90% certain that when you check to see if a workbook is currently open in the present Excel instance, you need to reference it by the filename, and not by the full filepath. So:

sFilepath = "P:\TestFiles\Test.xlsx"
If Not Application.Workbooks(sFilePath) Is Nothing then

would not work, whereas,

sFilename = "Test.xlsx"
If Not Application.Workbooks(sFileName) Is Nothing then

would work. Mind you, I could be completely wrong on that, in which case I revise my 90% certainty comment down to 30% to avoid any embarrassment on my part.

 

[BCVolkert]

Thanks for the insight. I'm a bit new to the Debug.Assert thing.

So far, the following is working for me. It runs through to the "IsExcelWorkbook_test passed." in the Immediate Window as originally intended. So, that's a win.

   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
         If WorkbookIsOpen(Expression) Then
            IsExcelWorkbook = True
         Else
            IsExcelWorkbook = False
         End If
      End If
   End If

WorkbookIsOpen takes a string argument and does return true in the IsExcelWorkbook_test scenario. So, the first phase of this is pretty well complete.

WorkbookIsOpen is a procedure I wrote or grabbed from the internet a long time ago. I think, I'll revisit that too and see if I can make it take either a string or an object. If the object is a workbook and open, WorkbookIsOpen would return true. If it's a string, it would do its thing as currently written.

My plan is to continue augmenting the IsExcelWorkbook_test procedure to test cases where Expression is a Workbook object, and cases where the Expression is something to the effect of FileThatDoesNotExist.xlsm as a string. As you suggest, I'll also try situations in which the Expression is just a filename, a filename with extension, and a few permutations of paths. An interesting one will be where an open workbook has the same filename and extension as the expression; but, having a different path. At that point, we'll see if I'm also a masochist.

You are welcome to continue working on this as "Masochist In Training"; but, I think you solved my problem.

P.S. No slight to MIT was intended; but, if the shoe fits .........

 

[BCVolkert]

Ok, things are getting semi-serious here because I've now managed to get a laptop out. It's a Mac though, and I have no idea how to use Mac and VBA together, so who knows what's going to happen.


I did see that, and that was the point (I thought) I was making here (Execution wrongly stops before Debug.Assert). In the code you've posted above, you're only ever passing is sFilePath (a string), so this is not going to return true for an object. What you could do, is test if it's "String" rather than "Workbook", then check to see if it's a string, test to see if the file exists (If Len(Dir(Expression)) Then), and if so you could then check to see if a workbook with that filepath is open.

But I would add one thing - I'm 90% certain that when you check to see if a workbook is currently open in the present Excel instance, you need to reference it by the filename, and not by the full filepath. So:

sFilepath = "P:\TestFiles\Test.xlsx"
If Not Application.Workbooks(sFilePath) Is Nothing then

would not work, whereas,

sFilename = "Test.xlsx"
If Not Application.Workbooks(sFileName) Is Nothing then

would work. Mind you, I could be completely wrong on that, in which case I revise my 90% certainty comment down to 30% to avoid any embarrassment on my part.

That's a reasonable couple of cases for testing. I probably have a Filename function laying around somewhere that will take just the filename portion of a path. I'll just need to verify that I honor the full path specification if it is provided. Another case to test!!!

 

[Dan_W]

I will need to give it some more thought, but my gut reaction is that you might be essentially repeating yourself in the code here. Let me wrap my head around it, but one thing that jumped out at me is:

If WorkbookIsOpen(Expression) Then
            IsExcelWorkbook = True
         Else
            IsExcelWorkbook = False
         End If

Could be rewritten to just

IsExcelWorkbook = WorkbookIsOpen(Expression)

 

[BCVolkert]

I will need to give it some more thought, but my gut reaction is that you might be essentially repeating yourself in the code here. Let me wrap my head around it, but one thing that jumped out at me is:

If WorkbookIsOpen(Expression) Then
            IsExcelWorkbook = True
         Else
            IsExcelWorkbook = False
         End If

Could be rewritten to just

IsExcelWorkbook = WorkbookIsOpen(Expression)

You are correct. That is tighter. I tend to be verbose and explicit until I'm sure what is going on. Consolidation comes later -- if it makes my priority list.

 

[BCVolkert]

Ok, things are getting semi-serious here because I've now managed to get a laptop out. It's a Mac though, and I have no idea how to use Mac and VBA together, so who knows what's going to happen.


I did see that, and that was the point (I thought) I was making here (Execution wrongly stops before Debug.Assert). In the code you've posted above, you're only ever passing is sFilePath (a string), so this is not going to return true for an object. What you could do, is test if it's "String" rather than "Workbook", then check to see if it's a string, test to see if the file exists (If Len(Dir(Expression)) Then), and if so you could then check to see if a workbook with that filepath is open.

But I would add one thing - I'm 90% certain that when you check to see if a workbook is currently open in the present Excel instance, you need to reference it by the filename, and not by the full filepath. So:

sFilepath = "P:\TestFiles\Test.xlsx"
If Not Application.Workbooks(sFilePath) Is Nothing then

would not work, whereas,

sFilename = "Test.xlsx"
If Not Application.Workbooks(sFileName) Is Nothing then

would work. Mind you, I could be completely wrong on that, in which case I revise my 90% certainty comment down to 30% to avoid any embarrassment on my part.

You are correct. Only the filename including the extension can be specified. I verified that with a test.

 

[Dan_W]

Phew! Thanks for letting me know.