Create an Excel VBA Application Like a Pro

Рет қаралды 38,084

Күн бұрын

How to Code and Optimize an Excel VBA application from Scratch
In this video I create a very interesting VBA application from scratch. Then I optimize it for speed and so that it is much easier to update in the future.
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: Get the VBA code for this video here: (bit.ly/3ayhvRf)
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...)
#VBAApplication #ExcelVBAapplication #VBAproject
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

Пікірлер: 59

@Excelmacromastery 4 жыл бұрын

I hope you enjoy this video. You can download the source code from the video description.

@nicholasniedzielski4707 4 жыл бұрын

this is from a question i asked you a few week back, honored to have the video

@Excelmacromastery 4 жыл бұрын

What problem are you having?

@Excelmacromastery 4 жыл бұрын

You're welcome Nick.

@tangsh497 3 жыл бұрын

you are the Best VBA master i v seen

@jlmcconchie 3 жыл бұрын

This is simply the most valuable video I have ever seen for “amateur” Programmers. The lessons it teaches about how to organize the construction of a program are invaluable and I suspect it will be the rare programmer who will be be efficient and timely without using the methodology used here. Thanks!

@anxonpues6018 2 жыл бұрын

I had sawn this file maybe 6 or 7 times, one reason is that my language is spanish and you speak clear but sometimes faster than I can listen on time, but the stronger reason to review this LECTURE is that I will put it as the best exposition on the subject to write not vba for excel it's the BEST EXPOSITION ON HOW TO WRITE SOFTWARE. With all my respect to a good teacher CONGRATULATIONS.

@Diallo268 4 жыл бұрын

I just like using the timer function. This is definitely a helpful video. I found a more crude way to use arrays years ago, but I've learned a lot since then.

@mike_case 4 жыл бұрын

Thank you Paul, great lesson :)

@alterchannel2501 2 жыл бұрын

Very usefull and informative channel. Thank you so much for this

@duaneorlovski 4 жыл бұрын

This is a fantastic video! I really need to play around more with dictionaries and arrays.

@rods6405 4 жыл бұрын

Thanks for this needed a refresher. The funny thing is is all stupid adds for "monday dot com and online excel course" that youtube puts on before these Excel Macro Mastery videos when there is so much info in Excel Macro Mastery videos for free. Yep I have never used dictionary or collection I have always had more than 3 fields in all my work and home stuff. Lately if I have the data in a excel table or sheet I then use the Find All feature on the range to find matches to a data string it then returns a array of all the row matches it is faster than a for loop plus it looks across all cells in the range which is handy in the Sales application because you might want to return everything related to a model number or brand name.

@jimfitch 4 жыл бұрын

Wow! Thank you, Paul. I have some very large applications with lots of interaction with different worksheets, & I often redesign the worksheets to add functionality, improve display, etc. I have struggled mightily with how best to write worksheet/range/cell addresses in the code to facilitate code updates when changing worksheet layouts. Enums is such an elegant solution, especially with your demonstration’s use of a separate modConstants module & different enumerations to group those constants by category. Also, thank you for the demonstration of class module. I scratched my head at first asking “why is this better?” Then, I watched that section again, & it made sense. Such a simple technique that makes the main code easier to understand.

@Excelmacromastery 4 жыл бұрын

Thanks for the feedback Jim. When a project starts to grow you really notice the benefits of the enums.

@ijimlyn 4 жыл бұрын

Thank you for sharing your knowledge. I’ve been searching for an advance tutorials like this and finally I found your channel. I would also like to request your expertise on table macros like how to add row in the table, deleting, updating etc..because this area Is prone to error for whatever reason...i hope you can add a tutorial to that.

@JohnOvens 4 жыл бұрын

Paul, this is fantastic - I plan to optimise some of the code that I have written for some personal projects. You have inspired me to do better.

@Excelmacromastery 4 жыл бұрын

Thanks John. I hope you are keeping well.

@frikduplessis3869 4 жыл бұрын

Morning Paul, How refreshing and motivating was this video 🤝 from a lockdown SA, Thank you so much this really made my day👌 and will help optimizing my new project I'm so grateful.

@Excelmacromastery 4 жыл бұрын

Thanks Frik.

@shoki92111 4 жыл бұрын

The way of your teaching is really awesome..Sir 🙏

@wayneedmondson1065 4 жыл бұрын

Hi Paul.. excellent tutorial.. still working my way through it. Lots of learning going on. Stay safe.. thanks.. and thumbs up!!

@Excelmacromastery 4 жыл бұрын

Thanks a lot Wayne.

@shashivishwakarma6302 4 жыл бұрын

Very informative content Mr Paul 👍

@thearchibaldtuttle 4 жыл бұрын

Nice! This is what I miss in many Excel solutions! Clean-up, refactoring and performance considerations are mostly unknown to macro recorder babies.

@Excelmacromastery 4 жыл бұрын

Thanks Archibald.

@KarthikKAVE 4 жыл бұрын

OMG, You are really Genius...! I don't how you remembering all those functions in a fingerprint.

@zx-25r12 4 жыл бұрын

Im not into this level but this is amazing, optimizing to 10 times faster is insane

@jcare07 3 жыл бұрын

Excelente!

@Huynh-Tan-Quoc 2 жыл бұрын

thanks so much.

@jrcryo 4 жыл бұрын

Hello, When you used the enum for the magic numbers, why did you split one enum for rows and one for columns?

