Code Execution Error Persisting

Question

I have been using this code for adding zeroes to IDs for a while now, and it always works without fail. Until today it started saying the code execution has been interrupted.

Sub AddZeroes()
'Declarations
Dim i As Long, j As Long, endrow As Long
'Converts the A column format to Text format
Application.ScreenUpdating = False
Columns("A:A").Select
Selection.NumberFormat = "@"
'finds the bottom most row
endrow = ActiveSheet.Range("A1").End(xlDown).Row
'selects the top cell in column A
ActiveSheet.Range("A1").Select

'loop to move from cell to cell
For i = 1 To endrow - 1
            'Moves the cell down 1. Assumes there's a header row so really starts at row 2
            ActiveCell.Offset(1, 0).Select
            'The Do-While loop keeps adding zeroes to the front of the cell value until it hits     a length of 7
Do While Len(ActiveCell.Value) < 7
                            ActiveCell.Value = "0" & ActiveCell.Value
            Loop
Next i
Application.ScreenUpdating = True
End Sub

It always highlights either the

DO WHILE LEN(ACTIVECELL.VALUE) < 7

or it highlights the

LOOP

check whether your cells contains errors like #N/A! , #DIV/0! and so on — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 13, 2014 at 17:49
There is no number, it is just saying: CODE EXECUTION HAS BEEN INTERRUPTED — user2119980
– user2119980, Commented Mar 13, 2014 at 18:00
What is the value of 'i' when it halts? Then look at that row for a problem. — Wayne G. Dunn
– Wayne G. Dunn, Commented Mar 13, 2014 at 18:12

Community · Accepted Answer · 2017-05-23 10:29:01Z

5

+100

This is an odd little bug with Excel. It happens when you hit CTRL + BREAK to stop execution of code. That's when it is supposed to come up.

Sometimes if you stop execution with CTRL + BREAK, it sticks around and keeps popping up. That's the bug. (Sometimes it happens without using CTRL + BREAK too, I think.)

Try pressing CTRL + BREAK several times, try closing and reopening the workbook, try restarting the computer.

Close the file, make a copy, use the copy and see if it still happens.

If all else fails, you MAY have to just make a new workbook and copy all code / functions over. I've heard horror stories about this happening and the last suggesstion was the only fix.

It's nothing IN the code that's wrong.

Edit

I suppose it could be a keyboard malfunction too. The keyboard is shorting or sticking and Excel is receiving break commands (from ESC key, or BREAK key). Try another computer, too.

here's just a little additional note regarding your code added from mehow

it should serve you with a better performance and clearer code. (Note: Avoided .Select and improved your loop's logic)

Sub AddZeroes()
Application.ScreenUpdating = False

    Dim lastRow As Long, cell As Range

    lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & lastRow).NumberFormat = "@"

    For Each cell In Range("A2:A" & lastRow)
        If Len(cell) < 7 Then cell = "0" & cell
    Next i

Application.ScreenUpdating = True
End Sub

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Mar 13, 2014 at 20:05

user1759942

1,3466 gold badges14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gaffi Over a year ago

Fantastic! This solution requires nearly zero effort and works better than any other I've come across for resolving the 'phantom breakpoint' issue, and more people need to be aware of it. (Now if only Microsoft would get to preventing the issue in the first place...)

Collectives™ on Stack Overflow

Code Execution Error Persisting

1 Answer 1

Edit

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related