How to Solve a VBA Error in Less Than a Minute
👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
-Want to download the source code for this video? Go here: bit.ly/3GOSuS8
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
How to Solve a VBA Error in Less Than a Minute
In this video I'm going to show you how to find a runtime error in less than a minute. When a runtime error appears it often leaves the user confused. Where should they start trying to track it down and find the cause?
There are 4 simple steps that you need. Some are well known but some are secrets only known by professional VBA developers. Most people waste a lot of time trying to figure out where the error is and what is causing it. However, if you follow these 4 simple steps it will save you hours of wasted time.
#VBaerror
Useful VBA Shortcut Keys
========================
Debugging:
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
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)
Пікірлер: 97
Debugging has given me some serious headaches. I cannot thank you enough for your explicit breakdown. Your tutorials have been an education for me.
I've been working with VBA for over a year, and every time I watch one of your videos I learn something new and invaluable that I wished I had learned long ago. They're all so clear and concise, and aimed at real every-day problems.
@Excelmacromastery
3 жыл бұрын
Thanks. That's great to hear.
Paul, I have not used the watches window much at all, but after viewing this online seminar I am a converted fan and now will be using it a lot more. I cannot thank you enough for the excellent training I have received from you over the years and in particular your VBA course, which is awesome.
@Excelmacromastery
3 жыл бұрын
Thanks a lot John, Glad you like it.
Great job as always. Rather than bringing up the Watch window, you can get the value of any single variable by simply holding the cursor over the variable in the debug window. The watch window is then helpful if you need assistance finding or confirming the source of the data in that variable. I find if I am dealing with my own code the source is usually readily apparent and the watch window is not needed. When debugging someone else's code or my own code that I have not seen in awhile the watch window is used more often.
@Excelmacromastery
3 жыл бұрын
Thanks for the feedback Don.
Well structured walkthrough, especially of Break on All Errors and editing the Watch Window.
@Excelmacromastery
3 жыл бұрын
Thanks Patrick
Another great job Paul! It didn't solve my problem, but I learned a ton and am on my way! Thanks so much!
Outstanding! This video is one of your very best Paul. I have struggled to use the Watch window effectively until now. Thank you & Well done!
@Excelmacromastery
3 жыл бұрын
Thanks Topher, Glad you found it helpful.
As watching every of your videos, I could enjoy it truly much! Thanks for your informative and easy tricks about error handling and reverse engineering light! Hope publishing your next video won't need so much times! I hope your fine and thank you!
@Excelmacromastery
3 жыл бұрын
You're very welcome!
You saved my life. Thank!
Great video and amazing way of explanation. It can come very handy
I really appreciate you explain everything in the simpliest way there is. There are lots of VBA tips on the internet, but only a little of it is understable. Please do more of your best - as always :)
@Excelmacromastery
3 жыл бұрын
Glad to help!
Excellent, as always. Thanks for sharing.
@Excelmacromastery
3 жыл бұрын
Thanks again!
King of error trapping!! Another good video.
Thanks for quick tips
ty, i actually solved my problem in under one min. gj brother
Nicely described!
Fantastic as always, you are doing a great job
@Excelmacromastery
3 жыл бұрын
Thank you very much!
every now and then if you are lucky you come across a gold nugget. This is one of those, I have used the watch window but can now see how much more useful it can be used...awesome tutorial.
@Excelmacromastery
3 жыл бұрын
Glad you like it Greg
thank you for this.... i know it will save me a lot of time
Thank you. Helped alot❤
Excellent, perfect way to debug. I sometimes use immediate window, if it is complex, watch window is ideal choice. Thanks for your efforts on making this content.
@Excelmacromastery
3 жыл бұрын
You're welcome!
Excellent and very useful, thank you.
@Excelmacromastery
3 жыл бұрын
Glad it was helpful!
Great job
man, you're just incredible. i have learned a lot from you. great content, simply to figure it out. great examples, etc. i have done a lot of different tutorials but your is the best. your blog is super helpful. owing to you, I have understood: arrays, collections. big thanks from Poland
@Excelmacromastery
2 жыл бұрын
You're welcome😀
Estoy aprendiendo bastante con tus videos gracias por compartir tus conocimientos.
@Excelmacromastery
3 жыл бұрын
You're welcome.
Very clear explanation, thanks
@Excelmacromastery
3 жыл бұрын
Glad you liked it
Thank you sir
Great tips, and great presentation.
@Excelmacromastery
3 жыл бұрын
Thanks Aaron.
Thank you so much!
@Excelmacromastery
3 жыл бұрын
You're welcome!
Thak You very much. Great as usual
@Excelmacromastery
3 жыл бұрын
Thanks again!
Thanks very much for the information.....
@Excelmacromastery
3 жыл бұрын
My pleasure
Great tip 👍🏻
@Excelmacromastery
3 жыл бұрын
Glad it was helpful!
Fantastic, thanks Sir.
@Excelmacromastery
3 жыл бұрын
Most welcome!
Nice one
Very usrfull
now I know how to use Watches window better. 🙏Can you please put some of your fantastic complete guide on text file handling in VBA?! I appreciate that.
@Excelmacromastery
3 жыл бұрын
I cover Text File Handing in my Excel VBA Handbook Course.
Thank You
@Excelmacromastery
3 жыл бұрын
You're welcome
Great explanation!
@Excelmacromastery
3 жыл бұрын
Thanks!
@hkoranteng1
3 жыл бұрын
@@Excelmacromastery Had i have known you'd reply I would have elaborated a bit more lol! To put my comment into context, for me error handling is the most intimidating part of learning to code in VBA (or in any language for that matter). Your video broke it down so well that I literally have no idea what I was scared of in the first place. I can't recommend your videos enough. Keep up the good work.
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!
I bought your book 41 habits
I Need some help, I built a trim sheet that has around 25 Worksheets in the workbook. I built in a auto reset clear page button for each page about the pages work just fine with the pages protected or unprotected. The other half for some reason will rest just fine unprotected, but when I protect the sheet and hit reset I get and error code 400, How can I fix this?
Merry Christmas to you and your family...
@Excelmacromastery
3 жыл бұрын
Thanks Kartick. Best wishes to you and yours.
Hi do you know what causes "unexpected error (35005)" excel? thanks so much
Please can we make record for errors by name of the macro and line number ( sometimes the employed don't have Permission to access VBA )
Happy New Year in advance to you Sir and your Family. May this new year bring all the happiness in your life. Sir I'm requesting you to please make videos on these topics given below. No one has ever discussed on these topics on internet in great details so that we can understand them properly. Please make videos on these 3 topics. By the way, I'm very much impressed with your teaching style. Your way of teaching is unbelievable. Please keep my request. 001) Split Function in VBA with Multiple Delimiters like ";:*_, ./#@". I mean to say, how to split alphanumeric contents in Excel with split function in VBA those have multiple Delimiters. 002) How to convert Currency to Word through VBA? (Like 1,000,000 = One Million Dollar and Zero Cent Only) 003) How to find out Prime Numbers through VBA? (Like 3, 7, 11, 13, 17, 19, 23, 29 and so on).
Hi i hope you are fine and good. i have question? kindly reply I have VBA Macro excel file. it have a button in it. when i press the button it automatically get data from website and put it excel sheet with date. but unfortunately the file is not working now. when i run the file in other laptop it works fine. can you solve the problem to run the file in first computer?
Hello Sir, Recently, I've faced some problems in my office work. In order to solve those problems, I came up with 3 ideas. But due to shallow knowledge in VBA, I couldn't implement those ideas in reality to sort out those issues. I've given my 3 questions below one by one. I believe, you'll definitely help me. 001) Is it possible to create Spilled Array (Dynamic Array) like function in VBA. I mean to say creating User Defined Function like Spilled / Dynamic Array Functions (Filter, RandArray, Sequence, Sort, SortBy and Unique Functions) in Excel (Office 365 Version). 002) How do I split First Names, Middle Names (If Available) and Last Names with one single User Defined Function in VBA? Is it also possible in VBA? 003) How to use VBA Split Function with Multiple Delimiters?
Wow!
@Excelmacromastery
3 жыл бұрын
Glad you liked it.
Just curious why you didn't mention the locals window?
I don't use the watch window or Parent anything like often enough.
my problem "is Out of memory" whenever i try to open vba on excell
Another wonderfully presented Excel VBA explanation... as we have come to expect. Many thanks.
I'm getting runtime error 91 excel on mac not on windows
Thanks for info, however, my excel vba says out of memory and this is the code, literally, " Sheets("Customer").Select Exit Sub . . . ? This is an excel that was downloaded onto a brand new pc, and my old pc is where this file came from (works fine there, and that pc is pretty much out of memory). I'm hoping that there is a way way way easier answer- like a button that I just have to click. . . .This is almost no code. . . it is only "selecting a sheet"
@Excelmacromastery
Жыл бұрын
Sounds like your code is in an event. When you select the sheet it triggers the event again and again until you have run out of memory. Turn off events at the start of the sub: Application.EnableEvents = False. Turn back on at the end: Application.EnableEvents = True
How to solve catastrophic failure error in vba
I need help. Can you please help
💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖
@Excelmacromastery
3 жыл бұрын
Thanks
sir can pls correct for me Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Rg As Range Dim G As Integer Dim varno As Long 'With Sheet1 'Range("J5:J5").AutoFill Destination:=Range("j5:j213") 'Range("L8").Formula = "=IF(AND(F5="",G5="",H5=""),"",I4+F5-G5-H5)" 'Range("l5").Value = "=(IF(AND(F5="",G5="",H5=""),"",I4+F5-G5-H5)" Range("A2").Formula = "=TRANSPOSE(P2)" Range("J4").Formula = "=SUM(G4-H4-I4)" Range("L3").Formula = "=SpellNumber(L2)" Range("M2").Formula = "=NOW()" ' Range("A1").Formula = "=TRANSPOSE(FO1)" Range("D2").Formula = "=COUNTA(D5:D217)" Last Row (Total =I4+F5-G5-H5)" End Sub
Thank you sir
@Excelmacromastery
3 жыл бұрын
Welcome