VBA: How do I get VBA function to produce an error output when provided inadmissible inputs-CodePudding

Please see the VBA code below. I am looking for a way to produce #error or #value if the inputs into the function are inadmissible. Currently it produces 0. For example if LGDCategory is = something other than Sr. Unsecured Bond the function produces 0. Any thoughts on how to make it produce #error?

Function Default(RiskCategory As String, LGDCategory As String, Duration As Double, Rating As String)
    If RiskCategory = "Corporate" And LGDCategory = "Sr. Unsecured Bond" Then
        If Rating = "AAA" Then
            If SDuration2 >= 1 And SDuration2 < 2 Then
                PDCumulative = (0.000102 - 0) / (Duration_rounded_up - Duration_rounded_down) * (Duration - 1)   0
                DefaultTest = -(1 - (1 - PDCumulative) ^ (1 / Duration)) * 0.61975 * 10000

CodePudding user response：

In general you can return an error with the help of CVErr like that

Function test (inp as string) as Variant

    If inp = "not correct" then
      test = CVErr(xlErrValue)
    Else
      test = UCase(inp)
    End If

End Function

You can use the following Cell Error Values with CVErr

The output of the following test

Sub testtest()
    Debug.Print test("not correct")
    Debug.Print test("Anything correct")
End Sub

will be

Error 2015
ANYTHING CORRECT

In case you use the function in Excel as an UDF you will get #VALUE!

In your case you would probably write code similar to

Function Default(RiskCategory As String, LGDCategory As String, Duration As Double, Rating As String)

    If RiskCategory = "Corporate" And LGDCategory = "Sr. Unsecured Bond" Then
        ' your code here
    Else
        ' error because of wrong input
        Default = CVErr(xlErrValue)
    End If
    
End Function