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.
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.
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" :-)
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.....
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.
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!
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 :) :)
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.
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?
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.
@@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!
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.
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.
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.
@@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.
Amazing! Zero Dislikes! Best teacher in youtube!
Thanks
You made my day. Just amazing
You're a great teacher. Awesome Content.
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.
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.
clear as a bell, just need to practice implementing these strategies, thanks looking forward to part two!
Glad it was helpful!
Thanks! Great tutorial man. Nicely done!
Glad you liked it!
best explanation on error I had! Thanks Paul!
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" :-)
Glad you like it.
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.....
Implement functionality: 20% effort
Error trapping, user experience: 100% effort
Learning to calculate: Priceless
Thank you Paul. 👏 I really wish you will get too many new subscribers on your valuable channel. Thanks for all your efforts. Salim
Thanks Salim.
Thank you Paul! Really helpful. ... for e.g., will think 3 times before adding "Resume Next" into code.
Thanks for your comment Andrey
Thanks Paul!
Thank you Paul for this instruction. It's very helpful :)
You're welcome Michal
Great video.
Thanks, Paul,very helpful and useful.
Thanks John
THANK YOU
Can you also add explanation about on error go to -1. Thank you for this one.
as always: simple, elegant, clear. Thanks mate.
Thank you
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.
Nice subject
thank you very helpful
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!
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 :) :)
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.
Not that I understand and can use this information but, I thank you for presenting it.
You're welcome Geoffrey
Thanks Paul :)
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?
Sandra Vukovic I’m wondering the same also...
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.
@@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!
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.
Very true. You have treat "what can go wrong" as part of the project requirements so you know how the application should handle issues.
@@Excelmacromastery Yes, Murphy's Law.
Can you have an error handling label in the middle of the subroutine instead of at the end after your "Done:" label?
Thank you Sir. What if the error derives from another process (eg database query bad response)? Will the error handler catch it?
Yes. It will give an error.
@@Excelmacromastery How do you get the database object error code (object?) rather than the vba Err code
👍
Error Handling is suitably called 'handling potential error'.
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.
box script testing... txt
.
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.
great videos..
Thank you GeezR
@@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.