+ Reply to Thread
Results 1 to 14 of 14

Error handling in VBA

  1. #1
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Error handling in VBA

    Hi I made a macro below for my own use (source file attached).

    I can see the statements temp1 = 1 / vMetric(j) caused some problem there. But I don't know why my error handling failed to work. I can see that cell A4 made the macro crash due to 1/0 error. Is there a good way to fix the problem?


    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Error handling in VBA

    The handler should go at the top of your code so as to catch any error.

    Please Login or Register  to view this content.

  3. #3
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Error handling in VBA

    Your error handling isn't quite right...

    Try:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Error handling in VBA

    You need to tell VBA what to do BEFORE the error occurs (- ie before the div by zero happens)

    In this case I would deal with it differently.
    Rather than dealing with this under error handling, how about telling VBA what to do if either vMetric(j) or vWeight(j) contain an invalid value

    something like:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: Error handling in VBA

    Hi everyone,

    I really appreciate the many constructive replies by your guys. It is great.

    I always like to write array formulas in Excel something like =PERCENTILE(IF(ISERROR(1/A1:A50/B1:B50),"",B1:B50),0.2). So the code of Olly allows me to do this similarly in VBA. It looks great. Thank you.

    The idea of kev_ also works. Instead of forcing an error, the code tests and tries to avoid the error. Error handling is no so easy in VBA.

    Thanks again, everyone.

  6. #6
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: Error handling in VBA

    Hi Olly, I attempted to shorten the code by moving the Error handler inside the loop. But now it failed. Why this was wrong?

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Error handling in VBA


    Hi !

    Or just test the value as non zero before to use it as a divider …

  8. #8
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: Error handling in VBA

    Hi everyone, can you please find why the error handler below is wrong:

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: Error handling in VBA

    the above macro will fail Is it because the statment is disabled? On Error GoTo ErrorHandler

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Error handling in VBA


    It may depend on which codeline error occurs ? …

  11. #11
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Error handling in VBA

    To keep going after an error occurs, you need to use the RESUME statement.

    Not sure why you're trying to further simplify, beyond the working version I posted?

  12. #12
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: Error handling in VBA

    Thanks, Olly. That is really a useful code.

  13. #13
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: Error handling in VBA

    Hi Olly, sorry to bother.

    For your error handling code below, the statement Err.Clear is not neede, as the Resume statement will automatically clear it. Is this right? There was a link to this:

    HTML Code: 
    Please Login or Register  to view this content.

  14. #14
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Error handling in VBA

    Yes, that's right - you could lose the line:
    Please Login or Register  to view this content.
    I tend to include it as sometimes you need to handle further errors in your error handling code, before resuming / exiting! but in your case, you can safely delete that line.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Error handling inside error handling
    By grantastley in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-06-2015, 03:43 AM
  2. RUN TIME ERROR '1004' error handling help needed
    By skop89 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2014, 12:54 PM
  3. [SOLVED] Error Handling: Creating code to display error messages
    By Student1990 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-01-2013, 02:21 PM
  4. Error Handling: Can it report line # where error occurred?
    By PingPing in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2010, 03:47 AM
  5. [SOLVED] On Error Goto (Global Error Handling)
    By mpeplow in forum Excel General
    Replies: 3
    Last Post: 04-10-2007, 11:33 PM
  6. Error Handling - On Error GoTo doesn't trap error successfully
    By David in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-16-2006, 02:10 PM
  7. [SOLVED] Error handling with a handling routine
    By ben in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2005, 11:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1