Throwing #Value in a UDF written in Excel VBA

D

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?
 
M

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
 
I

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 :
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top