juju
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
Do
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
Else
DetermineCouponRate = 0
testt = True
Debug.Print coupon
End If
End Function
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
Do
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
Else
DetermineCouponRate = 0
testt = True
Debug.Print coupon
End If
End Function