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!
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.
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.
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 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.
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.
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.
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?
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"
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.
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 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 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?
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 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!
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.
Very usefull and informative channel. Thank you so much for this
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.
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 ?
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.
The way of your teaching is really awesome..Sir 🙏
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.
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.
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.
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.
Thank you Paul, great lesson :)
Im not into this level but this is amazing, optimizing to 10 times faster is insane
Hello,
When you used the enum for the magic numbers, why did you split one enum for rows and one for columns?
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.
This is a fantastic video! I really need to play around more with dictionaries and arrays.
Very informative content Mr Paul 👍
OMG, You are really Genius...! I don't how you remembering all those functions in a fingerprint.
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?
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"
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.
Does this work on Excel for Mac
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.
thanks so much.
Excelente!
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.
Good Morning. Please make a code for add and updating data in listbox
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?
I really need to get "CurrentRegion.Value" tattooed on my arm
😂😂
Another great video 👍🏻
Thanks again!
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
I learnt that fruit is expensive in the UK. ✌😀
I love you
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.
It's too easy to hack excel vba password. Can you help us with tricks to lock vba at a better level???
🤟
Jj