Excel VBA Introduction Part 19 – Error Handling (On Error, Resume, GoTo)

47
6



If you’d like to help fund Wise Owl’s conversion of tea and biscuits into quality training videos you can click this link to make a donation. Thanks for watching!

You can buy our Introduction to Excel VBA book here

By Andrew Gould

– Almost every procedure you write in VBA has the potential to go wrong at some point! Writing error handling code allows your procedures to fail gracefully instead of dumping the user out into the run time error dialog box. This video teaches you all about the On Error statement including how to ignore errors, how to trigger a custom error handler and how to resume running your code after an error has occurred. Towards the end of the video you’ll also encounter the Err object which allows you to interrogate the error that has occurred and even raise your own custom errors.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!

Nguồn: https://blockchainvietnam.vn

Xem thêm bài viết khác: https://blockchainvietnam.vn/tong-hop/

47 COMMENTS

  1. mmm I ran into a funny scenario here. I'm not sure why. I ran the On Error GoTo CreateSheet45… which worked fine it created the sheet, then it jumped to a Function from video part 18 "Function CustomDate(DateToFormat As Date, Optional IncludeTime As Boolean = False) As String" very strange. my code is identical to yours?

  2. Wise Owl, thank You for all Your VBA videos, You are truly very wise. I have a weird problem which happened a few times now, if You would be so kind as to help me. Excuse me for the formatting, but for some reason YouTube changes it every time . I wrote a similar code like You:
    Sub ErrorsInVBA()
       
        On Error Resume Next
       
        Application.DisplayAlerts = False
       
        Worksheets("Sheet42").Delete
       
        Application.DisplayAlerts = True
       
    End SubIf I don't have a "Sheet42" this all runs fine and dandy. But, if I do have a "Sheet42", stepping through the code I get to Worksheets("Sheet42").delete, then I press f8 and I am transported to my Creating Functions pt1 module, to a function CustomDatabase, which You covered in an earlier video.                                                                                                                                                                                                                                                                                                     Function CustomDate(DateToFormat As Date, Optional IncludeTime As Boolean = False) As String
        'Boolean = false means that if the user doesn't type in data _
        the default value would in this case be false; can be true
        If IncludeTime Then
            CustomDate = Format(DateToFormat, "dddd dd mmmm yyyy hh:mm:ss")
        Else
            CustomDate = Format(DateToFormat, "dddd dd mmmm yyyy")
        End If
       
    End Function
    This complete function runs, which means it ends, and than I am transported back to ErrorHandling module, to Application.DisplayAlerts = True.This happened already a few times with different subroutines but I ignored it. Now it seems that it will keep bugging me. Do you know what's the problem here?Kind regards,Fran

  3. I’ve searched online for a while looking for explanations that are clear and comprehensive and finally I found them in this video. Thank you for publishing this, this has really helped me write better code for my projects!

  4. @WiseOwlTutorials, May you tell me, is VBA stil relevant as a tool in different nowadays tasks and in demand by IT-companies?

  5. me ha sido de mucha utilidad el video para realizar un codigo en el control de errores en unos de mis desarrollos me costo trabajo entender la estructura pero gracias a ti logre implementarlo correctamente

  6. Thank you Andrew, you are life saver. Your vedios help me to complete my task. Whenever I face issue, I approach to your vedios. They are really helpful. I really greatful 😊

  7. Excellent teaching skills you have. Great to see your videos. One small query, you used err number with if stmt to show msg, but how to find the list of err numbers ? Do we need to see practically or any error list available in vba. thanks.

  8. Brilliant! – found exactly what I was looking for, and some unexpected surprises which will come in really handy!

  9. Hi Andrew,
    Your videos are awesome.
    Can you please help on how can we control internal tools on our system using VBA.
    Does it require downloading external libraries like selenium ?

  10. Thank you very much for this tutorial. I have a question .. @ 18:30
    What if I wrote "GOTO GuessAgain" instead of "resume GuessAgain"?
    is there any difference? .. "resume" command can be used next I guess, but I can't think of a useful situation for this …

  11. Andrew your the best I ever seen: the way u organised the videos are simply awesome! In an every videos of yours I get some trivia and so on…

  12. Your videos really simple and easy to understand.
    Could you please do a video on Storage Item?
    it would be a great help

  13. Great video as always! And I have a suggestion: When using previous excel sheet examples, where there are formula already filled in, I would recommend to leave in the description a link (e.g. in dropbox) where the base of the macro can be easily downloaded. In this way people don't have to rewrite it by hand, nor have to look into the previous VBA classes to take the code (also because people like me like to change a little bit the code to make tests and what not, so the macro will never be the same as the one you make). Hope this feedback helps!

  14. Great video! You can also track the exact line number of an error by using the Erl function (if you add VBA line numbers).

  15. Hi Andrew, thanks a ton for these amazing videos. i hope you dont mind directing me to solution to the problem i am facing with one of my application. i have a data analyzing sheet which import raw data and create a new sheet named with that criteria i have put, the problem is when someone run twice the same criteria it comes with a obvious error as workbook cant have 2 sheets with same name. is there any way that i can put in my error handler to check if the sheet already exists and tell the user that its already exists… to handle the error gracefully i guess.

  16. Any one plz guide me that how can an error 1004 be removed? Its a Run Time error in MS Excel (stats tool package2). Early guidance may plz be solicited.

  17. Dear Mr. Andrew. No words can thank you enough. What a wonderful delivery of knowledge sharing You are doing. I have been able to create an excellent maintenance management system using excel vba by seeing your tutorials. I am a mechanical engineer and developing a system was need of the hour. Once again thank you.

  18. Awesome explanation. Congratulations once again for the incredible content you're showing us here. One question, where can I find the errors numbers of each error?

  19. Hi WiseOwl,

    I am getting an error when trying to run a template which has macro(Vba) coding in backend.
    The error message is " 57121 Application defined or object defined Error".

    I tried finding search,and i saw about activex setting in excel but that didn't work, even macros are in enabled mode.
    Now the problem is that the excel sheet containing macros run on some machines. I don't know whats wrong in some systems.

    Will appreciate any suggestion that may work. :):)

  20. awesome training collection Andrew, could you please upload Ms Access trainings if it is available with you it would be really helpful..

LEAVE A REPLY

Please enter your comment!
Please enter your name here