Managing typical excel formula errors in vba funct

Question

I'm writing some vba functions. In particular, i'm rewriting the VLOOKUP... In this MY_VLOOKUP, I have two boolean variable : 1. error_range, it's true if the range passed in the formula arguments is invalid 2. not_ava, it's true if there is no result (the VLOOKUP result would be #N/A)

In the error handler section i wrote

Errorhandler:
If error_range Then error_cat = xlErrRef
If not_ava Then error_cat = xlErrNA
Err.Raise error_cat 

but I get a "#VALUE" error in the cell. When debugging, i realised that err.raise doesn't work and generates this "#VALUE" error How can i get a "#N/D" o "#REF" error in the cell?

Answer 1

You can get many errors when using Excel Formulas. They can be like #Ref, #Value,#N/A, #Name etc.

I usually use CVERR() to trap these kind of errors. Here is an example.

Sub Sample()
    Dim Ret As Variant
    Dim error_cat As Long

    On Error GoTo Whoa

    Ret = Application.Evaluate("=VLOOKUP(12,SiddharthRout,1,0)") '<~~ Invalid Range
    'Ret = Application.Evaluate("=VLOOKUP(12,D3:G7,1,0)")   '<~~ No Value found

    Select Case CVErr(Ret)
        Case CVErr(xlErrName): error_cat = xlErrName
        Case CVErr(xlErrNA): error_cat = xlErrNA
        Case CVErr(xlErrRef): error_cat = xlErrRef
        Case CVErr(xlErrValue): error_cat = xlErrValue
    End Select

    If error_cat <> 0 Then Err.Raise error_cat

LetsContinue:
    MsgBox "Phew!"
    Exit Sub
Whoa:
    MsgBox "Error Error Damn Error"
    Resume LetsContinue
End Sub

Answer 2

I paste the code , maybe it will bu useful for someone

(thanks to Siddharth Rout for the help)

If error_range Then 
error_cat = xlErrRef 
Else
If not_ava Then 
error_cat = xlErrNA 
else 
error_cat = xlErrValue 
End If 
end if
my_vlookup = CVErr(error_cat )