Custom error handling vba

Question

I have a cell that is a brutal nested if and if error. I don't want to have to paste my vlookup 5 times in the formula. Instead, I'd like to have a macro check the errors. The special case is 0 or 1.

I wrote this function to get the value of the vlookup.

Public Function ValidateRealization(value As String)
Dim validate As String
If value = "#N/A" Or value = "#VALUE!" Or value or = "#REF!" Or value = "#DIV/0!" Or value = "#NUM!" Or value = "#NAME?" Or value = "#NULL!" Or value = "0" Or value = "1" Then
    validate = ""
Else
    validate = value 
End If


ValidateRealization = validate
End Function

However, I know it can be improved for one when I set the value, the formatting as a percentage is overridden.

Is there a better way to do this? Maybe by getting the active cells range, and setting the value property?

What is ervalue?

dwirony
– dwirony

2018-03-27 15:37:31 +00:00
Commented Mar 27, 2018 at 15:37 — dwirony
– dwirony, Commented Mar 27, 2018 at 15:37
Oop, my mistake. All the values used to be error

user1093111
– user1093111

2018-03-27 15:39:08 +00:00
Commented Mar 27, 2018 at 15:39 — user1093111
– user1093111, Commented Mar 27, 2018 at 15:39

Scott Craner · Accepted Answer · 2018-03-27 16:19:23Z

3

You are bringing in a string and not a value, so the return is a string.

set the parameter as variant and test for error:

Public Function ValidateRealization(value) As Variant
Dim validate As Variant

If Not IsError(value) Then
    If value = 0 Or value = 1 Then
        validate = ""
    Else
        validate = value
    End If
Else
    validate = ""
End If


ValidateRealization = validate
End Function

Which can be simplified to:

Public Function ValidateRealization(value) As Variant
ValidateRealization = ""
 If Not IsError(value) Then
    If Not (value = 0 Or value = 1) Then
        ValidateRealization = value
    End If
End If
End Function

edited Mar 27, 2018 at 16:19

answered Mar 27, 2018 at 15:40

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

7 Comments

Chronocidal Over a year ago

@dwirony No, IsError will accept a Variant.

dwirony Over a year ago

@Chronocidal Yes sorry but my point is it looks like he was bringing in value as a string before. He'll need to make sure the argument is a range or a string of #N/A won't return True?

Chronocidal Over a year ago

Ah - yes, OP was, but that's why Scott's removed the As String from the argument definition.

dwirony Over a year ago

@Chronocidal Oops okay I've got it now... I messed up in my own testing.

user1093111 Over a year ago

Both these answers are great. Thanks for your help.

|

Chronocidal · Accepted Answer · 2018-03-27 15:47:41Z

2

2 points: first, you have ervalue which is undefined. Go to the top of your module, and add the line Option Explicit. Then go "Tools" > "Options..." and tick "Require Variable Declaration".

Secondly: Why not accept a Variant instead of a String, and use IsError?

Public Function ValidateRealization(value As Variant) As Variant
    If IsError(value) Or CStr(value) = "0" Or CStr(value) = "1" Then
        ValidateRealization = ""
    Else
        ValidateRealization = value
    End If
End Function

{EDIT} If you have converted your value to a String before passing it, then use Select Case

Public Function ValidateRealization(value As Variant) As Variant
    If IsError(value) Then
        ValidateRealization = ""
    Else
        Select Case CStr(value)
            Case "#N/A", "#VALUE!", "#REF!", "#DIV/0!", "#NUM!", "#NAME?", "#NULL!", "0", "1"
                ValidateRealization = ""
            Case Else
                ValidateRealization = value
        End Select
    End If
End Function

edited Mar 27, 2018 at 15:47

answered Mar 27, 2018 at 15:40

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

3 Comments

user1093111 Over a year ago

I have not converted it to a string. It would seem if I would, then I loose my formatting options (it's a percentage now).

user1093111 Over a year ago

Converting the errors to strings seemingly work best. Not sure why, but IsError is not catching the error at the IF. For that you get the vote.

Chronocidal Over a year ago

I've just been through and tested all of the errors, and IfError recognised them all from Range.Value - it may be an issue with what you are passing to the Function?

Collectives™ on Stack Overflow

Custom error handling vba

2 Answers 2

7 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related