How can I make my VBA error handling more efficient

user_6291159 0
Viewed 93

I've written this for each loop that gives me the result I am looking for, but when I step through the code it seems to be repeating the error handler, and is therefore running pretty slow. Is there a way to make it run more efficiently?

The reason I needed error handling at all is because it is looping through values from a VLOOKUP and some cells contain an error.

For Each cell In maxStockRange
        On Error GoTo ErrorValue
        If cell.value = "0" Then
            cell.value = ""
        End If
ErrorValue:
            If cell.Text = "#N/A" Then
                cell.value = ""
            End If
            Resume Next
    Next cell
error-handling vba excel foreach performance
3 Answers
Option Explicit

Sub cleanDataUsingErrorHandling()

    Dim cell As Range
    Dim maxStockRange As Range
    
    Set maxStockRange = Sheet1.Range("A1:A4")
    
    For Each cell In maxStockRange
        On Error GoTo ErrorValue
        If cell.Value = "0" Then
            cell.Value = ""
        End If
        GoTo nextIteration
        
ErrorValue:
        If cell.Text = "#N/A" Then
            cell.Value = ""
        End If
        Resume nextIteration
        
nextIteration:
    Next cell
End Sub
user_15714070

Erroring Cells

Why not change the formula?

=IFERROR(IF(VLOOKUP(...)=0,"",VLOOKUP(...)),"")

Think about how you never ever want to have mixed formulas and values in a data column.

' This works for values.
maxStockRange.Replace 0, "", xlWhole
maxStockRange.Replace "#N/A", "", xlWhole

' This also works for formulas.
For Each cell In maxStockRange
    Select Case True
    Case IsError(cell), CStr(cell.Value) = "0"
        cell.Value = ""
    Case Else
    End Select
Next cell
user_98140690

VBasic2008 has already given you a solid solution to your problem, generally speaking it's cleaner and better to check and handle errors with if statements (if possible) and use Error handling for unexpected conditions, With that said, I feel the need to point you to the flaw in your error handling.

'ErrorValue:' is called Label Statement,

Statement labels are used to mark a line of code

That's about sums it up, the use of labels is to point to certain code line

Goto is used to jump to a specific line label (or a line number)

so basically your code logic can be summed as follows:

  1. loop through range
  2. if an error is raised (On Error) then jump (GoTo) the code line which is labeled 'ErrorValue' and execute the code after it
  3. If cell.value = "0" Then cell.value = ""

at this point, you expect that the loop would then continue the next iteration and jump to step 2, but what actually happens is that the code after the label is also executed since the label in itself doesn't halt code (you can think of labels as variables that holds the line number)

Option Explicit

Sub maxStockSub()
On Error GoTo ErrorValue    'you can safely put 'on error' statement after the sub/function defenition

Dim maxStockRange As Range
Dim cell As Range

Set maxStockRange = Range("A1:A10000")

For Each cell In maxStockRange
    If cell.Value = "0" Then
        cell.Value = ""
    ElseIf IsError(cell.Value) Then
        cell.Value = ""
    End If
Next cell

Exit Sub                    'important so that error handling code is only excuted if an error is raised
ErrorValue:
    Debug.Print "#" & Err.Number & ", Desc:" & Err.Description      'here you should handle unexpected errors (like reporting it to the user, or logging it)
    Resume Next
End Sub

Notes:

  1. use option explicit at top of your sub, and define your variables this well make code execution faster
  2. You can check for cell data type with isnumeric(),iserror() and many other function.
  3. You can convert a cell value (number formated as text) to a number with cint(), or cdbl().
user_165167170
Related