Can This Code Be Saved? | VBA Code Audit

[@chieffireseal7212]

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.

[@rayearth9760]

Practical use cases. That's what needed on this channel!

[@philhamilton6926]

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.

[@davidrazorsek7849]

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". 😁

[@Excelmacromastery]

Thanks David.

[@michaelharpole9416]

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.

[@Excelmacromastery]

Thanks Michael. More on the way.

[@excelautomationtool]

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.

[@Excelmacromastery]

Glad you liked it.

[@idogus]

Really great content. Especially the last minute is crucial. Thanks for your effort.

[@vikits21]

Great video. Its nice to see how you go about improving / refactoring existing macros. Please do more code reviews. I learnt a lot. Thanks!

[@Excelmacromastery]

Thanks, will do!

[@BuildFriendly]

Brilliant. Love this way of explaining how to improve technique.

[@Excelmacromastery]

Thanks

[@nancykay3109]

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.

[@Excelmacromastery]

It's on my list

[@x4ms]

Thank you so much. I will have to watch this several times, especially the library subject.

[@Excelmacromastery]

You're welcome

[@kenrichardson2781]

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.

[@Excelmacromastery]

You're welcome

[@R72Investments]

Another great video! Thank you! And please make more of these!!

[@Excelmacromastery]

Thanks Marcel

[@reng7777]

Incredible!! you are a really master mind on VBA!! thanks for sharing this video.

[@Excelmacromastery]

Wow, thank you!

[@graytonw5238]

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!

[@Excelmacromastery]

It is hard to change habits after so long:) On the plus side I think you will see the benefits pretty quickly

[@jimfisher349]

Great format. I learned a bunch of small but useful tricks.

[@karolispulokas1364]

I learnt a lot from the video. Thank you. I look forward to videos of a similar format 👍

[@Pedritox0953]

Great video! Its good to know best software practices

[@Excelmacromastery]

Glad you think so!

[@deonvanniekerk7700]

Great format Paul!

[@Excelmacromastery]

Thanks

[@vbaclasses3553]

Awesome video and enjoy the refactor format. Well done, Paul.

[@Excelmacromastery]

Glad you enjoyed it

[@astollie]

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.

[@SaberTooth2251]

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

[@mtstans]

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.

[@godisalmighty4449]

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

[@Robbo-tron]

Great idea for video and love the format. Very educational to use real world examples.

[@Excelmacromastery]

Thanks

[@AlexFariaOliveira]

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.

[@Excelmacromastery]

That's great that your code is readable.

[@annasaheb9678]

Great Video thanks..!! Yes need more videos like this👍

[@Excelmacromastery]

More to come!

[@elwont]

you're a true hero

[@Excelmacromastery]

Thanks

[@s1ngularityxd64]

awesome, looking forward for more

[@Excelmacromastery]

Thanks. Gladc you liked it.

[@nicor1501]

great video indeed. The "withevents" class idea is rather attractive and a video on this topic would be nice.

[@Excelmacromastery]

Thanks for the feedback

[@robbe58]

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.

[@Excelmacromastery]

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

[@b99eu]

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 KZbin tutorials… Maybe something like this would also be helpful for many business users? Any ideas on this? Best, Fritz

[@Excelmacromastery]

Glad you like the video. Thanks for the suggestion.

[@podden22]

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]

Did you try setting the sheet to text format before the import? I use sheet.Cells.NumberFormat = "@" where sheet is the sheet reference.

[@podden22]

@@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?

[@pawemajewski2430]

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.

[@edoggamus]

Great stuff Paul

[@Excelmacromastery]

Thanks

[@zakimoustapha7296]

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

[@hammeedabdo.82]

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?

[@Excelmacromastery]

Of course.

[@hammeedabdo.82]

I hope you can program the same idea on Excel, but through Python.@@Excelmacromastery

[@hammeedabdo.82]

Do you think it will have the same performance and speed?@@Excelmacromastery

[@rond9222]

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.

[@MUHAMMADNASIR-tv2jx]

Excellent

[@Excelmacromastery]

Thanks

[@quenchedthirst1]

Great video

[@Excelmacromastery]

Glad you like it

[@aboanasalhamdany]

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?

[@Excelmacromastery]

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.

[@Bhavik_Khatri]

Great video. How about adding line numbers?

[@Excelmacromastery]

For what purpose?

[@Bhavik_Khatri]

@@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.

[@baphnie]

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]

You can use class modules. Declaring a member variable makes it available to all class methods.

[@baphnie]

@@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?

[@aboanasalhamdany]

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?

[@MC_Kun]

I applaud your patience, the original code was unreadable

[@Excelmacromastery]

To be fair, everyone writes bad code in the beginning. It's being willing to improve that's important.

[@clandeszipp4564]

Italians! 😂

[@jameswilliams5045]

Sorry I'm out, declaring variables as they are used just sucks.

[@Excelmacromastery]

Why?

[@AlexZabelkin]

@@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)