Optional IsMissing is not working

[korokke]

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
positive value the user has entered in. However, my codes are not
working!!

If I enter, say a 8, the program runs exactly like how i want it to
run. But if I do not enter anything, it just loops through, and wont
set couponrate to the default 0!!

Please help!!
Much appreciation!!


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

 

[Bob Phillips]

Try this

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)
If couponrate = False Then
c = 0
Else
c = DetermineCouponRate(couponrate)
End If
Debug.Print c

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[korokke]

Brilliant!!

Thank you very much, I've been working on that piece of unworking cod
for 2 hours. Thanks for the rescue

 

[korokke]

Since coupon is now set to type variant, it also takes strings....

How do I restrict the user from entering stuff other than numbers?

Thanks again in advance

 

[Bob Phillips]

I think that you had the right idea, you just set the type argument
incorrectly

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", Type:=1)
If couponrate = False Then
c = 0
Else
c = DetermineCouponRate(couponrate)
End If
Debug.Print c

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[korokke]

But if I set inputbox to Type:=1, then the user NEEDS to enter a numbe
when being prompted the inputbox, which means the Optional functio
wont be run at all?

How do I restrict users to enter ONLY numbers, but at the same time i
they do not enter anything, the default value to couponrate will b
zero

 

[Tom Ogilvy]

Try it like this:

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", _
Default:=0, Type:=1)
If couponrate = False Then
c = 0
Else
c = DetermineCouponRate(couponrate)
End If
Debug.Print c

End Sub