@nadermounir8228 10 ай бұрын

This is a perfect video. Just a quick question: do we have to create a class module in case we have more than 2 columns for a dictionary ?

@serdip 3 жыл бұрын

Thanks for posting this very informative and interesting video! It demonstrates a lot of advanced techniques that make for a robust and elegant solution. I would imagine that using named ranges might make the code more flexible. The problem of how to return multiple items from a function was solved in the video using a class, which is very elegant. Would you also consider returning an array or a dictionary a viable alternative? I think that it might be helpful if a procedure in the Workbook_Open event were to be called that would read all of the source data into a global array. That way it wouldn't be necessary to read the source data each time the Report button is clicked.

@serdip 3 жыл бұрын

I did have some difficulty understanding adding rows to the array. I will have to rewatch the explanation of this technique in order to have a better grasp of the concept. I personally used a somewhat different approach to this solution, which is for me a bit easier. Leaving out most of the details, I propose the following broad outline to solve the problem: 1) Read the source data into a dictionary whose keys are the fruits and whose items are arrays of the dollar amounts from the Total column. If "Banana" occurs 4 times in the source data, the dictionary item for the key "Banana" will be an array having 4 elements 2) For each fruit in the criteria range , use the Max value to determine the number of rows in the output range for that fruit. 3) Populate the first column of the output range for a given fruit with that fruit. 4) Populate the second column of the output range for a given fruit with the elements of the array in the dictionary corresponding to that fruit, transposed of course. Note: If the array has 5 elements for "Banana" but the Max criteria is 3, the number of rows in the output range for "Banana" will be 3 and only the first 3 elements of the array will be assigned to the output range for "Banana". In other words, if the size of the range is less than the size of the array being assigned to it, Excel will just use the first N elements of the array and ignore the rest. Thank you kindly.

@konatesiakamounir8704 4 жыл бұрын

Thank you, teacher, for this course but I have a problem I have hours of work in my table instead of the sums that can help me calculate this? I want to speak this video "How to design and code an Excel VBA application like a pro"

@Rkeev1 4 жыл бұрын

great use of dictionary.

@rods6405 4 жыл бұрын

I really need to get "CurrentRegion.Value" tattooed on my arm

@Excelmacromastery 4 жыл бұрын

😂😂

@frankh2357 4 жыл бұрын

Is there a way to speed up Pivot Filter looping? I have code where my buttons apply filtering for reports. But it generally takes 30-45 sec to loop through my pivot tables. 😕

@jlute10 4 жыл бұрын

Does this code actually do what is described in the beginning about getting the max? Seems to me like it gets the first number of each fruit. For instance @19:54 you can see it is supposed to get the top 2 Bananas (which manual evaluation would be 7291 and 7109) but it returns 7291 and 1937. Perhaps a sort function could be added before your expiation method is used such that the highest values per fruit are always on top?

@grahamparker7729 4 жыл бұрын

Another great video 👍🏻

@Excelmacromastery 4 жыл бұрын

Thanks again!

@kike6691 3 жыл бұрын

I love you

@konatesiakamounir8704 4 жыл бұрын

Dim key As Variant, amount As Long For Each key In dict amount = dict(key) If amount > 150000 Then dict(key) = CLng(amount - (amount * 0.1)) End If Next key Set ApplyDiscount = dict I want to calculate hours instead of sums

@philipsombilon8519 3 жыл бұрын

Good Morning. Please make a code for add and updating data in listbox

@MGDLProductions 4 жыл бұрын

Hi Paul, once again an excellent video. I'm confused by the function AddArrayRow and how it is used in the ReadData. Obviously it works, but for me I expected it to look something like this: result = AddArrayRow(result, row, arr, i) Can you explain how/where the variable result get the data in your line: AddArrayRow result, row, arr, i I haven't seen this kind of use before, I expect a equal sign like in my example. Can't figure it out...

@Excelmacromastery 4 жыл бұрын

That's a great question Mikael. When an array is passed as parameter we are passing a reference to an array. This means that when we change the array in ArrArrayRow we are changing the original array. We don't need to pass it back from the function. From a readability point of view it is better but doing it slows the code down a lot. -Paul

@redangrybird7564 4 жыл бұрын

I learnt that fruit is expensive in the UK. ✌😀

@tughanozsezer9332 4 жыл бұрын

🤟

@davidkirk7769 3 жыл бұрын

Does this work on Excel for Mac

@imranbhatti8580 4 жыл бұрын

Hello Paul! Nice video. But these libraries (collection and dictionary) are not suitable when it comes to larger data sets. I always try to avoid loops What you do for huge data sets? say 50000+ record sets.

@jnorton47a 2 жыл бұрын

I am having a problem that I need help with. When I get to the line of code Set "rg = shFruit.Range("F3").CurrntRegion" I get a "Variable not defined" error. The code looks exactly like the code in the video. What do I have wrong?

@Excelmacromastery 2 жыл бұрын

ShFruit should be a worksheet object.

@jnorton47a 2 жыл бұрын

@@Excelmacromastery Sorry I still do not get it. Do you mean the sheet needs to be named Fruit? If so I still get the same error. Can you please be more specific?

@FredrikDahlberg 4 жыл бұрын

Your videos are awesome. But please, check your mic. Watching your this video with a subwoofer was a pain. Check how you handle the sound. The whole room shakes when you hammer your keyboard. Not ok.