Excel VBA: Error Handling Breaking Mid-Code

Question

Code loops through a massive amount of data performing calculations. Goes well for first two workbooks. Third workbook, suddenly Error Handling breaks--no longer works. Ideas on why?

1) Break on Unhandled Errors is correctly marked in options

2) Each Error Handling is followed by On Error GoTo 0

3) This breaks in On Error Resume Next AND in On Error GoTo ErrHandler.

I thought OERN disregards any other error handling anyway?

Here is the lengthy code. I took out several of the variable definitions to shorten it.

amount = lastcolumn / 6
totalstrikes = 0
Do Until amount = 0
    currentcolumn = amount * 6 - 5
    i = 2
Do Until Sheets("Data").Cells(i, currentcolumn).Value = ""
    currentminute = Sheets("Data").Cells(i, currentcolumn).Value
    If oldminute <> 0 Then
    On Error GoTo ErrHandler
        If WorksheetFunction.MRound(currentminute - oldminute, 1 / 86400) >= 0.0007 Then
            'Do Stuff
        End If
5        End If
    On Error GoTo 0
        Do Until Sheets("Data").Cells(i, currentcolumn) <> currentminute
        If InStr(1, hitlist, Sheets("Data").Cells(i, currentcolumn + 1).Value) = False Then
            totaltime = totaltime + CSng(Sheets("Data").Cells(i, currentcolumn + 4).Value)
            totaltotal = totaltotal + CSng(Sheets("Data").Cells(i, currentcolumn + 2).Value)
        End If
        i = i + 1
    Loop
    On Error Resume Next
    If totaltime / totaltotal <= failuretime Then
        Strike = 1
    Else
        Strike = 2
    End If
    On Error GoTo 0
    If minute1 = 0 Then
        'do stuff with the minutes
    End If
    oldminute = currentminute
Loop
amount = amount - 1
Loop
Exit Sub
ErrHandler:
If WorksheetFunction.MRound((-1 * (currentminute - oldminute)), 1 / 86400) >= 0.0007 Then
    Resume Next
Else
    GoTo 5
End If
End Sub

Thanks in advance.

As a rule of thumb, if you have that many On Error statements in a function this small, you need to consider refactoring your code. Over-reliance on GoTo and Resume leads to spaghetti code, difficult to read and maintain. — David Zemens
– David Zemens, Commented Jul 30, 2018 at 17:56

Tim Williams · Accepted Answer · 2018-07-30 18:24:46Z

6

You can do it this way and manage your run-time errors:

If WorksheetFunction.MRound(currentminute - oldminute, 1 / 86400) >= 0.0007 Then
    'Do Stuff
End If

...or without needing to manage run-time errors, by dropping WorksheetFunction and instead testing the function's return value for an error:

Dim m
m = Application.MRound(currentminute - oldminute, 1 / 86400)
If IsError(m) Then
    m = Application.MRound((-1 * (currentminute - oldminute)), 1 / 86400)
End If

edited Jul 30, 2018 at 18:24

answered Jul 30, 2018 at 17:39

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

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

1 Comment

user8221156 Over a year ago

Does it not bother anyone that VBA has no "Mround" function? That the correct syntax is Application.Worksheetfunction.Mround(... or just Worksheetfunction.Mround(..., but not Application.Mround(... ?

Collectives™ on Stack Overflow

Excel VBA: Error Handling Breaking Mid-Code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related