How to Design and Code an Excel VBA Application Like a Pro

Рет қаралды 210,753

Күн бұрын

How to Design and Code an Excel VBA Application Like a Pro
In this previous videos I have shown many tips and tricks for getting to the most out of Excel VBA. In this video I'm putting them all together and showing you how to design and write the code from and Excel VBA application.
I've also included many little-known secrets that have never appeared in any of my youtube videos.
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE CODE: You can download the code from here:
bit.ly/2UamvVh
Excel VBA Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Related Articles
Excel VBA Copy - The Complete Guide to Copying Data(excelmacromastery.com/excel-v...)
The Complete Guide to Ranges and Cells in Excel VBA(excelmacromastery.com/excel-v...)
Excel VBA Array - The Complete Guide(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
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)
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
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
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
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
View the Properties Window: F4

Пікірлер: 278

@Excelmacromastery 4 жыл бұрын

Hi Everyone. I hope you enjoy the video. You can download the code from the link in the video description.

@konatesiakamounir8704 4 жыл бұрын

I have hours of work how to calculate it because when I use the same procedure as for the sums it gives me 0

@budiyanto1531 4 жыл бұрын

Excellent

@johnwaterbury5046 3 жыл бұрын

This was outstanding information - thank you

@philotine 3 жыл бұрын

I simply cannot find this. Where is this video description?

@dimmak8206 3 жыл бұрын

Help please, The link to your code isn't showing me anything but a blank page

@albertbatfinder5240 4 жыл бұрын

Been writing code since punched cards, and I can tell any beginners out there that this lesson is packed full of good programming principles. Paul is a natural born educator.

@Excelmacromastery 4 жыл бұрын

Thanks Albert.

@tomharrington1453 4 жыл бұрын

I second that. I have had to watch this in 10 minute sessions,. Its a lot to take in.

@oneanddone7992 4 жыл бұрын

I'm fluent in VBA, and I second the motion this educator does a very good job. Also, dictionaries are neat to use, and as my mentor told me years ago, they're the lazy person's 2 dimensional array.

@Elmar2k83 2 жыл бұрын

I have been programming for years but these videos are hot stuff :)

@hadibq 2 жыл бұрын

So true!

@RoyalGoal Жыл бұрын

Never thought of saving the Excel settings code as a class module, but that's really efficient and found that really helpful!

@mrashid229 3 жыл бұрын

I am an Excel VBA enthusiast and have come across lots of resources online and grateful to all contributors. However always realized lacking a professional approach that is essential to maintain integrity and maintainability of an application. Often tried to develop some standard by myself but realize shortcomings quite often. Bundling so many vital issues into this small(!) application and describing those in such a simple way, you have given the hope and confidence to learners/developers to grow faster in becoming a Pro. Will go through all your resources eventually. Not only learning new features but applying those in the right place is important. Your teaching approach and delivery is unparallel!

@Snowy042 4 ай бұрын

I've been programming self taught vba for 15 years. And this video still had plenty of tips that I found useful and will start to apply. Thank you

@rrrprogram8667 2 жыл бұрын

I Madara the ultimate king of the world.... Again declares you as the king of VBA

@noisearchitect 2 жыл бұрын

Hi, thank you so much for your great VBA tutorials. Becuase of you, I just got a raise on my job.

@Excelmacromastery 2 жыл бұрын

Nice work!

@imronnesia5170 7 ай бұрын

Finally i found my vba hero here. As a the self taught beginner with nothing resources, no teachers, nothing basic vba knowledge, i am learning from scratch by Google.. after many weeks and months, now you turns me on it. I don't have any confidence before, since didn't get a right direction how to write good code structure, handling errors, manage dynamic rows column condition etc. By now.. i'll erase all my silly code - code for my self needs - and starting to rebuild them from zero. subscribe your channel. Even i am not native english speaker, it's easy to understand all your explain. thank you

@percyveere8400 4 жыл бұрын

