Para erros, use IsError()
( #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!
)
Para verificar se os parâmetros contêm uma única célula, use TypeName(a) = "Range"
e CountLarge > 1
Option Explicit
Public Function MyAdd(ByVal a As Variant, ByVal b As Variant) As Long
If TypeName(a) = "Range" Then If a.Cells.CountLarge > 1 Then Set a = a.Cells(1)
If TypeName(b) = "Range" Then If b.Cells.CountLarge > 1 Then Set b = b.Cells(1)
If IsError(a) Then
a = 0
Else
a = IIf(Not IsNumeric(Val(a)), 0, Val(a))
End If
If IsError(b) Then
b = 0
Else
b = IIf(Not IsNumeric(Val(b)), 0, Val(b))
End If
MyAdd = a + b
End Function