Macro that stops at a mid point occasionally

[Ed]

Hi
This Macro almost always runs to completion.
But occasionally will stop at some mid point.
Sometimes stops at the same mid point.
I have reentered the lines at the stopping point.
This has not helped.
Do you know of anything to look for or do?
Are there any dummy lines that could be entered that would
possibly help or correct this?
Or is this just an uncorrectible computer problem?
What is your thinking on this?
Thankyou
Ed

 

[Dave Peterson]

I don't think that you shared enough for any real help.

But when my macros stop, it's usually because of my code and my data (in the
cells).

Sometimes, I'll look for a value and assume that it'll be numeric and it isn't
(crash!).

Sometimes, I'll look at the value of a cell and it'll contain an error (crash!).

If you can find the line that's crashing, maybe you can see what it's looking
at. Then go back to check your data.

 

[Ed]

Dave
It stopped in this loop:

Do Until amtWon = 0
If amtWon = 0 Then GoTo Line177
amtWon = amtWon - 1
accumTotal = accumTotal + 1
Worksheets("abc").Range("bl16").Value = amtWon
Worksheets("abc").Range("bl17").Value = accumTotal
Worksheets("abc").Range("zf16,u17").Calculate
Loop

I also have had occasions in other macros where it would
stop on lines where a comparison is being made.
Such as:

If abcd > efg Then (Goto Line10)

In all cases, they basicly work great.
Just once in a while they stop.
This tells me, it must be written correctly because it
works most of the time.
Just occasionally, something is messing it up.
Am I thinking correctly?
If for some unknown reason a cell is drifting from numeric
to otherwise, could a line be put in to guarantee it
staying numeric or putting it back to numeric just before
it is used?
If so, what would that line be?
Just trying to think outside the box.
Thankyou
Ed

 

[N10]

This may be off the MArk but as far as I know and Im willing to be corrected
but it seems to me that

GoToLine177 is therefore perhapes not a correct Excell VBA command
therefore such will hang or generate an error

As far as I know Excel modules and proceedures dont have line numbers

The nearsest thing I know of is the case command ?

Hope this helps

N10

 

[Tom Ogilvy]

Sub TestGoto()
msgbox "Before Goto"
goto Line177

Msgbox "before Line177"

Line177:

Msgbox "after Line177"

End Sub

Line177 is a label the way he has it set up and it is legal.

 

[Tom Ogilvy]

And for completeness, VBA does support line numbers:

Sub TestGoto()
MsgBox "Before Goto"
GoTo 177

MsgBox "before Line 177"

177 MsgBox "Line 177"

End Sub

 

[Dave Peterson]

You could check for valid stuff with something like:

Dim abcd As Variant
abcd = Worksheets("sheet1").Range("a1").Value
If IsError(abcd) Then
MsgBox "an error"
ElseIf IsNumeric(abcd) Then
MsgBox "numeric)"
Else
MsgBox "not numeric"
End If


====

And this line wouldn't even compile for me:

If abcd > efg Then (Goto Line10)
(if I dropped the parentheses, it did.)

=========

Do you use user defined functions? Sometimes if there's an unhandled error in
one of these, it can cause macros to stop.

And just curious about Line177. If that's just used to exit the loop, why not:

if amtwon = 0 then exit do

About the only time I use Goto's to branch somewhere else is when I'm branching
to my clean up code at the end of a procedure.

sub aaa()
application.screenupdating = false
application.calculation = ...

'do a bunch of stuff
'decide to exit early

if amtwon = 0 then goto ExitNow:

'lots more code

exitNow:
application.screenupdating = true
application.calculation = ...

End Sub

Lots of people don't like GoTo's anyplace. I don't mind branching to an exit
routine.

 

[N10]

Thanks Tom

I didnt know that, the fact is a bonus I'll check it out

N10