PDA

View Full Version : IsNumber is not working



juju
05-28-2006, 02:18 AM
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!

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

Bob Phillips
05-28-2006, 03:19 AM
Cross-posting

http://www.ozgrid.com/forum/showthread.php?t=51414
http://www.excelforum.com/showthread...hreadid=546249 (http://www.excelforum.com/showthread.php?threadid=546249)