VBA Error Handling Explained in Plain English (with examples)

Рет қаралды 46,358

Күн бұрын

👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
- FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: bit.ly/2MXsnz9
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
VBA Error Handling Explained in Plain English (with examples)
Related Article: VBA Error Handling - A Complete Guide(excelmacromastery.com/vba-err...)
Useful VBA Shortcut Keys:
Compile the code: Alt + D + C or Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
Auto complete word: Ctrl + Space
Shift + F2: Get the definition of the item under the cursor.
Ctrl + Shift + F2: Go to the last cursor position.
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + R: View the Project Explorer Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
Tab: To move lines of code to the right(Indent).
Shift + Tab: To move lines of code to the left(Outdent).
F4: View the Properties Window.
F9(or click left margin): Add a breakpoint to pause the code.
Table of Contents:
00:00 - Introduction
00:24 - Ensure the error settings are correct
00:55 - Remove all Compile errors
02:45 - Dealing with Runtime Errors
04:45 - The 3 Error Handling States
06:57 - On Error Goto 0
08:09 - On Error Resume Next
10:22 - On Error Goto [label]
15:18 - Cleaning up after an error occurs
17:54 - Turning off Error Handling
19:03 - Unwinding the code
21:19 - Error Handling in a Nutshell
21:23 - Recap

Пікірлер: 58

@EduardoRibeiroEdu 4 жыл бұрын

best explanation on error I had! Thanks Paul!

@tdtrecordsmusic 3 жыл бұрын

You're a great teacher. Awesome Content.

@s1ngularityxd64 2 жыл бұрын

You made my day. Just amazing

@darray7472 4 жыл бұрын

Paul, thanks for this video and explaining how the code does not stop at the error handling label (eh:) if there is not an error. I was never too sure about that.

@elainestewart7188 3 жыл бұрын

Thank you and much appreciated. This was great for me as I am self taught and a newbie in coding and don't know what I am doing half the time. With your help things are getting better.

@mike_case 4 жыл бұрын

Thank you Paul for this instruction. It's very helpful :)

@Excelmacromastery 4 жыл бұрын

You're welcome Michal

@JohnOvens 4 жыл бұрын

Thanks, Paul,very helpful and useful.

@Excelmacromastery 4 жыл бұрын

Thanks John

@wayneedmondson1065 2 жыл бұрын

Thanks Paul!

@gregbernard7861 Жыл бұрын

Great video.

@micmike 3 жыл бұрын

clear as a bell, just need to practice implementing these strategies, thanks looking forward to part two!

@Excelmacromastery 3 жыл бұрын

Glad it was helpful!

@stevec1835 2 жыл бұрын

Thank you so much for these videos. Love them, so helpfull. Am doiing your Basics course at the moment and have learned so much. Just wanted to let you know i appreciate your time and efforts.....

@andrewwest5344 3 жыл бұрын

THANK YOU

@wayanfjwork5037 2 жыл бұрын

Thanks! Great tutorial man. Nicely done!

@Excelmacromastery 2 жыл бұрын

Glad you liked it!

@jay55patel 4 жыл бұрын

thank you very helpful

@rrki Жыл бұрын

Thank you

@PrincePedia 4 жыл бұрын

Thanks Paul :)

@infamemoriarty8432 4 жыл бұрын

Hi Paul, another excellent video, thank you for this. All your videos are refresher and I always learn a bit more each time. Looking forward to watching your next video "Error Handling Strategy" :-)

@Excelmacromastery 4 жыл бұрын

Glad you like it.

@sanjeevirajendran9670 2 жыл бұрын

Nice subject

@andreykapustin5080 4 жыл бұрын

Thank you Paul! Really helpful. ... for e.g., will think 3 times before adding "Resume Next" into code.

@Excelmacromastery 4 жыл бұрын

Thanks for your comment Andrey

@AI-ec2qb 3 жыл бұрын

Amazing! Zero Dislikes! Best teacher in youtube!

@Excelmacromastery 3 жыл бұрын

Thanks

@sasavienne 4 жыл бұрын

Thank you Paul. 👏 I really wish you will get too many new subscribers on your valuable channel. Thanks for all your efforts. Salim

@Excelmacromastery 4 жыл бұрын

Thanks Salim.

@geoffreyjones3832 4 жыл бұрын

Not that I understand and can use this information but, I thank you for presenting it.

@Excelmacromastery 4 жыл бұрын

You're welcome Geoffrey

@hemant0088 4 жыл бұрын

