VBA Code in Excel randomly stops executing. No error messages occur

Question

Essentially, I have an Updata button that takes information from two columns, in two spreadsheets (within 1 book). The overall goal of this code is to take all the values from one column, and then append the values from the other column below it.

Worksheets("Overall Flow").Range("A4:A1004").Value = Worksheets("Active").Range("A2:A1002").Value
Dim i As Integer
For i = 4 To 1004
    If Worksheets("Overall Flow").Range("A" & Trim(str(i))) = "" Then
        Worksheets("Overall Flow").Range("A" & Trim(str(i)) & ":A" & Trim(str(1000 + i))).Value = Worksheets("Inactive").Range("A2:A1002").Value
        i = 1005
    End If
Next

For some reason, the first line executes, and then finishes. When I put break points, then do step-by-step, no other steps happen afterwards.

When I run the first line individually, it appears to work fine, but not when:

Worksheets("Overall Flow").Range("A" & Trim(str(i)) & ":A" & Trim(str(1000 + i))).Value = Worksheets("Inactive").Range("A2:A1002").Value

or

Worksheets("Overall Flow").Range("A4:A1004").Value = Worksheets("Inactive").Range("A2:A1002").Value

is present aftwards.

Answer 1

To improve the performance I called the function DoEvents inside the loop. It solved the problem for me.

Answer 2

VBA simply is prone to this issue. I have used it for years in corproate workflows because it is so hardcoded into lots of things, but if possible I would just consider alternatives. If this an ad-hoc project R will be faster and offer more flexibility. If this is more production oriented and meant to handle large volumes I would consider informatica.

Answer 3

I ran into the same problem. I had a sub routine that gave random errors throughout the code without giving error messages. By pressing F8, the code would resume.

I found someone had posted a Subroutine he called "ThatCleverDevil" I do not remember the resource or who posted it. It would warn you an error was about to occur. The routine is posted below.

I split the code into component sub-routines. The short snippits ran with no interruption or erros. I created a subroutine that called each snippit. Errors resumed.

They would run individually, but not all together.

RESOLUTION: Between called sub-routines, I ran the following line of code:

Application.Wait Second(Now) + 1

The code then ran without error.

Thanks to whomever it was that wrote ThatCleverDevil. And special thanks to the coder who wrote about Application.Wait.

Sub ThatCleverDevil()

On Error GoTo err

MsgBox "About to error" err.Raise 12345 MsgBox "Got here after the error"

Exit Sub

err: Stop: Resume

End Sub

Robert

Answer 4

I had this issue and I tracked it down to custom VBA functions used in Conditional Formatting that was processed while application.screenupdating was still set to True.

I'm not sure how consistent this behaviour is but, when a custom VBA function is referred to in a conditional formatting rule, when the screen updates, it will not step through the code even when employing break points or the debug.assert method. Here's the breakdown of what happened:

Context:

• 2 open workbooks.
• Conditional formatting and custom function in question were in workbook1.
• The code I was attempting to execute was in workbook2.

Process

1. I call a procedure in workbook2.
2. Workbook2's procedure reaches a line executing an autofilter command.
3. Autofilter command triggers a screen update in all open workbooks (any command that triggers a Worksheet_Change or Worksheet_Calculate event can apply here).
4. Screen update processes the conditional formatting rules, including the rule in workbook1 calling workbook1's custom function.
5. Custom function is run in a 'silent' state (i.e. with no interaction with user, ignoring break points and "debug.assert" calls; this appears to be by design as part of the conditional formatting feature)
6. Custom function finishes execution and ceases all other active code execution.

I fixed my problem by adding a Application.ScreenUpdating = False line at the start to prevent screen updates and, by extension, conditional format processing (but it's best to keep custom functions away from conditional formatting to begin with).

I'm not sure if this is relevant to your situation at all but I hope it helps somebody.

Answer 5

Solution to this is very unusual.

CTRL+BREAK CTRL+BREAK CTRL+BREAK ESC

It just happened to me againg after long time, I was looking for a solution and I came here then this sequence came back to my mind and I tried.

It worked for me, I hope this will help someone.

Answer 6

It has already been mentioned in transistor1's answer, but only as a side comment.

I had a similar problem, that VBA code simply stopped executing in the middle of a function. Just before that it also jumped back a few lines of code. No Error Message was shown.

I closed all open Excel programs, and upon reopening the File everything worked fine again.

So my confirmed Answer to this problem is: Corrupted Memory, restart Excel.

Edit: after doing this, I also encountered the Problem that Visual Basic Editor crashed when I tried uncommenting a particular line. So I created a New Excel file and copied my code. Now I don't have any problems anymore.

Answer 7

Update: Tweaked code (now with error checking!)

Main points concerning the current code:

• When copying the ACTIVE range, check for last consecutive cell used. This is faster and more effecient than a loop.
• Why are you trimming a number you know will not contain spaces?
• There's no need to set i = 1005, just use Exit For. This is more effecient and clear to the reader what the intention is. I don't use this in the code below since I avoided looping altogether.

Here's a different way you can do this without any looping, which I think is more clear and effecient. Try this and see if it works for you:

Sub test()

Dim lastRow As Long, offSet As Long
lastRow = Worksheets("Active").Range("A2").End(xlDown).row

'Sanity checks
If IsEmpty(Worksheets("Active").Range("A2")) = True Then offSet = 1: lastRow = 2
If lastRow > 1001 Then lastRow = 1002

Worksheets("Overall Flow").Range("A4:A" & lastRow + 2).Value = _
Worksheets("Active").Range("A2:A" & lastRow).Value

If lastRow < 1002 Then
    Worksheets("Overall Flow").Range("A" & lastRow + (3 - offSet) & _
    ":A1004").Value = Worksheets("Inactive").Range("A2:A1002").Value
End If

End Sub

Notes:

• Sanity check 1 is for if A2 is blank in the Active sheet.
• Sanity check 2 is for if there are cells beyond A1002 with values in Active sheet.

Answer 8

This is what I am using to test your code. Since I don't know what's in the spreadsheets, I can't reproduce exactly what you're seeing so I'm first putting dummy data into the ranges.

For me it is running fine every time, and I've tried it on 2 different computers - Excel 2003, and Excel 2010.

I set a breakpoint and stepped with F8, and also Shift F8 and both worked fine.

Something may be different with your data (i.e. the first cell being copied over from the inactive sheet is blank and therefore execution stops after processing the first cell -- check that column A4 is not blank), or perhaps some memory has gotten corrupted from having Office being killed.

In a Module I have:

Sub test()
    Worksheets("Active").Range("A2:A1002").Value = "active"
    Worksheets("Active").Range("A5").Value = ""
    Worksheets("Inactive").Range("A2:A1002").Value = "inactive"

    Worksheets("Overall Flow").Range("A4:A1004").Value = Worksheets("Active").Range("A2:A1002").Value
    Dim i As Integer
    For i = 4 To 1004
        If Worksheets("Overall Flow").Range("A" & Trim(Str(i))) = "" Then
            Worksheets("Overall Flow").Range("A" & Trim(Str(i)) & ":A" & Trim(Str(1000 + i))).Value = Worksheets("Inactive").Range("A2:A1002").Value
            i = 1005
        End If
    Next
End Sub

Have you tried the same code on another computer?