VBA Excel Break Points and Stop not working

Question

Any idea why inserting breakpoints and breakpoints no longer stops my vba code?

The code works fine until the end (I tested it), but ignores breakpoints and Stop.

Also, take a step so that all code runs entirely, ignoring breakpoints and stopping.

When I close a book in which a problem arises from the same problem, arises in other macro books.

If I completely close excel and reopen it with a normally working workbook, the problem does not occur until I open the problems workbook.

I added breakpoints:

TotP1 = 0 

following code:

 Option Explicit Private Country As String Private Measure As String Private P1 As String Private P2 As String Private TotP1 As Double Private TotP2 As Double Sub VennDisplayIt() Dim SI() As String Dim SICount As Integer Dim x As Integer Dim OSh As Worksheet Dim BrandListBox As Object Dim VennGroup As Shape TotP1 = 0 TotP2 = 0 Set OSh = ThisWorkbook.Sheets("Venn") Set BrandListBox = OSh.OLEObjects("BrandListBox").Object ReDim SI(2, 0) For x = 0 To BrandListBox.ListCount - 1 If BrandListBox.Selected(x) = True Then 'If UBound(SI) < 4 Then ReDim Preserve SI(2, UBound(SI, 2) + 1) SI(1, UBound(SI, 2)) = BrandListBox.List(x) SI(2, UBound(SI, 2)) = x + 1 'End If End If Next x If UBound(SI, 2) < 2 Then BrandListBox.Selected(BrandListBox.ListIndex) = True Exit Sub ElseIf UBound(SI, 2) > 4 Then BrandListBox.Selected(BrandListBox.ListIndex) = False Exit Sub End If For x = 1 To UBound(SI, 2) OSh.Range("o8").Offset(x, 0).Value = SI(1, x) OSh.Range("o8").Offset(x + 5, 0).Value = SI(1, x) Next x For x = UBound(SI, 2) + 1 To 4 OSh.Range("o8").Offset(x, 0).Value = "" OSh.Range("o8").Offset(x + 5, 0).Value = "" Next x SICount = UBound(SI, 2) For x = 1 To OSh.Shapes.Count If Right(OSh.Shapes(x).Name, 5) = "Group" Then If LCase(OSh.Shapes(x).Name) = SICount & "waygroup" Then Set VennGroup = OSh.Shapes(x) OSh.Shapes(x).Visible = True Else OSh.Shapes(x).Visible = False End If End If Next x For x = 1 To SICount VennGroup.GroupItems.Item(SICount & "WayBrand" & x).DrawingObject.Text = SI(1, x) Next x Country = ThisWorkbook.Sheets("Venn").Range("D4").Value Measure = ThisWorkbook.Sheets("Venn").Range("E32").Value P2 = ThisWorkbook.Sheets("Venn").Range("E31").Value P1 = ThisWorkbook.Sheets("Selections").Range("B5").Value End Sub 

Answer 1

I have never heard that Stop not working, but I have heard and experienced a breakpoint many times. When you compile VBA, it creates p-code that is used by the interpreter. You have a VBA syntax layer that you can see, and a p-code layer that you don't see. When breakpoints stop working, this is due to damage to the p-code. I do not know how and why this happened, but it happened.

The fix is ​​to export, delete, and reimport all of your modules. Exporting them creates a .bas file (plain text, really). When re-importing, the p-code is restored from scratch. If you have more than two modules, get CodeCleaner (a free add-on) and it will automatically export and reimport.

Answer 2

If breakpoints are in your code, then the code should stop as soon as it hits this line. Possible causes of your problem:

a) The code never hits a breakpoint. It seems extremely unlikely to see you breaking in the first line of your code. Perhaps go through sub-F8 to check how it works.

b) Your control points have been erased. Any line with a breakpoint should appear as highlighted in red on your IDE. Your breakpoints can be easily wiped, for example, closing and opening a book (a screenshot will be really useful).

c) your workbook is somehow violated. It would be unlikely to break something as fundamental as a stop at control points and still function normally. Are you sure your code is really running?

Answer 3

Just to Thibault's “second” comment: I had a problem when I had a form with about 5 different routines. One of them (attached to the button event) will not stop when I set a breakpoint. I’ve never seen this happen in 10 years of writing VBA. Interestingly, breakpoints worked on all other routines in this form. After many scratches and distortions, I came across this post and Thibault's comment. I added the Stop command to the affected subroutine, followed the procedure (it stopped, as it should be), and then the breakpoints started working again! Hope this helps someone in the future.

Answer 4

Just share the funny thing that happened to me if that helps someone. The mistake I made was that I simply took the method code intended to open the workbook and inserted it into the code area of Sheet1 excel object code. Thus, it was not possible to run the Workbook_Open method.

 Private Sub Workbook_Open() Stop On Error Resume Next Call ActiveSheet.Worksheet_Activate On Error GoTo 0 End Sub 

I needed to insert this method after double-clicking ThisWorkbook node under Microsoft Excel Objects in the Project panel, as shown below:

Note The side effect of copying code in other cases can be bizarre.

Answer 5

If one of the options is not set, breakpoints will not work. "File / options / Current database / Application settings / Use special keys" should be checked