PDA

View Full Version : Error message



a2010
03-02-2010, 11:35 AM
Ive written a VBA program that does a few calculations and includes InputBoxes. It works fine when I input realistic values, however if I use extreme values (i.e. very very large numbers), an error message appears.

The error message says Run time error 6, overflow.

So how would I go about converting that error message into a custom error message saying "Please use realistic numbers" or something similar, and then going back to the start of the Sub.

Thanks.

mbarron
03-02-2010, 11:55 AM
Use an error handler.
Sub TooBig()
Dim something As Integer
inputbox:
On Error GoTo handler
something = Application.inputbox("hi")
MsgBox "I like that number. Thank you"
On Error GoTo 0

Exit Sub
handler:
If Err.Number = 6 Then
MsgBox "The number you've entered is waaaaaaaaaaay too large" & vbLf _
& "Please try again"
GoTo inputbox
End If
End Sub

a2010
03-02-2010, 01:13 PM
Thanks a lot.

a2010
03-03-2010, 03:16 AM
Ok I'm having a bit of trouble implementing this code into my program. Ive got three input boxes that follow on from one another, and then a calculation is made at the end and displayed using a message box.

Private Sub Calculate_Monthly_Repayments_Click()
Dim y As Variant
Dim r As Variant
Dim n As Variant
Dim ratio As Double
Dim calc1 As Double
Dim calc2 As Double
Dim calc3 As Currency
Do
y = Application.inputbox("Please enter the loan that is to be paid off", "Loan amount")
If TypeName(y) = "Boolean" Then
Exit Sub
ElseIf y <= 0 Then
Else
Exit Do
End If
Loop
Do
r = Application.inputbox("Now input the annual interest rate to be used")
If TypeName(r) = "Boolean" Then
Exit Sub
ElseIf r <= 0 Then
MsgBox "Please enter a positive value."
ElseIf Not IsNumeric(r) Then
MsgBox "Please enter a numeric value."
Else
Exit Do
End If
Loop
Do
n = Application.inputbox("Finally, enter the number of years in which the loan needs to be paid off")
If TypeName(n) = "Boolean" Then
Exit Sub
ElseIf n <= 0 Then
MsgBox "Please enter a positive value."
ElseIf Not IsNumeric(n) Then
MsgBox "Please enter a numeric value."
Else
Exit Do
End If
Loop
ratio = (r / 100) + 1
calc1 = y * (ratio ^ n)
calc2 = ((ratio ^ n) - 1) / (ratio - 1)
calc3 = calc1 / (calc2 * 12)
MsgBox ("The monthly payment required to pay off the loan is £") & Round(calc3, 2)

End Sub


So all 3 inputboxes have to be used before the error message comes up, and then it should restart from the beginning of the 1st InputBox.

Bob Phillips
03-03-2010, 04:00 AM
Why would you not put up the error immediately, hy wait until all 3 done?

a2010
03-03-2010, 06:08 AM
'Cos the runtime error only appears after making the calculation after the 3 values have been input. So instead of the MsgBox stating the answer to the calculation, it says the error. At this point, if the runtime error comes up, I need a message saying try again and then taking the user back to the first box.

Bob Phillips
03-03-2010, 06:10 AM
But the error can occur on the first input, so to my mind, that is what hyou should trap and should report it there.

a2010
03-03-2010, 07:36 AM
No matter what number I put in the inputboxes, it still runs through to the final calculation and then the error comes up.

mbarron
03-03-2010, 08:21 AM
I cannot break it. It runs through - displays the monthly amount and then ends. What numbers are you using when you get the error?

mbarron
03-03-2010, 08:29 AM
I forgot that it was an overflow error from the values being too large. I would put a check at each step and force reasonable entries for each input box.


Private Sub Calculate_Monthly_Repayments_Click()
Dim y As Variant
Dim r As Variant
Dim n As Variant
Dim ratio As Double
Dim calc1 As Double
Dim calc2 As Double
Dim calc3 As Currency
Do
y = Application.InputBox("Please enter the loan that is to be paid off", "Loan amount")
If TypeName(y) = "Boolean" Then
Exit Sub
ElseIf y <= 0 Then
MsgBox "Please enter a positive value."
ElseIf y > 10 ^ 10 Then
MsgBox "Sorry, your loan amount is too high"

Else
Exit Do
End If
Loop
Do
r = Application.InputBox("Now input the annual interest rate to be used")
If TypeName(r) = "Boolean" Then
Exit Sub
ElseIf r <= 0 Then
MsgBox "Please enter a positive value."
ElseIf r > 100 Then
MsgBox "Please enter a value less than 100."
ElseIf Not IsNumeric(r) Then
MsgBox "Please enter a numeric value."
Else
Exit Do
End If
Loop
Do
n = Application.InputBox("Finally, enter the number of years in which the loan needs to be paid off")
If TypeName(n) = "Boolean" Then
Exit Sub
ElseIf n <= 0 Then
MsgBox "Please enter a positive value."
ElseIf n > 60 Then
MsgBox "Do you really want to burden your grandchilren with this debt?"
ElseIf Not IsNumeric(n) Then
MsgBox "Please enter a numeric value."
Else
Exit Do
End If
Loop
ratio = (r / 100) + 1
calc1 = y * (ratio ^ n)
calc2 = ((ratio ^ n) - 1) / (ratio - 1)
calc3 = calc1 / (calc2 * 12)
MsgBox ("The monthly payment required to pay off the loan is £") & Round(calc3, 2)

End Sub

mbarron
03-03-2010, 08:33 AM
If you to want wait until the end before letting them know there is an error:
Private Sub Calculate_Monthly_Repayments_Click()
Dim y As Variant
Dim r As Variant
Dim n As Variant
Dim ratio As Double
Dim calc1 As Double
Dim calc2 As Double
Dim calc3 As Currency

startOver:
Do
y = Application.InputBox("Please enter the loan that is to be paid off", "Loan amount")
If TypeName(y) = "Boolean" Then
Exit Sub
ElseIf y <= 0 Then
Else
Exit Do
End If
Loop
Do
r = Application.InputBox("Now input the annual interest rate to be used")
If TypeName(r) = "Boolean" Then
Exit Sub
ElseIf r <= 0 Then
MsgBox "Please enter a positive value."
ElseIf Not IsNumeric(r) Then
MsgBox "Please enter a numeric value."
Else
Exit Do
End If
Loop
Do
n = Application.InputBox("Finally, enter the number of years in which the loan needs to be paid off")
If TypeName(n) = "Boolean" Then
Exit Sub
ElseIf n <= 0 Then
MsgBox "Please enter a positive value."
ElseIf Not IsNumeric(n) Then
MsgBox "Please enter a numeric value."
Else
Exit Do
End If
Loop
On Error GoTo handler

ratio = (r / 100) + 1
calc1 = y * (ratio ^ n)
calc2 = ((ratio ^ n) - 1) / (ratio - 1)
calc3 = calc1 / (calc2 * 12)
MsgBox ("The monthly payment required to pay off the loan is £") & Round(calc3, 2)

Exit Sub

handler:
If Err.Number = 6 Then
MsgBox "One of your enties is invalid, please try again", vbOKOnly + vbCritical
GoTo startOver
End If

End Sub

a2010
03-03-2010, 09:12 AM
Thanks.