Error message

[a2010]

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]

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]

Thanks a lot.

[a2010]

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]

Why would you not put up the error immediately, hy wait until all 3 done?

[a2010]

'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]

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]

No matter what number I put in the inputboxes, it still runs through to the final calculation and then the error comes up.

[mbarron]

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]

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]

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]

Thanks.