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.

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.

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 © 2019 vBulletin Solutions Inc. All rights reserved.