CODEWITHSUNDEEP
vba
user1093111
user1093111

Reputation: 1111

Custom error handling vba

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?

Upvotes: 0

Views: 103

Answers (2)

Scott Craner
Scott Craner

Reputation: 152515

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

Upvotes: 3

Chronocidal
Chronocidal

Reputation: 7951

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

Upvotes: 2

Related Questions