Using a VBA Try/Except Equivalent for If/Else

Question

I am trying to run through some spreadsheet range and use a try/except in order to build an if/else statement. The reason I am doing this is because IsNumeric() is not working for me so I am trying to do something like this (try except formatting from python)

Dim Temp as Integer
Dim Myrange as Range
Dim Myrow as Range
Set Myrange = Range("A1","A1000")
For Each Myrow in Myrange.Row
  If IsEmpty(Range("A" & Myrow.Row)) Then  
    Exit For 'To escape the loop at the end of the filled cells
  Else
    Try:
      Temp = (Myrow.Value() - 0) 'This causes a #VALUE! error when the Myrow.Value is not a number.
    Except:
        Range("B" & Myrow.Row).Value = Temp 'this sets the value of the rightmost cell to whatever current value of Temp is.

I have also tried some other error catching but can't seem to get it in VBA.

For Each Myrow In Myrange.Rows
       If IsEmpty(Range("A" & Myrow.Row)) Then
         Exit For
       Else
           On Error Resume Next
           Temp = Myrow.Value() - 0
           If Err.Number = 0 Then
               Range("A" & Myrow.Row).Value = ""
           ElseIf Err.Number <> 0 Then
               Range("B" & Myrow.Row) = Temp
           End If
       End If
   Next Myrow

I am really just looking to run down the list, see the first number, set value of B0:Bn1 = Temp, when An is hit (new number), The value of Temp changes to temp2 and then cells Bn1+1 -> Bn2-1 is temp2 until a new number is found etc.

in the worksheet I can do it fine with dragging down formula =(A1-0) to see the error message for those that are not numeric but for some reason I can't code it.

Solved this using advice of @MathieuGuindon by using variant type and testing isnumeric on that. Solution code:

    Dim Myrange As Range
    Dim Myrow As Range
    Dim Temp As Variant
    Dim NextTemp As Variant

    Set Myrange = Selection
    For Each Myrow In Myrange.Rows
        NextTemp = Range("A" & Myrow.Row).Value
        If IsEmpty(Range("A" & Myrow.Row)) Then
          Exit For
        ElseIf IsNumeric(NextTemp) Then
            Temp = NextTemp
            Range("A" & Myrow.Row).Value = ""
        Else
            Range("B" & Myrow.Row).Value = Temp
                End If
    Next Myrow

The best error handling is no error handling - if assigning Temp to MyRow.Value throws a type mismatch, read MyRow.Value into a Variant and determine whether IsNumeric(MyRow.Value) before trying to coerce it into an Integer; then you don't need to worry about error handling. — Mathieu Guindon
– Mathieu Guindon, Commented May 13, 2019 at 16:25
Also On Error Resume Next should be paired with an On Error GoTo 0 statement that restores error handling - without it OERN is suppressing all errors in the procedure scope. — Mathieu Guindon
– Mathieu Guindon, Commented May 13, 2019 at 16:26
@MathieuGuindon I was going to ask you for more detail on "The best error handling is no error handling", and then I found you already have a blog post covering it. Helpful for better understanding of that topic. — Robert Todar
– Robert Todar, Commented May 13, 2019 at 16:45

SJR · Accepted Answer · 2019-05-13 16:37:37Z

1

A bit of simplification, and picking up on Mathieu's comments, try this. Not sure what you're doing though so may no be quite right.

Sub x()

Dim Temp As Variant
Dim Myrange As Range
Dim Myrow As Range

Set Myrange = Range("A1", "A1000")

For Each Myrow In Myrange
    If Not IsEmpty(Myrow) Then
        Temp = Myrow.Value - 0
            If IsNumeric(Temp) Then
               Myrow.Value = vbNullString
            Else
                Myrow.Offset(, 1).Value = Temp
            End If
    End If
Next Myrow

End Sub

answered May 13, 2019 at 16:37

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

2 Comments

jmdatasci Over a year ago

This was essentially what I did, thank you. not sure how I can post my code.

SJR Over a year ago

Thanks for accepting my answer. However you can post your own code as an answer if you prefer.

Zack · Accepted Answer · 2019-05-13 16:32:22Z

One way is to have a dedicated error handler at the end of your sub, and check the error code (13 for Type Mismatch):

Option Explicit

Public Sub EnumerateValues()
    On Error GoTo err_handle

    Dim Temp As Integer
    Dim Myrange As Range
    Dim Myrow As Range
    Dim myNumber As Double ' Int? Long?

    Set Myrange = Range("A1", "A1000")

    For Each Myrow In Myrange.Rows
        If IsEmpty(Range("A" & Myrow.Row)) Then
            Exit For ' to escape loop at end of filled cells
        Else
            myNumber = CDbl(Myrow.Value())
            Debug.Print myNumber
        End If
' use label, since VBA doesn't support Continue in loop.
loop_continue:
    Next Myrow

exit_me:
    Exit Sub

err_handle:
    Select Case Err.Number
        Case 13 ' Type Mismatch
            GoTo loop_continue
        Case Else
            MsgBox Err.Description, vbOKOnly + vbCritical, Err.Number
            GoTo exit_me
    End Select
End Sub

This way, if we encounter a value for which CDbl (or the equivalent function) fails, we just continue on to the next row.

user11494081 · Accepted Answer · 2019-05-13 16:54:52Z

While the first example contains Try: and Except: as labels, they provide no error control. Try/Except are vb.net error control methods, not vba.

It's unclear whether you might have text that looks like numbers in column A. If the Temp = (Myrow.Value() - 0) is only meant to determine whether the value in column A is a number and not used as a conversion then SpecialCells can quickly find the numbers in column A.

dim rng as range

on error resume next
'locate typed numbers in column A
set rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
on error goto 0

If not rng is nothing then
    rng = vbNullString
End If

on error resume next
'locate text values in column A
set rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues)
on error goto 0

If not rng is nothing then
    rng.Offset(0, 1) = rng.Value
End If

You can also use xlCellTypeFormulas to return numbers or text returned by formulas.

Collectives™ on Stack Overflow

Using a VBA Try/Except Equivalent for If/Else

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related