Forum Discussion
Solved
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 =doSomet...
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.
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