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.
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.
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.
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!
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!
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.
*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!
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
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
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!
✔ 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.👏👏👏👌
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
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
Thank you, Paul, for this great video. Just one quick question: This example has 2 columns so u using key for company and Dic(key) for the amount. What if we have a 3rd column. How could we name then? Since dictionary has only key and value. How can we call the 3rd column ?
I'm a bit confused, watched it through, and i do not see the point for making such complicated tool for as simple task as shown. I get it is capable to to more that it is shown, but in just 30 sec i would complete the given task with out using VBA, just some written out formulas. +Unique (Company names) (+Sumifs ) * discount
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!"
Thanks 4 ur email.. for now, im trying to change my methods from Array to Collections. I have saw ur Video about Collection. May this method solve my problem on my Active listbox, select list then delete, single or multi. And the listbox can not handle refresh data after numbers entry reached up to 20
Hi ! I just arrived on your channel and i apologize in advance for exposing you to a problem. I have a workbook with several userforms and i would like to know if there is a trick to knowing which ones are in use ?!! 🥶🥶🥶🤕
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)
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!
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.
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
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.
Thank you very much for the video (and all of your others)! May I please ask, at the end you wrote 'settings.TurnOff' at the beginning of the sub but 'settings.Restore' at the end, why not settings.TurnOn?
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!
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.
Is there a way to create a macro or button that will allow you to take in some input from the user and have it output into like a template that can be copied and pasted?
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?
Wow, I keep picking up more tricks, every video. I was importing the .bas file to turn on/off the functions, now I see the class module as a new enhancement-though I need to review this video again and your Class Module trainings. Your training is just SUPERB!! Thank you. I'm curious why you import these vice using a personal Macro Workbook. Putting the enums at the top of the module, does that make them available to all modules (like public) or just that module?
Dear Sir I find your style of teaching very interesting and in depth. Therefore I want a PDF copy of your Excel VBA Hand Book, plz advise how much would that cost me. Thanks
The "Excel VBA Handbook" is actually a course although it does contain 9 pdfs. You can find all the details here including the price: theexcelvbahandbook.com/
Hello Paul. Perhaps I'm just doing it all wrong. You have uploaded several videos however it would be great to be able to follow them in a progressive sequence. Whether you prefix them numerically or have an index somewhere. Kind regards.
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"?
I got totally lost in this learning within the first 15 min. Why do we "call" something? what does "(byval ...as dictionary) as dictionary" do and why do you enter "as dictionary" twice in this statement? I guess i need an even more basic than this tutorial. any recommendations?
Awesome lesson and I'm sure even experienced developers saw something here which can improve their macros. I'm interested in knowing why you use a Class for macro performance settings. I've always just called a sub like this: Public Sub MacroMode(ByVal Toggle As Boolean) With Application .ScreenUpdating = Not Toggle .EnableEvents = Not Toggle .DisplayAlerts = Not Toggle .EnableAnimations = Not Toggle .DisplayStatusBar = Not Toggle .PrintCommunication = Not Toggle .Calculation = IIf(Toggle, xlCalculationManual, xlCalculationAutomatic) .Cursor = IIf(Toggle, xlWait, xlDefault) End With End Sub
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. :)
Is it possible to replicate this with late binding of the dictionary? I tried with the following, but I get an error with the ReadData function: Dim dict as Object Set dict = CreateObject("Scripting.Dictionary") Set dict = ReadData
Hi, do you have any clips related to unstructured data from a text file? I really want to update the MsAccess app that helps me in the Eu4 game with tons of modifiers that are stored in some files that have structure somehow similar to JSON files. I would really enjoy data mining them using VBA and automatically update my MSAccess tables. Would be really really great if you can do a clip on this topic.
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. :)
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
While it’s important to be clear on the requirements before you start coding, in the real world, the requirements are likely to change while, or after, you’ve written it. Don’t get too hung up on knowing the exact requirements because you’ll never finish an app, or never even start, if you expect to know 100% of requirements before you begin.
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.
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.
Global variables refers to scope of a variable. It is a variable that is available to the entire project. Enums are a type of constant variable. They can global using by putting the "Public" keyword before them.
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!
For adding total values of "peach" or "apple", a simple SumIf would be a billion times faster and easier if you have a large list.. Unique the list, sum if and bam... Totals for your Dictionary.
@@Excelmacromastery I haven't timed it. It was only a suggestion when dealing with the list you were working with. You manually compared "peach" to see if it was added already.. and a unique list would already have that, then a sumif would tally up all the "peach" quantities and all of the "apple" etc. I guess it depends on how large the list really is though.
The problem is that sumif reads from the worksheet and the Dictionary reads from memory. Multiple reads to the worksheet tend to be way slower than memory .
@@Excelmacromastery I can definitely see that. Thank you. I guess it depends on how big of a database you are working with. if you only have a couple thousand lines.. you are looking at 10ths of a seconds differently vs if you have 20000 lines, there can be definitely a lag time, thank you for the video, I will try the dictionary method.
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..
Let's say there are two amount columns per company. I can't seem to figure out how to return the sum of the two amounts per company. Can you include code snippets on how to do that?
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
Hi Everyone. I hope you enjoy the video. You can download the code from the link in the video description.
I have hours of work how to calculate it because when I use the same procedure as for the sums it gives me 0
Excellent
This was outstanding information - thank you
I simply cannot find this. Where is this video description?
Help please, The link to your code isn't showing me anything but a blank page
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.
Thanks Albert.
I second that. I have had to watch this in 10 minute sessions,. Its a lot to take in.
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.
I have been programming for years but these videos are hot stuff :)
So true!
Wish i had your brain. My head is spinning, but i will keep trying. Thanks for these great tutorials....
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.
Thanks Jim. Glad you liked it.
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!
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!
Hi Sir, am Rohith from India please teach Us complete array concepts in VBA and how to implement in reports to create dynamically.
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.
Thanks Bruce. Glad you found it useful.
Brilliant Video & service you give sir !! I cant thank you enough !! Thumbs Up !!
*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!
Hi, thank you so much for your great VBA tutorials. Becuase of you, I just got a raise on my job.
Nice work!
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
You're welcome. You're right about the enums.
Never thought of saving the Excel settings code as a class module, but that's really efficient and found that really helpful!
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.
Glad it was helpful!
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
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!
Thanks for the feedback Adam. Glad you liked the video.
“Many junior programmers will create a single sub with hundreds of lines of code”
I feel personally attacked somehow. 😅
We've all done it:-)
✔ 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.👏👏👏👌
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.
Who knew? I have never seen that before. Mind blown.
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
Is there is 100k like button... I just want to hit it over and over agiann..... Best video so far..
Thanks master
Thanks very much. Glad you like it.
Tottaly agree with you
I Madara the ultimate king of the world.... Again declares you as the king of VBA
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
Thank you, Paul, for this great video. Just one quick question: This example has 2 columns so u using key for company and Dic(key) for the amount. What if we have a 3rd column. How could we name then? Since dictionary has only key and value. How can we call the 3rd column ?
I'm a bit confused, watched it through, and i do not see the point for making such complicated tool for as simple task as shown. I get it is capable to to more that it is shown, but in just 30 sec i would complete the given task with out using VBA, just some written out formulas.
+Unique (Company names)
(+Sumifs ) * discount
Why don't I see the VBAProject (Create Application .xlsm) section in my window?
A great video. Will come back to it often for my education. Paul, you are a fantastic teacher.
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!"
Thanks Craig. Glad you enjoy my resources and handbook so much.
Thanks 4 ur email.. for now, im trying to change my methods from Array to Collections. I have saw ur Video about Collection. May this method solve my problem on my Active listbox, select list then delete, single or multi. And the listbox can not handle refresh data after numbers entry reached up to 20
Hi !
I just arrived on your channel and i apologize in advance for exposing you to a problem.
I have a workbook with several userforms and i would like to know if there is a trick to knowing which ones are in use ?!! 🥶🥶🥶🤕
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)
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!
Thanks for the feedback Thomas.
Very good video. Thank you!
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.
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
Great videos! And great content! Thanks so much.... new sub indeed!
40:29 why you didn't use "With Application"?
Would this have a negative impact on the code if it would be used there?
What is you name? I sure do appreciate your instructive videos. I think Paul?
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.
Happy to help!
Thank you very much for the video (and all of your others)! May I please ask, at the end you wrote 'settings.TurnOff' at the beginning of the sub but 'settings.Restore' at the end, why not settings.TurnOn?
well I applied it to my code and it seems to work just fine anyway
Let's go on VBA Cruise
Hi....How to copy excel Pivottable and paste it in Outlook new email window?
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!
You refer to a class module for the dictionary for macbooks can you please advise where I can get a copy
Hey bro I have learnt alot from your videos
One thing I want to know that why I cant use currentregion in a vba function?
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.
Great to hear Steven!
Why do you prefix code modules with mod when they are clearly under the modules folder in the project explorer?
I am coming from soft one 4.6 and I am so torn with soft soft. It seems great as it is so in-depth in its' capabilities but, so fiddly to get
Love the video however i will often need to check value data in more than one column and return it is this possible using a dictionary
Thanks for your video, I see in the Excel workbook the sum of Table 2,094,709
but in the sum of the report 1,890,933
Help me find the error.
Hi, i need to create an excel sheet for 3 more schools data maintain . is it possible?
Is there a way to create a macro or button that will allow you to take in some input from the user and have it output into like a template that can be copied and pasted?
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?
Wow, I keep picking up more tricks, every video. I was importing the .bas file to turn on/off the functions, now I see the class module as a new enhancement-though I need to review this video again and your Class Module trainings. Your training is just SUPERB!! Thank you.
I'm curious why you import these vice using a personal Macro Workbook.
Putting the enums at the top of the module, does that make them available to all modules (like public) or just that module?
Thanks Jerry. You can use public and private with enums to control their scope.
Dear Sir I find your style of teaching very interesting and in depth. Therefore I want a PDF copy of your Excel VBA Hand Book, plz advise how much would that cost me. Thanks
The "Excel VBA Handbook" is actually a course although it does contain 9 pdfs. You can find all the details here including the price: theexcelvbahandbook.com/
Khatarnak boleto ekdum Zakkas.
Is your course this fast or is it slower? I seem to get lost at this pace.
Hello Paul. Perhaps I'm just doing it all wrong. You have uploaded several videos however it would be great to be able to follow them in a progressive sequence. Whether you prefix them numerically or have an index somewhere. Kind regards.
If you look through the playlists you will see order.
@@Excelmacromastery Thank you. Do you need to follow the playlists in a sequence? I'm asking this as the playlists may be themed.
great tutorial, thanks for sharing this
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"?
I got totally lost in this learning within the first 15 min. Why do we "call" something? what does "(byval ...as dictionary) as dictionary" do and why do you enter "as dictionary" twice in this statement? I guess i need an even more basic than this tutorial. any recommendations?
Yes, Try this beginners playlist: ruclips.net/p/PL7ScsebMq5uWZ5wMUQcH2OUh_suW3vgfy
Awesome lesson and I'm sure even experienced developers saw something here which can improve their macros. I'm interested in knowing why you use a Class for macro performance settings. I've always just called a sub like this:
Public Sub MacroMode(ByVal Toggle As Boolean)
With Application
.ScreenUpdating = Not Toggle
.EnableEvents = Not Toggle
.DisplayAlerts = Not Toggle
.EnableAnimations = Not Toggle
.DisplayStatusBar = Not Toggle
.PrintCommunication = Not Toggle
.Calculation = IIf(Toggle, xlCalculationManual, xlCalculationAutomatic)
.Cursor = IIf(Toggle, xlWait, xlDefault)
End With
End Sub
Hi Paul,
I'm wondring why need 2 subs Backup & Restore. :)
To save the current settings and restore them.
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. :)
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.
It seems the book is only for sale with the course?
Is it possible to replicate this with late binding of the dictionary?
I tried with the following, but I get an error with the ReadData function:
Dim dict as Object
Set dict = CreateObject("Scripting.Dictionary")
Set dict = ReadData
Wonderful. Thanks a lot.
Hi, do you have any clips related to unstructured data from a text file? I really want to update the MsAccess app that helps me in the Eu4 game with tons of modifiers that are stored in some files that have structure somehow similar to JSON files. I would really enjoy data mining them using VBA and automatically update my MSAccess tables. Would be really really great if you can do a clip on this topic.
The result is not correct, pls see it again. Tks!
Thanks Paul. 👏 Your tutorials are stunning. I could learn many useful VBA techniques from you. Go on please. Thanks indeed.
Thanks, will do!
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. :)
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
While it’s important to be clear on the requirements before you start coding, in the real world, the requirements are likely to change while, or after, you’ve written it. Don’t get too hung up on knowing the exact requirements because you’ll never finish an app, or never even start, if you expect to know 100% of requirements before you begin.
Hmm I thought dict means you would sort by alphabet so apple cones first ??👀🤔
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
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.
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.
Sir, using VBA code can I determine if an excel file has a header row to be used for uploading the data into a database? Thanks a bunch 🙏🏿
Best teacher on RUclips. My question is why store the functionality settings (events, calculations, etc) as a class rather than a module?
Smashing..
You are a gun Paul
Thanks Paul, another great video. Learning a lot since i found your youtube channel
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?
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.
Super..You are really a STAR.
sorry Paul, but you go too fast.
thanks for your video though.👍
Tap the Settings gear, choose play speed, select 75%. I do it all the time.
What is difference between enum and global variables?
Global variables refers to scope of a variable. It is a variable that is available to the entire project.
Enums are a type of constant variable. They can global using by putting the "Public" keyword before them.
Thanks very much
Awesome 👍，Thanks
Vey helpful.,
How to search value using the input ?
Hi, thanks for the video. Is it faster to read from text file using arrays? Do you have a video about it?
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!
Great video tutorial.
Sir any good recommended links to beginner books ..videos ..will be greatly appreciated..
Check out this tutorial: excelmacromastery.com/vba-tutorial-1/
@@Excelmacromastery Thank you sir ..will follow up
For adding total values of "peach" or "apple", a simple SumIf would be a billion times faster and easier if you have a large list.. Unique the list, sum if and bam... Totals for your Dictionary.
Have you written the code and timed it? Because I would love to see it and compare speed.
@@Excelmacromastery I haven't timed it.
It was only a suggestion when dealing with the list you were working with.
You manually compared "peach" to see if it was added already.. and a unique list would already have that, then a sumif would tally up all the "peach" quantities and all of the "apple" etc.
I guess it depends on how large the list really is though.
The problem is that sumif reads from the worksheet and the Dictionary reads from memory. Multiple reads to the worksheet tend to be way slower than memory .
@@Excelmacromastery I can definitely see that. Thank you.
I guess it depends on how big of a database you are working with. if you only have a couple thousand lines.. you are looking at 10ths of a seconds differently vs if you have 20000 lines, there can be definitely a lag time, thank you for the video, I will try the dictionary method.
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..
really great job mate! Are you planning to create a python course as well? Would be great...
8:21 Sorry, I'm childish...
Let's say there are two amount columns per company. I can't seem to figure out how to return the sum of the two amounts per company. Can you include code snippets on how to do that?
Great Video! Is it possible for the filtered results to be copies to the bottom of an existing table in the same worksheet?
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