Outstanding video and part of a great series! These videos have re-ignited my enthusiasm for writing VBA code, and now that I have plenty of time (due to self-isolation!), I’m going to scrape the rust off my knowledge and get right back into it again. Cheers - keep up the great works!

@dewamanandhar5029 3 жыл бұрын

This is great video. You are not just teaching Excel VBA, you are teaching the programing principles that will go long way in every programming.

@Excelmacromastery 3 жыл бұрын

Glad it was helpful!

@pegwinn Жыл бұрын

Thank you again. After years of doing the macro recorder and begging for help on message boards I am finally determined to learn and hopefully (eventually) contribute to the boards. I have a huge macro system and there is an intermittent issue. I am determined to figure it out and a guy at mrExcel shared your link. I’m hooked. Thank you for making these tutorials.

@Excelmacromastery Жыл бұрын

Happy to help!

@billroberts2808 4 жыл бұрын

Enum component was great! Been using VBA since Access 3.0. Started Excel VBA reluctantly but out of necessity for a couple of clients. Your videos are very well structured and most informative. Some of the tips and tricks you share have become basic design components in many of my desktop platforms. Thanks for all you do! Best regards

@seamushand8439 3 жыл бұрын

I really love the programming structure and checks and balances and applied to something simple enough that the structure of the code is evident. Thank you

@FukSN 4 жыл бұрын

As always Paul, another great video. It's channels like yours that really help the self taught people like me excel (pun intended) at work. Keep up the good work, you are one of a few Excel channels I look forward to and watch without fail. Thank You.

@redfeather22sa 2 жыл бұрын

Brilliant Video & service you give sir !! I cant thank you enough !! Thumbs Up !!

@tomharrington1453 4 жыл бұрын

Just a beautiful, elegant clinic in the fundamentals of reading, modifying and outputting data with an excel VBA application.

@cgd1037 5 ай бұрын

Excellent video. Thought I knew VBA however it is an example of knowing what you know and not knowing what you don't know. Thanks Paul.

@tjchance4023 4 жыл бұрын

Some very useful techniques applied here, as well as your english speaking easy to understand. I'm not a english speaking person, so important part for me when it's well articulated. Thank you for sharing Paul, greatly appreciated!

@FulvioGaggioli 4 жыл бұрын

Thank you for your teachings: I learnt a lot!

@jimfitch 4 жыл бұрын

Terrific lesson, Paul, thank you. I have built several Excel applications during past few years & thought I had good techniques & habits, but this lesson identified things I can/should do to make them (much) better. I appreciate your instruction very much.

@Excelmacromastery 4 жыл бұрын

Thanks Jim. Glad you liked it.

@twgrisham 4 жыл бұрын

Paul, this is by far the best comprehensive video I have come across for building a real-world application from scratch in a generic organized fashion that limits the scope of coding/debugging to focused building blocks. That's a lot of words to say thanks!

@Excelmacromastery 4 жыл бұрын

Thanks for the feedback Thomas.

@stewartlittle7931 2 жыл бұрын

Great that you mentioned the Commodore 64 and Spectrum! I had a Spectrum 48k and I can remember writing simple BASIC programs. I can also remember computer studies at school where we would write out our programs hard copy and then they would be sent to the Hatfield Polytechnic college where they would be entered in to their computers and run. We would then get our programs sent back to us as a stack of punch cards errors and all!

@trux66 4 жыл бұрын

Hi Paul - like always great video. I love the "extras" that you keep throwing in along the way. This time it was the simple class module for managing Excel functionality like calculation and events that caught my eye. I plan to use it and give you full credit in all my applications. I am rebuilding an old application that was created before I found your site and picked up the VBA Handbook, this little class will make a solid addition to unwinding that mess of code.

@Excelmacromastery 4 жыл бұрын

Thanks Bruce. Glad you found it useful.

@JohnOvens 3 жыл бұрын

