Forum Discussion
How to return #Value! or #Name? from VBA?
How can I return the standard spreadsheet errors #Value! or #Name? from a VBA-function to the calling Cell?
With the following little function doSomething one can write the formula
=doSomething(A1)
into some cell and it produces any error when its number is entered into cell A1. But I cannot find the numbers for those standard Excel errors.
Function doSomething(nbr As Long) As Variant
doSomething = True
On Error GoTo handleError
If nbr > 0 Then Err.Raise nbr, "doSomething"
Exit Function
handleError:
doSomething = Err.Description
End Function
BTW: I cannot find documentation about the meaning of all those error numbers. Can someone point me to a reference list?
Now I found it: You have to use
' use nbr = xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum , xlErrRef, xlErrValue doSomething = CVErr(nbr)If called from a spreadsheet this produces:
#DIV/0!, #NA, #Name!, etc.
4 Replies
Now I found it: You have to use
' use nbr = xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum , xlErrRef, xlErrValue doSomething = CVErr(nbr)If called from a spreadsheet this produces:
#DIV/0!, #NA, #Name!, etc.
Yes, that is what the links I posted show...
You can use the CVErr function in combination with the Cell Error Values such as xlErrDiv0 and xlErrNA.
I knew CVErr but do not know how to produce #Name? or #Value! with it.
I changed my doSomething program a bit. For negative numbers it produces CVErr(Abs(nbr)) now. For 0 it produces True.
Function doSomething(nbr As Long) As Variant doSomething = True If nbr < 0 Then doSomething = CVErr(Abs(nbr)) ElseIf nbr > 0 Then On Error GoTo handleError Err.Raise nbr, "doSomething" End If Exit Function handleError: doSomething = Err.Description End Function