Paul, you have given very good example even beginner level coder can understand. Kudos :) However it would be helpful if you can include the some example about on error goto 0 , 1 and -1. Also we are keen to know trick how we can handle error if subsequent procedure doen't impact if base procedure has any errors. Waiting for next release. Good job :) :)

@Excelmacromastery 4 жыл бұрын

I show an example of goto 0. goto -1 is rarely needed and I thought it would confuse things. There is no goto 1. I will be covering error handling when the procedure is not the top most one. This will be in my next video.

@sujatadhamale1194 3 жыл бұрын

Can you also add explanation about on error go to -1. Thank you for this one.

@thearchibaldtuttle 4 жыл бұрын

Implement functionality: 20% effort Error trapping, user experience: 100% effort Learning to calculate: Priceless

@kishi3987 Жыл бұрын

Great content! Thank you for sharing your knowledge! My team has quite a few macros. Most of them open log files, read data, validates and extracts data on log files based on the position in the row. Then finally generats ouput files (log file) with the information based on the matched criteria. Most of the times because of extra spaces or characters macro not able to read data on source files (log files) and providing error messages. Could you please upload a video with log files and how to identify source file and the line of souce file where the error coming from? Thank you in advance!

@jerryjones8836 Жыл бұрын

Boy have I been making errors in my error handling. Thanx for the advice and teaching an old dog new tricks. Just an FYI, the info in "More" has a minor typo FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here, but the cheat sheet is actually errors. Thanx again for all.

@GeezRvonFart 4 жыл бұрын

great videos..

@Excelmacromastery 4 жыл бұрын

Thank you GeezR

@GeezRvonFart 4 жыл бұрын

@@Excelmacromastery i think your videos have a good amount of info in them, others often don't explain why a command does something or go too deep down the rabbit hole.

@andywawa7227 2 жыл бұрын

as always: simple, elegant, clear. Thanks mate.

@sandravukovic2901 4 жыл бұрын

Hi Paul, thank you for such a nice explanation. One question: if the code is changing the data by looping through cells, and after a few cells runs into type mismatch error, is there a way to reverse the process other than putting everything into an array? Maybe letting the user correct the mistake in the cell and then continue with the loop?

@dtr406 4 жыл бұрын

Sandra Vukovic I’m wondering the same also...

@Excelmacromastery 4 жыл бұрын

Generally it's cleaner to terminate the current task, ask the user to fix the data and run it again. If you wanted to do it: You can use "Resume Next" after the error message. This will continue the code from the line after the error. You also need to set the loop variable back one to retry the code that failed. You could do this before the "Resume Next" line e.g. i=i-1.

@sandravukovic2901 4 жыл бұрын

@@Excelmacromastery Yes, that's what I was thinking. I had an example in mind where one should add 10% increase to all prices in cells, and by starting the code from the beginning, all the prices that were already increased would be increased one more time. Thank you for the support!

@CollDott 3 жыл бұрын

👍

@TSSC 4 жыл бұрын

The tricky thing with error handling is to think about what can go wrong. It’s easy to think that everyone will do as intended. One needs to imagine what can go wrong (e.g. incorrect data or user input, user clicks on cancel or closes a user form or dialogue box) before being able to handle errors in a way that doesn’t confuse the users.

@Excelmacromastery 4 жыл бұрын

Very true. You have treat "what can go wrong" as part of the project requirements so you know how the application should handle issues.

@houstonvanhoy7767 3 жыл бұрын

@@Excelmacromastery Yes, Murphy's Law.

@tomschopler4094 6 ай бұрын

Can you have an error handling label in the middle of the subroutine instead of at the end after your "Done:" label?

@stavrosdimoudis1524 4 жыл бұрын

Thank you Sir. What if the error derives from another process (eg database query bad response)? Will the error handler catch it?

@Excelmacromastery 4 жыл бұрын

Yes. It will give an error.

@redfern_mike 4 жыл бұрын

@@Excelmacromastery How do you get the database object error code (object?) rather than the vba Err code

@ibh9999 Жыл бұрын

Error Handling is suitably called 'handling potential error'.

@PhanchaiSiam555 3 ай бұрын

555. My Userform is not loading at all on another PC. Excel just say cannot open file. LUL. In the macro is just a Userform1.show and the message say: Cannot open file.

@oluben3577 4 жыл бұрын

box script testing... txt

@tughanozsezer9332 4 жыл бұрын

@dariuszspiewak5624 Жыл бұрын

It's a real shame that M$ have for so many years not implemented the clean and elegant structured error-handling paradigm. We're stuck with this ugly spaghetti code >:\ Preposterous. This is why I try to shy away from VBA as much as possible. It's just plain ugly.