Paul, that was great. Each time I watch your videos I learn new things, thanks heaps. John

@mangobrother Жыл бұрын

A great video. Will come back to it often for my education. Paul, you are a fantastic teacher.

@haragorn88 4 жыл бұрын

Awesome Paul. Even after doing the VBA Handbook course still learn heaps more in everyone of your videos and blogs. This time using enum instead of constant and using class module for turning off and on functionality. And, you can never get enough on the dictionary. Great teaching technique of yours to show "You could do it this way but this way is better and this is better again!"

@Excelmacromastery 4 жыл бұрын

Thanks Craig. Glad you enjoy my resources and handbook so much.

@stevennye5075 4 жыл бұрын

This was one of your best videos so far, for the self-taught it is easy to get a big jumble of code without any structure. This was very good on defining how to set up subs.

@Excelmacromastery 4 жыл бұрын

Great to hear Steven!

@stevec1835 2 жыл бұрын

Wish i had your brain. My head is spinning, but i will keep trying. Thanks for these great tutorials....

@grahamparker7729 4 жыл бұрын

Great to see you code the application, more please.

@estebanisswimming 4 жыл бұрын

Thanks ! Absolutely amazing

@sasavienne 4 жыл бұрын

Thanks Paul. 👏 Your tutorials are stunning. I could learn many useful VBA techniques from you. Go on please. Thanks indeed.

@Excelmacromastery 4 жыл бұрын

Thanks, will do!

@Red00022 11 ай бұрын

Clean code makes me smile

@MustafaKalafat Жыл бұрын

37:15 thanks for the nostalgia as i remembered my first coding in 1990 using gwbasic in a 80286 machine. i used gwbasic by the end of 1998 to perform calculations in my fluid mechanics homeworks. :)

@adamwstbrook 4 жыл бұрын

I've learned a ton from this video that covered a range of topics by building an application from scratch. I love the trick to update the dictionary value. I'll definitely be utilizing enum and the settings class. I used to use Application.ScreenUpdating = False, Application.Calculation.... etc and reset them to True at the end. Class modules are still foreign to me, but I'll first utilize your template! Thank you again!

@Excelmacromastery 4 жыл бұрын

Thanks for the feedback Adam. Glad you liked the video.

@nicor1501 4 жыл бұрын

That's just (again) an awesome video ! I've been coding for years and still discover new tips and tricks, thanks to you! As for the Enums, the trick is that the incremential is done automatically : Enum ReadColumns rcinvoice = 1 'returns 1 rcCompany 'returns 2 rcAmount 'returns 3 End Enum

@Excelmacromastery 4 жыл бұрын

You're welcome. You're right about the enums.

@davidmonnerat8598 4 жыл бұрын

*Pressed subscribe* :) Great Video. Been coding VBA for years, but still could add something to my skill set. Especially the debug.assert and the enum columns are potentially very helpful. Never thought about treating col changes with an enum.... My bad Thanks!

@williamspiers4692 4 жыл бұрын

Very clear and useful. Thank you. Pleased that I use a few of your tips already

@giorgiomya4546 3 жыл бұрын

✔ It is very impressive the clarity you possess to write an entire application in one go!. Thank you for sharing your knowledge in such a professional manner.👏👏👏👌

@NiteLynr 3 жыл бұрын

So, just found this video by accident and I have to say that it's 3/4 of an hour that is going to save me immeasurable amounts of time in the future. I've been working with spreadsheets for decades and vba for longer than I care to remember but some of this video opened my eyes wide. I've been doing things the long way apparently (to the point where I re-opened a report I made at the weekend and halved the length of the code!) I've always gone down the step-by-step route to creating vba but you've given me a few extra tools that will make things much smoother in future. On a side note: nice to hear a local(ish) accent giving advice on here, from 'Up North', myself. Plus one kudos for the C64 reference...I remember those days well, the C64 Bible (Programmer's Reference Guide) permanently resided in my schoolbag.

