Really great video, please make more of these. Have you thought about a coding challenge where you provide the information about what the application should do with the data, and then we all start working on building our applications? Later, we could send the applications to you for review and comparison, followed by an audit of the code.
Your examples and emphasis on the use of arrays and dictionaries have made the biggest impact on the performance of my code to date. I look forward to more of these sessions, especially the one on "WithEvents". 😁
Hi Paul - good video. Very useful to see better alternatives to original code shown. I used to declare all my variables at the top as that was the way I got from a lesson somewhere in the distant past., but having followed you now for a number of years I changed to declaring variables just before they are needed and find this much easier to read and troubleshoot. I have vastly improved the efficiency of a lot of my VBA projects via your coding examples and tips. Use of dictionaries has been particularly helpful in increasing running speeds but this is only a fraction of the benefits I have learned from your tutorials and videos. Look forward to watching the next video. Thank you.
Thank you for simplifying the intricacies of VBA code auditing. Your clear explanations and systematic approach make it easier for learners to understand and apply these concepts.Your tutorial not only educates but empowers developers to refine their VBA coding skills. The emphasis on code efficiency and optimization is a game-changer for those in the development sphere.
Very instructive video! I have always learned best by watching others and reading others' code. I would indeed like to see a video about adding all the buttons to one click event. I have a macro workbook with over 20 buttons (one for each kind of parameter a user can choose) and maintaining it is rather unwieldy.
Thank you for sharing you knowledge of VBA. I find reviewing old code to be extremely beneficial as it helps us learn and evolve as programmers. I appreciate the manner you did this video which also allows for insight into how other programmers approach an project. Please make more of these.
Genuinely a good approach gives a handful of real world examples of people doing what they're doing, rather than the same awfully abstract repeat examples functions as a good way to run through a bunch of functionality without going into too much detail by referencing other videos with that detail
interesting and entertaining video. This kind of lessons of style makes me think of art lessons, where good habits and techniques make come out to the observer what you meant.
I like this type of video, good to learn from other's mistakes! But I would rather see 1 example with a more detailed re-write to a more efficient code. Thank you.
I've heard recently from another source about keeping a sub or function to 20 lines or less. I was wondering why that is? Thanks and keep up the good work.
Thank you. I like the format and you have reinforced some of the things my current programming mentor keeps scolding me about. I will be on the look out for the next one.
Great video as usual! I know you've mentioned this before about the advantage of declaring variables just before you use them, but damn, that's so tough for me to do after decades of declaring my variables at the top of the module. Old habits die hard!
I enjoyed the idea of figuring out how to improve your code. Somethings you did, I do but the guy that also works here with VBA don't and I tend to have trouble understanding his code. It appears that I'm bragging when I say my code seems easier to read.
Thank you for this great video. Pls could you make another about OLEDB because it doesn't work with a directory in sharepoint if you need to get data from in your workbook. Thank you
Hi. I was try to find something like transparent listbox or label(this exist but when click you have back color anyway). There is any chance disable click event on label or make list box transparent ?. Please help if you can. Your tutorial are so good and maybe there i will find a solution. Thanks and your tutorail teach me a lot.
Mr. Paul I want to know how can I add a new column as running balance which is equal to debit - credit to my filtered data at run time then I write it back to output sheet. Thanks in advance
Hi Paul, great initiative. Keep it on! Always good to learn how to write and compact the code in a structured way. I still struggle about how to write class module, (Get, Let, ...) still confuse me when to use.
Cool, thanks! Think of like this: In VBA the full way to assign a basic variable is: Let count = 5 and to assign an object variable is: Set book = ActiveWorkbook So Let myClass.Count = 5 Set myClass.Book = ActiveWorkbook
I have learnt a lot from your channel! One thing I haven't been able to overcome in excel is when I import a csv file and some fields with IDs gets converted into either a date or a number (Like 01E3 getting converted to 1000) and the original ID is lost. Do you know of a solution for this? Thanks for a great channel!
@@yokoyama7590 It didn't help, but I found a solution. I suspect that the solution fixed the problem that was overriding your suggestion. I found this line in querytable.add. .TextFileColumnDataTypes = Array(1, 1, 1) Changing a 1 to xlTextFormat sets that column to Text. Maybe the whole line is not necessary and then your line would work?
Thanks Mr. Paul for this video. I wish you would be interested in programming Excel through Python. Can a trading simulator be made on Excel through Python, as you had previously done through VBA?
Paul - thanks for the video. A good way to improve my code by watching best practice! Another idea for a video - sharing the results of a VBA code (a report, a status update,…) gets more and more important. But people do not want to share via e-mail. They request sharing via Teams/SharePoint (private chat, general into a specific channel (as message), direct upload to a SharePoint library, directly into a SharePoint list, as text with/wo attachment,…). Right now I can‘t find any useful way which can be implemented into my vba routines and also no TH-cam tutorials… Maybe something like this would also be helpful for many business users? Any ideas on this? Best, Fritz
Does VBA support the concept of multitasking, multithreading or asynchronous programming? For example, if I send a set of messages to a group of friends through Excel, will all the messages be sent at once, or will the execution be synchronous, meaning that each message will be sent one after the other?
It doesn't have native multithreading although there are workarounds. Regarding sending messages - if you send emails from excel they are typically sent using outlook.
Globals get a lot of heat, but have made my professional projects much easier to manage. From my experience, it seems that VBA doesn’t respect nested scope like other languages, and the only alternative to globals is passing variables to each sub in a process chain. What am I missing?
@@Excelmacromastery I haven’t worked with class modules, but your description doesn’t sound terribly different from my current project schema. Are there other benefits to building a module as a class?
I meant, can Excel perform several tasks at the same time, for example, sending several WhatsApp messages to a number of users at once, and not message by message?
@@ExcelmacromasteryEasily know how long is code. In addition, line number can easily be added to msgbox to display the line number & error code. Hope it makes sense.
@@Excelmacromastery just a few examples from my practice: + often there is a need to declare several variables of the same type, so it is easier to follow certain name convension when they are in one place + quite often I have no idea where the variable will be used for the 1st time) + easer to identify obsolete/unused variables when they are groupped at the start of sub/function, also this section is a quick reference to local variable vs. global vs. sub/function parameters Yes, this is mainly applicable for the complex projects, which I am into at my work)
Really great video, please make more of these. Have you thought about a coding challenge where you provide the information about what the application should do with the data, and then we all start working on building our applications? Later, we could send the applications to you for review and comparison, followed by an audit of the code.
Practical use cases. That's what needed on this channel!
Your examples and emphasis on the use of arrays and dictionaries have made the biggest impact on the performance of my code to date. I look forward to more of these sessions, especially the one on "WithEvents". 😁
Thanks David.
Hi Paul - good video. Very useful to see better alternatives to original code shown. I used to declare all my variables at the top as that was the way I got from a lesson somewhere in the distant past., but having followed you now for a number of years I changed to declaring variables just before they are needed and find this much easier to read and troubleshoot. I have vastly improved the efficiency of a lot of my VBA projects via your coding examples and tips. Use of dictionaries has been particularly helpful in increasing running speeds but this is only a fraction of the benefits I have learned from your tutorials and videos. Look forward to watching the next video. Thank you.
Thank you for simplifying the intricacies of VBA code auditing. Your clear explanations and systematic approach make it easier for learners to understand and apply these concepts.Your tutorial not only educates but empowers developers to refine their VBA coding skills. The emphasis on code efficiency and optimization is a game-changer for those in the development sphere.
Glad you liked it.
Very instructive video! I have always learned best by watching others and reading others' code. I would indeed like to see a video about adding all the buttons to one click event. I have a macro workbook with over 20 buttons (one for each kind of parameter a user can choose) and maintaining it is rather unwieldy.
It's on my list
I learnt a lot from the video. Thank you. I look forward to videos of a similar format 👍
Great video. Its nice to see how you go about improving / refactoring existing macros. Please do more code reviews. I learnt a lot. Thanks!
Thanks, will do!
Thank you for sharing you knowledge of VBA. I find reviewing old code to be extremely beneficial as it helps us learn and evolve as programmers. I appreciate the manner you did this video which also allows for insight into how other programmers approach an project. Please make more of these.
Thanks Michael. More on the way.
Genuinely a good approach gives a handful of real world examples of people doing what they're doing,
rather than the same awfully abstract repeat examples
functions as a good way to run through a bunch of functionality without going into too much detail by referencing other videos with that detail
Great format Paul!
Thanks
Really great content. Especially the last minute is crucial. Thanks for your effort.
Great video! Its good to know best software practices
Glad you think so!
interesting and entertaining video. This kind of lessons of style makes me think of art lessons, where good habits and techniques make come out to the observer what you meant.
I like this type of video, good to learn from other's mistakes! But I would rather see 1 example with a more detailed re-write to a more efficient code. Thank you.
Thank you so much. I will have to watch this several times, especially the library subject.
You're welcome
Brilliant. Love this way of explaining how to improve technique.
Thanks
I've heard recently from another source about keeping a sub or function to 20 lines or less. I was wondering why that is? Thanks and keep up the good work.
Thank you. I like the format and you have reinforced some of the things my current programming mentor keeps scolding me about. I will be on the look out for the next one.
You're welcome
Great format. I learned a bunch of small but useful tricks.
Another great video! Thank you! And please make more of these!!
Thanks Marcel
great video indeed. The "withevents" class idea is rather attractive and a video on this topic would be nice.
Thanks for the feedback
Great video as usual! I know you've mentioned this before about the advantage of declaring variables just before you use them, but damn, that's so tough for me to do after decades of declaring my variables at the top of the module. Old habits die hard!
It is hard to change habits after so long:) On the plus side I think you will see the benefits pretty quickly
Incredible!! you are a really master mind on VBA!! thanks for sharing this video.
Wow, thank you!
I enjoyed the idea of figuring out how to improve your code. Somethings you did, I do but the guy that also works here with VBA don't and I tend to have trouble understanding his code. It appears that I'm bragging when I say my code seems easier to read.
That's great that your code is readable.
Awesome video and enjoy the refactor format. Well done, Paul.
Glad you enjoyed it
Thank you for this great video. Pls could you make another about OLEDB because it doesn't work with a directory in sharepoint if you need to get data from in your workbook.
Thank you
Great idea for video and love the format. Very educational to use real world examples.
Thanks
Great Video thanks..!!
Yes need more videos like this👍
More to come!
awesome, looking forward for more
Thanks. Gladc you liked it.
Excellent
Thanks
Hi. I was try to find something like transparent listbox or label(this exist but when click you have back color anyway). There is any chance disable click event on label or make list box transparent ?. Please help if you can. Your tutorial are so good and maybe there i will find a solution. Thanks and your tutorail teach me a lot.
Mr. Paul I want to know how can I add a new column as running balance which is equal to debit - credit to my filtered data at run time then I write it back to output sheet. Thanks in advance
Hi Paul, great initiative. Keep it on!
Always good to learn how to write and compact the code in a structured way.
I still struggle about how to write class module, (Get, Let, ...) still confuse me when to use.
Cool, thanks!
Think of like this: In VBA the full way to assign a basic variable is:
Let count = 5
and to assign an object variable is:
Set book = ActiveWorkbook
So
Let myClass.Count = 5
Set myClass.Book = ActiveWorkbook
I have learnt a lot from your channel! One thing I haven't been able to overcome in excel is when I import a csv file and some fields with IDs gets converted into either a date or a number (Like 01E3 getting converted to 1000) and the original ID is lost. Do you know of a solution for this? Thanks for a great channel!
Did you try setting the sheet to text format before the import? I use sheet.Cells.NumberFormat = "@" where sheet is the sheet reference.
@@yokoyama7590 It didn't help, but I found a solution. I suspect that the solution fixed the problem that was overriding your suggestion. I found this line in querytable.add.
.TextFileColumnDataTypes = Array(1, 1, 1)
Changing a 1 to xlTextFormat sets that column to Text. Maybe the whole line is not necessary and then your line would work?
you're a true hero
Thanks
Thanks Mr. Paul for this video.
I wish you would be interested in programming Excel through Python.
Can a trading simulator be made on Excel through Python, as you had previously done through VBA?
Of course.
I hope you can program the same idea on Excel, but through Python.@@Excelmacromastery
Do you think it will have the same performance and speed?@@Excelmacromastery
Paul - thanks for the video. A good way to improve my code by watching best practice!
Another idea for a video - sharing the results of a VBA code (a report, a status update,…) gets more and more important. But people do not want to share via e-mail. They request sharing via Teams/SharePoint (private chat, general into a specific channel (as message), direct upload to a SharePoint library, directly into a SharePoint list, as text with/wo attachment,…). Right now I can‘t find any useful way which can be implemented into my vba routines and also no TH-cam tutorials… Maybe something like this would also be helpful for many business users? Any ideas on this? Best, Fritz
Glad you like the video. Thanks for the suggestion.
Great stuff Paul
Thanks
Does VBA support the concept of multitasking, multithreading or asynchronous programming?
For example, if I send a set of messages to a group of friends through Excel, will all the messages be sent at once, or will the execution be synchronous, meaning that each message will be sent one after the other?
It doesn't have native multithreading although there are workarounds. Regarding sending messages - if you send emails from excel they are typically sent using outlook.
Globals get a lot of heat, but have made my professional projects much easier to manage. From my experience, it seems that VBA doesn’t respect nested scope like other languages, and the only alternative to globals is passing variables to each sub in a process chain. What am I missing?
You can use class modules. Declaring a member variable makes it available to all class methods.
@@Excelmacromastery I haven’t worked with class modules, but your description doesn’t sound terribly different from my current project schema. Are there other benefits to building a module as a class?
Great video
Glad you like it
I meant, can Excel perform several tasks at the same time, for example, sending several WhatsApp messages to a number of users at once, and not message by message?
I applaud your patience, the original code was unreadable
To be fair, everyone writes bad code in the beginning. It's being willing to improve that's important.
Great video. How about adding line numbers?
For what purpose?
@@ExcelmacromasteryEasily know how long is code. In addition, line number can easily be added to msgbox to display the line number & error code. Hope it makes sense.
Italians! 😂
Sorry I'm out, declaring variables as they are used just sucks.
Why?
@@Excelmacromastery just a few examples from my practice:
+ often there is a need to declare several variables of the same type, so it is easier to follow certain name convension when they are in one place
+ quite often I have no idea where the variable will be used for the 1st time)
+ easer to identify obsolete/unused variables when they are groupped at the start of sub/function, also this section is a quick reference to local variable vs. global vs. sub/function parameters
Yes, this is mainly applicable for the complex projects, which I am into at my work)