Throwing #Value in a UDF written in Excel VBA

[Dom]

I just wrote a UDF in excel's VBA. If the arguments are wrong, I'd
like to have #Value appear in the cell. Is there a way to throw this
exception in a VBA function?

 

[Maurizio Borrelli]

Il giorno giovedì 21 febbraio 2013 20:11:38 UTC+1, Dom ha scritto:

Hi,
try

Public Function m(a)
'...
m = CVErr(xlErrValue)
End Function

 

[isabelle]

hi Dom,

one possibility is to declare the arguments of type Optional

Sub test()
MyFonction Arg1:=0, Arg2:=56765765
MyFonction Arg1:=453
MyFonction Arg2:=56765765
MyFonction
End Sub


Function MyFonction(Optional Arg1 As Variant, Optional Arg2 As Variant)
As Variant
MsgBox "Arguments manquants:" & vbCrLf & _
IsMissing(Arg1) & " / " & IsMissing(Arg2)
End Function

each argument can be verified by type
TypeName
VarType
IsDate
IsNumeric
IsEmpty
IsMissing
IsArray
IsObject
IsNull

isabelle

Le 2013-02-21 14:11, Dom a écrit :

 

[Dom]

hi Dom,

one possibility is to declare the arguments of type Optional

Sub test()
     MyFonction Arg1:=0, Arg2:=56765765
     MyFonction Arg1:=453
     MyFonction Arg2:=56765765
     MyFonction
End Sub

Function MyFonction(Optional Arg1 As Variant, Optional Arg2 As Variant)
As Variant
     MsgBox "Arguments manquants:" & vbCrLf & _
         IsMissing(Arg1) & " / " & IsMissing(Arg2)
End Function

each argument can be verified by type
      TypeName
       VarType
       IsDate
       IsNumeric
       IsEmpty
       IsMissing
       IsArray
       IsObject
       IsNull

isabelle

Le 2013-02-21 14:11, Dom a crit :




- Show quoted text -

Thanks, Maurizio, Exactly what I wanted.

 

[Maurizio Borrelli]

Il giorno giovedì 21 febbraio 2013 20:47:26 UTC+1, Dom ha scritto:

YW!