@Excelmacromastery 3 жыл бұрын

Thanks for the feedback. Glad you found the code useful. C64 - Those were the days.

@thearchibaldtuttle 4 жыл бұрын

Thank you for this comprehensive video!

@Excelmacromastery 4 жыл бұрын

You're welcome Archibald. Thanks for your ongoing support.

@brucelister5594 4 жыл бұрын

This video was the the most informative I've seen. I picked up some great tips for efficient program structuring. Thanks Paul.

@Excelmacromastery 4 жыл бұрын

Great to hear!

@canews8971 Жыл бұрын

Best teacher on KZfaq. My question is why store the functionality settings (events, calculations, etc) as a class rather than a module?

@RajPatelMansuria 4 жыл бұрын

The Enum for storing Column positions is an awesome technique and going to be a life saver for me. Thanks. You are doing a great job. Keep Going.

@tomharrington1453 4 жыл бұрын

Who knew? I have never seen that before. Mind blown.

@ieatglueathome 4 жыл бұрын

Thank you for your videos. You sir, are awesome.

@hichamhadj9640 4 жыл бұрын

Simply amazing and brilliant

@noambrand 3 жыл бұрын

Great Video. Been coding VBA for years with less effective solutions like using constants instead of Enum , multiple looping instead of dictionary. Finally understood how to use classes. You are the best guide to VBA. Thanks Paul! P.S I prefer to turn Debug.Assert on and off and not delete it in the release build by using: Select Tool->VBAProject Properties from the VBA menu: Conditional Compilation Argument: Debugging=1 # If Debugging Then Debug.Assert some condition # End If

@sic031 4 жыл бұрын

This is amazeballs, thank you so much, Paul!

@Excelmacromastery 4 жыл бұрын

Glad you like it!

@schlaf-michelatwork6532 Жыл бұрын

Thank you so much This was so clear and briliant and easy to understand!

@peterjames8046 Жыл бұрын

Thanks Paul, another great video. Learning a lot since i found your youtube channel

@rrdoane07 4 ай бұрын

Unbelieveable there are a lot of codes that you can use to archive something and you showed real neat codes at the end. I haven't yet run with real world data

@apbosh1 Жыл бұрын

I just use loads of global variables for everything and a few 9 tier if conditionals and loops . Clicking run is equivalent to throwing a tennis ball into a room full of loaded mouse traps, variables flying everywhere! I'm trying to do better though, this video is helping a lot.

@petercoombes6602 4 жыл бұрын

Thank you Paul. I've learned a tremendous amount from you in just 6 months. I enrolled in your ExcelMacroMastery Course and I subscribe to your Vault, which is an ongoing resource everyday I'm programming in Excel. Among the many things I've learned, what impresses me the most is the overview on structuring the code. Today's video was another great learning experience, using Enums instead of constants, love it, and creating the Function Class is another great tool to help organize and hide that bit of data. Thank you! Peter C Vancouver, BC Canada

@Excelmacromastery 4 жыл бұрын

Thanks so much Peter. Glad you enjoyed the course and my videos so much.

@tuanphan3644 4 жыл бұрын

Hey peter i live in vancouver too. Do you have any piece of advice for newbie like me?

@caraith 6 ай бұрын

Paul, always good videos. Can you now show some stuff not related to read/write data from sheet (like comparing files in two folders, backups, some file management, reading data from web page, etc)

@nazaserh 3 жыл бұрын

Very good video. Thank you!

@mike_case 4 жыл бұрын

Thank you Paul. Good rules. I like this style of coding.

@Excelmacromastery 4 жыл бұрын

Thanks Michal

@bharatjain84 4 жыл бұрын

Thanks for such an easy explanation

@midmindconsulting1298 2 жыл бұрын

Super..You are really a STAR.

@m-squaredcontractors9720 4 жыл бұрын

You are the best Paul. I enjoy your videos always. May God bless you for your good work.

