Consulting

Results 1 to 12 of 12

Thread: Error message

  1. #1
    VBAX Regular
    Joined
    Feb 2010
    Posts
    12
    Location

    Error message

    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.

  2. #2
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    Use an error handler.
    [vba]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[/vba]

  3. #3
    VBAX Regular
    Joined
    Feb 2010
    Posts
    12
    Location
    Thanks a lot.

  4. #4
    VBAX Regular
    Joined
    Feb 2010
    Posts
    12
    Location
    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.

    [vba] 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
    [/vba]

    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.

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Why would you not put up the error immediately, hy wait until all 3 done?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Regular
    Joined
    Feb 2010
    Posts
    12
    Location
    '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.

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    But the error can occur on the first input, so to my mind, that is what hyou should trap and should report it there.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Regular
    Joined
    Feb 2010
    Posts
    12
    Location
    No matter what number I put in the inputboxes, it still runs through to the final calculation and then the error comes up.

  9. #9
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    I cannot break it. It runs through - displays the monthly amount and then ends. What numbers are you using when you get the error?

  10. #10
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    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.

    [vba]
    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[/vba]

  11. #11
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    If you to want wait until the end before letting them know there is an error:
    [vba]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[/vba]
    Last edited by mbarron; 03-03-2010 at 10:37 AM.

  12. #12
    VBAX Regular
    Joined
    Feb 2010
    Posts
    12
    Location
    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •