Master VBA Debugging in 20 Minutes
Вставка
- Опубліковано 2 сер 2024
- 👉 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: shorturl.at/Ncam5
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
Master VBA Debugging in 20 Minutes
In this video, I'm going to cover everything you need to know about Debugging in VBA.
* What is debugging and why you need it.
* When to use the Locals window and when to use the Watch Window.
* The SandBox Method
* Putting it all together - a real-world example
* Plus loads of cool tips and tricks you didn't know
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L 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
Autocomplete 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)
Table of Contents:
00:00 - Introduction
00:40 - What is Debugging?
01:19 - Simple Debug Example
01:50 - The Debug Toolbar
02:25 - The Locals Window
03:58 - The Watch Window
05:03 - Watch Window: Using Functions
06:20 - Tips for Debugging Ranges
07:33 - Watch Window: Divide Conquer
08:45 - Step Into/Over/Out
11:31 - Run to Cursor
11:59 - Set Next Statement
12:48 - The Call Stack
13:45 - Show Next Statement
14:17 - Break in a loop
15:43 - The Sandbox Method
17:27 - Real-World Example - Наука та технологія
Enjoy the video. You can download the workbook I used in the description below. Let me know the most useful thing you learned from this video.
Thanks for the great input! very valuable. Just, I can't download the file through the link given in the description. Can anyone assist? Thanks :-)
So much great information in just 20 minutes. I'll definitely rewatch this a few times, but this will help me save so much time debugging. Thanks Paul!
Yet another helpful masterpiece. Sir, Thank you for this wonderful explanation. I am still learning excellent VBA tricks from you. It's Awesome.
You are most welcome
Thanks for this video. For me it didn't contain much new stuff, but I recommend this video all the time to VBA beginners: friends, colleagues and on Stack Overflow.
Thanks for sharing!
Wow, Paul! I’ve been debugging the hard way for so long … like using a hand shovel when a bulldozer is right there. Thank you!
You're welcome Jim
Thank You! Knowing how to debug is just as important as knowing how to code.
Very true!
Thank you for this video. I've been missing a lot of things in debugging my vba. This is very helpful. Breaking in a loop is awesome. I have been struggling with that. Now I know how. Thanks again
You're welcome
Great intro. (In decades of using VBA, I'd never pondered what Set next statement even meant. Too soon old, too late smart.) Two comments:
1) the advantage of Run to cursor vs setting a breakpoint is you don't have to clear the breakpoint afterwards if you don't want to keep stopping there during this session.
2) I'm somewhat surprised you never mentioned the Immediate window. Some of the things you showed for watch are occasionally better suited to Immediate (i.e., ? x+7700). Also, Immediate allows you to modify variable values on-the-fly (i.e., loopcount=maxloopcount-1). Not to mention Debug.Print...
This is great. I've also been exploring the usefulness of debug.assert to stop when a value doesn't match what's expected. Not sure if there's any advantage over a watch.
Great VBA knowledge sharing sir Paul. So much thankful. God bless you.
Wonderful lesson of debugging code.
I am very grateful for the video.
Thanks u
Thanks for sharing these very useful tips!! It will help me a lot
You're welcome
I wish I watched this video couple months ago, but only hours of painful debugging made me look for it :) thanks!
Thanks Paul. Great, as always! Thumbs up!!
The "Break When Value Is True" Watch option is especially valuable. Thank you!
Thanks Walter
And once again I left with more knowledge of vba. Thanks Paul.
My pleasure!
Concise and super informative, thank you!
wow, I've been debugging for years and still learned a lot from your video, ty
You're very welcome Rafael!
Another master class!
Thanks.
Great and knowledgeable video. Thanks for making it and sharing it with us.
Thanks for the video. Very helpful!
Gracias, aprendi cosas nuevas
Unbelievably informative video! Thank you for making it!
You're welcome
Awesome video!
Glad you enjoyed it
Thanks man!
Thank you
Thanks a lot for the great content!
You're welcome.
Thanks
Really usefull, thanks Paul
You're welcome
Thank you. I am not an excel pro. So when my simple macros break it is traumatic to say the least. I appreciate your tutorial videos.
Glad you like it.
Nice Video Mr. VBA!👍👍👍🤟😎
Thanks John.
I didn't think I could get anything new from the video, but... this 'Add Watches & Break if True' is really cool tip. Thank You for that :)
Maybe I have something for You - do You know that You can manually move the yellow line (break mode) some lines further or before? It helps me a lot in debugging.
Glad it was helpful!
Useful❤😂
I'm relatively new to vba and self taught, I often read that "call" is depreciated and "application.run" should be used instead? What are the advantages and disadvantages of each?
Call is not required but I like using it as it makes the code a little bit more readable.
Well how do you step on I cant even get the yellow arrow to highlight the next line?
1:27 I wrote the code exactly as you did. Whenever I try to step through windows just gives me an error chime. Any ideas please?
Excel Macro Mastery...I need help! I have been running into a brick wall concerning PasteSpecial x1PasteValues error. How do I correct this error? If you've explained this specific example, please please point me to your video. Thanks!!
Do you have any examples where a function can be used in the "Break When Value Is True" Watch option is used? I'm trying to figure out where in my code a particular data connection is getting inadvertently moved/renamed which causes subsequent data refresh steps to fail. I created a function that should tell me if the Connection exists but adding it to the watch window [via something like ConnExists("Connection Name")] always shows "" and I wasn't sure if something like a function that returns a Boolean could be used in a "Break When Value Is True" watch option. 🤔
I don't think you can do that with a function. If possible you could return the function to a variable and put the watch on that.
Another idea is to use the Debug.Assert line which will pause if the condition is false. See this video for more about Debug.Assert(ua-cam.com/video/CT7XkPXKVFw/v-deo.html)
Yet another helpful masterpiece. Sir, Thank you for this wonderful explanation. I am still learning excellent VBA tricks from you. It's Awesome.
Thanks Rahul