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.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.