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!
[VBA]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
[/VBA]
Reply With Quote