@Excelmacromastery 4 жыл бұрын

Thanks very much.

@fadetowhite3538 4 жыл бұрын

very instructive content, thank you

@jen-lichen8163 3 жыл бұрын

Really like your content. And you always zoom in to the code to make it easier to follow along. Nice stuff! Thanks! One thing is, not sure if I am the only one get the "type mismatch error" for the last part: change rg to arr.. checked a few times just did not make it work..

@jonnypeace2810 3 жыл бұрын

Compared to my vba lol. A level above for sure. Always interesting see how others code. There's a lot of useful information, things I've never even touched before.

@mtotowamungu8259 4 жыл бұрын

I already feel like a pro.

@jawalim1996 4 жыл бұрын

Well explained, excellent tutorial, as usual. Thanks Paul a zillion times

@Excelmacromastery 4 жыл бұрын

Very welcome

@tha2irtalib343 2 жыл бұрын

Excellent , and thanks for sharing.

@lyonhard1 4 жыл бұрын

As always good stuff Paul. Any chance that you'll be doing a seminar on some of the techniques used in this video? Where you will do a deeper dive into the uses and nuances of things like "Enum v. CONST"?

@rjaquaponics9266 2 жыл бұрын

Excellent advice!

@Lykoskia 4 жыл бұрын

My god this is amazing... I've started learning programming casually about a year ago and I'm just getting comfortable with some intermediate stuff after laying down a foundation. After trying to wrap my head around everything I've been doing this video blew my mind... I'm actually stuck on making an application that uses your awesome advanced filter concept, trying to adapt it into being able to filter each value inside a column and export all the rows containing it into a new sheet (+ some stuff later that I know how to do already). I used to do that with loops row by row... I went to watch more of your stuff and I think it's drilling a hole into my brain, letting everything I thought I knew leak out and force feed this awesome approach. But I feel so overwhelmed, weak and stupid right now, cause basically everything I know is wrong :). It's also an inspiring feeling... Like the whole world opened up before me and I can start learning about it. I never heard of enums before, and haven't used classes. I feel so confused right now that I think I'll never get that application to work properly. But I can't help feel excited for the future. THANKS FOR EVERYTHING YOU DO!

@Excelmacromastery 4 жыл бұрын

Glad you like it.

@Lykoskia 4 жыл бұрын

@@Excelmacromastery Not only do I like it good sir BUT I DID IT!! I succeeded yesterday :) ty sooo much

@peterwooldridge7285 Жыл бұрын

Smashing.. You are a gun Paul

@coolag74 4 жыл бұрын

Feeling like a pro...

@wayneedmondson1065 4 жыл бұрын

Hi Paul.. excellent.. you said it "video is packed with information". Lots to study here. Thanks for sharing your tips and techniques for proper structured code design. Thumbs up!!

@Excelmacromastery 4 жыл бұрын

Awesome, thank you!

@godisalmighty4449 Жыл бұрын

Wonderful. Thanks a lot.

@Elbranto1 4 жыл бұрын

Thanks Paul, I am new to your channel, but really appreciate this packed full tutorial.

@Excelmacromastery 4 жыл бұрын

Glad it was helpful!

@hadibq 2 жыл бұрын

Great videos! And great content! Thanks so much.... new sub indeed!

@OmkarUmbre 4 жыл бұрын

Khatarnak boleto ekdum Zakkas.

@guilherme031109 3 жыл бұрын

I Love your job so much. You do this like incredible

@Excelmacromastery 3 жыл бұрын

Thanks. Glad you like it.

@joeburkeson8946 2 жыл бұрын

Following the advice in this video has forced me to think through what it is I'm actually trying to accomplish without becoming overwhelmed by all of the unanswered technical details. It is a structured approach which keeps you focused on the task at hand while working toward the goal of a finished project. For instance I may not know exactly how I will end up coding a particular data manipulation procedure, but I can outline the structure which continues the flow then get back and code the details stepping through the structure one thing at a time. Needless to say this helped me greatly to get back into programming . Thanks.

