Hi,

Can anyone identify the error in my looping? My IsNumber(f) is not
working. If I enter a string, an error message will pop up, but if I
enter a number (e.g 4 or -4), the same error message pops up too...what
can I do?

I cant really set the inputbox to Type:=1, because I want this inputbox
to be optional to the user, i.e If user does not enter anything, it will
return a 2.

any help will be much appreciated!

Thanks!


Code:
--------------------
Sub frequency2()


'Get the frequency of coupon payments per year

frequency = Application.InputBox("Please enter the frequency of the coupon payments", _
"Frequency of the coupon payments", , , , , 1)

'If c = 0 Then 'If it is a zero-coupon bond, set the frequency of coupon payments to 0
'frequency1 = 0
'Debug.Print frequency1
'ElseIf frequency = False Then
'frequency1 = 0
'Else
frequency1 = determinefrequency(frequency)
Debug.Print frequency1
'End If
End Sub

' This function automatically sets the value 2 to the Frequency of Coupon Payment of the Bond if the user
' did not enter anything when prompted.
' It also validates if the user entered negative numbers
Function determinefrequency(Optional ByVal f As Variant) As Variant

Dim testt As Boolean

Do
' If values have been entered into the inputbox and
' the userinput is a number
If Len(f) 0 And WorksheetFunction.IsNumber(f) Then


Do
'if frequency of coupon payment is valid
If f = 0 Or f = 1 Or f = 2 Or f = 4 Then
testt = True
determinefrequency = f
Debug.Print f

'if frequency of coupon payment is negative
ElseIf f < 0 Then
testt = False
MsgBox "Frequency of coupon payment needs to be positive", vbCritical, "Warning"

f = Application.InputBox("Please enter the frequency of the coupon payments", _
"Frequency of the coupon payments", , , , , 1)

'if frequency of coupon payment is not equal to 0, 1, 2 or 4
Else
testt = False
MsgBox "Frequency of coupon payments needs to be 0 or 1 or 2 or 4", vbCritical, "Warning"

f = Application.InputBox("Please enter the frequency of the coupon payments", _
"Frequency of the coupon payments", , , , , 1)
End If
Loop Until testt
ElseIf Len(f) 0 And Not WorksheetFunction.IsNumber(f) Then 'If text has been entered
testt = False
MsgBox "Frequency of coupon payments is invalid", vbCritical, "Warning"
f = Application.InputBox("Please enter the frequency of the coupon payments", _
"Frequency of the coupon payments", , , , , 1)
'End If
'Loop Until testt

' if values have been entered into the inputbox but
' the user input is not a number
'ElseIf Len(f) 0 And Application.IsNumber(f) = False Then
'MsgBox "Frequency of coupon payment needs to be positive", vbCritical, "Warning"

'f = Application.InputBox("Please enter the frequency of the coupon payments", _
'"Frequency of the coupon payments", , , , , 1)
Else
determinefrequency = 2
testt = True
End If
Loop Until testt

End Function
--------------------


--
korokke
------------------------------------------------------------------------
korokke's Profile: http://www.excelforum.com/member.php...o&userid=34760
View this thread: http://www.excelforum.com/showthread...hreadid=546249