Create an Excel VBA Application Like a Pro
HTML-код
- Опубликовано: 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: bit.ly/3ayhvRf
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
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.
#vbaoptimizecode #vbaoptimize
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...)
#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
I hope you enjoy this video.
You can download the source code from the video description.
this is from a question i asked you a few week back, honored to have the video
What problem are you having?
You're welcome Nick.
you are the Best VBA master i v seen
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!
Very usefull and informative channel. Thank you so much for this
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.
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.
Thank you Paul, great lesson :)
This is a fantastic video! I really need to play around more with dictionaries and arrays.
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.
The way of your teaching is really awesome..Sir 🙏
Very informative content Mr Paul 👍
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.
OMG, You are really Genius...! I don't how you remembering all those functions in a fingerprint.
Hi Paul.. excellent tutorial.. still working my way through it. Lots of learning going on. Stay safe.. thanks.. and thumbs up!!
Thanks a lot Wayne.
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.
Thanks for the feedback Jim. When a project starts to grow you really notice the benefits of the enums.
Im not into this level but this is amazing, optimizing to 10 times faster is insane
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.
Thanks Frik.
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.
Thanks John. I hope you are keeping well.
Nice! This is what I miss in many Excel solutions! Clean-up, refactoring and performance considerations are mostly unknown to macro recorder babies.
Thanks Archibald.
Excelente!
thanks so much.
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 ?
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.
Hello,
When you used the enum for the magic numbers, why did you split one enum for rows and one for columns?
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.
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
great use of dictionary.
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"
Another great video 👍🏻
Thanks again!
I love you
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. 😕
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?
I really need to get "CurrentRegion.Value" tattooed on my arm
😂😂
I learnt that fruit is expensive in the UK. ✌😀
Good Morning. Please make a code for add and updating data in listbox
Does this work on Excel for Mac
🤟
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.
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...
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
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.
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?
ShFruit should be a worksheet object.
@@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?
It's too easy to hack excel vba password. Can you help us with tricks to lock vba at a better level???
Jj