Write #REF! in cell

[ErikJan]

I want to write #REF! in a cell using VBA (to indicate an error in a VBA function); how do I do that?

[HansV]

Like this:

Range("A1") = CVErr(xlErrRef)

Other possible error values:

xlErrDiv0: #DIV/0!
xlErrNA: #N/A
xlErrName: #NAME?
xlErrNull: #NULL!
xlErrNum: #NUM!
xlErrValue: #VALUE!

[ErikJan]

It's the return value of a user function, I don't know which cell... WIll try CVErr(xlErrRef)... Was thinking about "Error(a number)" or something like that

Update - it works!

[HansV]

User-defined functions will automatically return an error value in the cell if they fail. If you want to return an error value in specific circumstances, you can use code like this:

Code: Select all

Function MyFunction()
    ...
    If <some condition> Then
        MyFunction = CVErr(xlErrNum)
        Exit Function
    End If
    ...
End Function

If you want to replace the built-in error value with a user-defined return value, use an error handler:

Code: Select all

Function MyFunction(x As Long, y As Long)
    On Error GoTo ErrHandler
    MyFunction = y / x
    Exit Function
ErrHandler:
    MyFunction = "!@#$%^&*"
End Function