VBA Excel Break Points and Stop do not work

Question

Any idea why inserting break points and stop no longer stops my vba code from running?

The code runs ok all the way to the end (I tested it) but ignores break points and Stop.

Also step into just makes the code run in it's entirety, ignoring break points and stops.

When I close the workbook where the issue seems to originate from the same issue occurs in other macro workbooks.

if I completely close excel and re-open it with a normally working macro workbook the issue doesn't occur until I re-open the problem work book.

I added breakpoints on:

TotP1 = 0

of the 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've never heard of Stop not working, but I've heard about and experienced the breakpoint thing many times. When you compile VBA, it creates a p-code, which is used by the interpreter. You have the VBA syntax layer that you can see and the p-code layer that you can't see. When breakpoints stop working, it's because the p-code was corrupted. I don't know how or why it happened, but it did.

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

Answer 2

Just to 'second' Tibo's comment: I had a problem where I had a form with about 5 different subroutines. One of them (attached to a button event) would not stop when I set a breakpoint. in 10 years of VBA writing, I've never seen that happen. Interestingly, breakpoints worked on all of the other subroutines in that form. After much head-scratching and searching, I came upon this post and Tibo's comment. I added a "Stop" command to the affected subroutine, ran the procedure (it stopped as it should have) and then breakpoints began working again! Hope this helps someone in the future.

Answer 3

If one of the settings is unchecked then breakpoints will not work. "File/options/Current database/Application options/use access special keys" should be checked