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

try isnumeric

Isn't this the same problem that you asked and got answered yesterday?

--
HTH

Bob Phillips

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

"korokke" wrote in
message ...

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

Sub ABC()
Dim Frequency As String
Dim msg As String
msg = "Please enter the frequency of the coupon payments" & _
vbNewline & "(should be 2, 4, 6, or 8)"
TryAgain:
Frequency = InputBox(msg, _
"Frequency of the coupon payments")
If StrPtr(Frequency) = 0 Then
' User hit Cancel, so exit
Exit Sub
End If
' use hit OK with no entry or entered something
If Frequency < "" Then
' user entered something
If Not IsNumeric(Frequency) Then
' entry isn't numeric
msg = "Please enter a number or nothing"
GoTo TryAgain
Else
If Frequency <= 0 Then
' entry is not positive
msg = "Frequency should be positive"
GoTo TryAgain
ElseIf Frequency 8 Then
' entry is greater than 8
msg = "Frequency should be 2, 4, 6, or 8"
GoTo TryAgain
ElseIf Frequency Mod 2 < 0 Then
' entry isn't divisible by 2
msg = "Frequency should be 2, 4, 6, or 8"
End If
End If
Else
' user hit OK with no entry
Frequency = 2
End If

End Sub

--
Regards,
Tom Ogilvy

"korokke" wrote in
message ...

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