@Excelmacromastery 2 жыл бұрын

You're welcome Joe. Thanks for the feedback.

@cypplus2855 4 жыл бұрын

Valuable contents, thank you very much. Plenty of useful information and easy to follow your master process ... So awesome !!!

@Excelmacromastery 4 жыл бұрын

Glad you enjoyed it!

@rrrprogram8667 4 жыл бұрын

Is there is 100k like button... I just want to hit it over and over agiann..... Best video so far.. Thanks master

@Excelmacromastery 4 жыл бұрын

Thanks very much. Glad you like it.

@broleather5851 4 жыл бұрын

Tottaly agree with you

@alexsandoval7636 4 жыл бұрын

muy bueno el vídeo, espero más vídeos tuyos, son muy educativos

@Excelmacromastery 4 жыл бұрын

Thanks Alex.

@eduedu726 4 жыл бұрын

you are a master ! Thanks for the video

@Excelmacromastery 4 жыл бұрын

You're welcome!

@markarmon2883 4 жыл бұрын

This is so cool.

@faridPQ 3 жыл бұрын

Thank you so much for your great service

@Excelmacromastery 3 жыл бұрын

You're welcome Farid.

@freeliberalminds Жыл бұрын

Great video tutorial.

@MohamedEhbal 2 жыл бұрын

Thanks very much

@the_feature_selector859 4 жыл бұрын

Easily the best teacher of VBA on the internet! Thanks for putting out great content. Do you have plans to do anything on Power BI?

@Excelmacromastery 4 жыл бұрын

I don't have plans at the moment but maybe in the future. I have used PowerQuery and would like to look at it some more when time permits.

@frikduplessis3869 4 жыл бұрын

Can't wait Paul

@Excelmacromastery 4 жыл бұрын

Thanks Frik.

@frikduplessis3869 4 жыл бұрын

Thank you Paul again a fantastic fantastic video, really inspiring, I feel like coding something, 🤝

@amarnadhg6896 4 жыл бұрын

Let's go on VBA Cruise

@kebincui 8 ай бұрын

Awesome 👍，Thanks

@RijschoolRijSecure 4 жыл бұрын

Gr8 lessons 👍 plz can you make new video about macro settings. i think we gonne love it

@krn14242 4 жыл бұрын

Thanks Paul

@Excelmacromastery 4 жыл бұрын

You're welcome.

@philipmcdonnell7168 3 жыл бұрын

I sort of get the Dictionary thing but not really. I finally get the Enum thing, though! And I may even get the Class module you showed for events-handling. I just do it in an ordinary module but yours looks much more comprehensive. :)

@nedimozbey9150 2 жыл бұрын

thanks alot

@NanaAtiekuFrans 4 жыл бұрын

Thanks. excellent content.

@Excelmacromastery 4 жыл бұрын

You're very welcome!

@sdawilson 4 жыл бұрын

Excellent... Again.

@Excelmacromastery 4 жыл бұрын

Thanks Simon

@benjaminy4934 4 жыл бұрын

This is so programmer view~ Thanks!

@Excelmacromastery 4 жыл бұрын

You're welcome!

@RobertWalthery1975 2 жыл бұрын

Thank you for the video, in fact dictionaries are very useful. I've discovered them when learning python. Now I'm also using them a lot to store data in my custom program. A small question or remark, I'm also splitting my codes into small pieces but in the main program I'm always using an error handling to handle unexpected errors and preventing the program to crash. For instance if we have non numeric value. I guess it make the program more secure. What do you think ? Thank you again for the videos, they are useful Robert

@johnnyrodriguez7689 4 жыл бұрын

Cool.

@rickkeev4919 4 жыл бұрын

Great video!! Is enum a procedure or a declaration? I ask because Could you use a match function to actually find the column names or must it be a sub procedure to run?