View Full Version : IsMissing is not working!

05-27-2006, 09:49 AM
Hi everyone,

I am trying to write a procedure which sets the couponrate to 0 if the user did not enter anything(Optional), and sets the couponrate to the value the user has entered in the inputbox. However, my codes are not working!!http://www.excelforum.com/images/smilies/frown.gif

If I enter, say a 8, it sets couponrate to 8. But if I do not enter anything, it just loops through, and wont set couponrate to the default 0. The IsMissing function doesnt work!!

Please help!!
Thanks in advance!

Sub getcoupon()
Dim couponrate, c As Variant
Dim test As Boolean

'Get the coupon rate of the bond

couponrate = Application.InputBox("Please enter the coupon rate of " & _
"the bond in its per annual percentage term, e.g enter 8 if the coupon " & _
"rate is 8%", "Coupon Rate of the bond", , , , , 1)

c = DetermineCouponRate(couponrate)

Debug.Print c

End Sub

Function DetermineCouponRate(Optional coupon As Variant) As Variant

Dim testt As Boolean

If Not IsMissing(coupon) Then
If coupon >= 0 Then
testt = True
DetermineCouponRate = coupon
Debug.Print coupon
ElseIf coupon < 0 Then
MsgBox "Couponrate needs to be positive", vbCritical, "warning"
testt = False
End If
Loop Until testt
DetermineCouponRate = 0
testt = True
Debug.Print coupon
End If
End Function

05-27-2006, 10:30 AM
I think you can set the default value to 0 for the InputBox instead of checking IsMissing

05-28-2006, 03:41 AM
IsMissing will be true if an optional argument is missing. couponrate is a variable which you are always passing and it is, thus, never missing.

The contents of the variable are not relevant, it is always present. If you want to check the contents, you must do so explicitly - either before or after the call.

I'll leave you to sort out the rest of the code - I presume what you have posted is exemplary and not the real thing.

05-28-2006, 09:42 AM
Cross post on Ozgrid.com probably more too!!!