Excel Macro randomly stops in the middle of running. No errors, it just stops.

Hi,

Code doesn't behave randomly, instead it has the irritating habit of doing precisely what you tell it to to even if that wasn't what you meant it to do. Post your code and a sample of the data it is working on. Include the result you expect from that code.

---

If this post answers your question, please mark it as the Answer.

Mike H

Yes it does, and I can prove it. 

I'm in the middle of a corporate migration from XP/2003 to 8.1/2013 and have worked for weeks on "upgrading" something that has been a staple of my user's business for years.  A trading position monitor that updates every minute, integrating user-entered data with database and live data from Bloomberg and Reuters.

If you edit an email in Outlook while the monitor is doing its final data compilation, it fails to complete, every single time.  If you leave it activated with focus it will complete every single time.  With the VBA unlocked, I can flip back to XL and press escape and it will break out into the code at in step mode.  I haven't had enough time to test this to see if the location is consistent, from watching the status updates I very much doubt it is.  This is software that has run all day, every day, for many years and now, after this "upgrade" it poses a real risk to the business. 

I haven't even talked about the herculean struggle to get these new systems with 4 times as much memory to perform with nearly the speed and stability they have in XP/2003.  That struggle has prevented me from having the time to determine whether the code is stalling on the same line, even though I'm nearly certain that it is not.

In fact, I have sometimes run across code which simply stops. Makes no sense. For example I might have something like this but much more complicated & involved:

Sub Test()

A=5

b=5

c=5

End Sub

...and I can confirm it stops after b=5! In single-step mode it doesn't stop, but full speed execution it's the last statement executed. Makes absolutely no sense. Only happens (when it does) in long and involved macros.

My only workaround was to insert several lines of

DoEvents

in random places in the code, and it seemed to fix it.

Also, you're not looking for empties, you're looking for when .Interior.ColorIndex = 100 ceases to be true in the active cell.

Read this code of yours carefully:

Else

ActiveCell.Offset(0, 7).Select

ActiveCell.GoalSeek Goal:=0.05, ChangingCell:=ActiveCell.Offset(0, -7)

ActiveCell.Offset(0, -1).Select

If ActiveCell.Value < 3 Then

ActiveCell.GoalSeek Goal:=3, ChangingCell:=ActiveCell.Offset(0, -6)

ActiveCell.Offset(1, -6).Select

ElseIf ActiveCell.Value > 3 Then

ActiveCell.Offset(1, -6).Select

End If

If ActiveCell.Value = 3, your loop ends with the ActiveCell 6 columns to the right of your users selected range, where .Interior.ColorIndex = 100 is indeed false.

Change ElseIf ActiveCell.Value > 3 to ElseIf ActiveCell.Vaue >= 3 and see if that works for you (it should)

Here's mine:

Sheets("Master").Select

Columns("I:I").Select

Selection.Interior.Color = 100

Set rRange = Application.InputBox("Click the Cell You Would Like to Start the Macro", "Price Minimum", , , , , , 8)

Set sRange = Application.InputBox("Highlight the range you would like the macro to run over", "Price Minimum", , , , , , 8)

Columns("I:I").Select

Selection.Interior.ColorIndex = Blank

sRange.Select

Selection.Interior.Color = 100

rRange.Select

Do While ActiveCell.Interior.Color = 100

If ActiveCell.Offset(0, 19).Value = 1 Then

ActiveCell.Offset(1, 0).Select

ElseIf IsError(ActiveCell.Offset(0, 6)) Then

ActiveCell.Value = ActiveCell.Offset(0, -2).Value

ActiveCell.Offset(1, 0).Select

ElseIf ActiveCell.Offset(0, 15).Value = 0 Then

ActiveCell.Value = ActiveCell.Offset(0, -2).Value

ActiveCell.Offset(1, 0).Select

Else

ActiveCell.Offset(0, 7).Select

ActiveCell.GoalSeek Goal:=0.05, ChangingCell:=ActiveCell.Offset(0, -7)

ActiveCell.Offset(0, -1).Select

If ActiveCell.Value < 3 Then

ActiveCell.GoalSeek Goal:=3, ChangingCell:=ActiveCell.Offset(0, -6)

ActiveCell.Offset(1, -6).Select

ElseIf ActiveCell.Value > 3 Then

ActiveCell.Offset(1, -6).Select

End If

End If

Loop

Columns("I:I").Select

Selection.Interior.ColorIndex = Blank

rRange.Select

MsgBox "Min"

'

End Sub

It jumps to the end and displays the message box. Again sometimes it completes the entire selected range other times it does not.

Hi,

Code doesn't behave randomly, instead it has the irritating habit of doing precisely what you tell it to to even if that wasn't what you meant it to do. Post your code and a sample of the data it is working on. Include the result you expect from that code.

If this post answers your question, please mark it as the Answer.

Mike H