Can This Code Be Saved? | VBA Code Audit

Рет қаралды 10,816

7 ай бұрын

Can This Code Be Saved? | VBA Code Audit
In this video I review the code submitted by my customers. Watch this video if you want to improve your own code.
This video is packed with tips and techniques for writing clean and efficient code.
#ExcelVBA #VBACodeReview #VBACodeAudit
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
Learn how to build Excel VBA Applications the right way using best practices and proven techniques:
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L 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)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
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
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
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Пікірлер: 86

@chieffireseal7212 7 ай бұрын

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 7 ай бұрын

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

@karolispulokas1364 2 ай бұрын

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

@jimfisher349 5 ай бұрын

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

@philhamilton6926 6 ай бұрын

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 6 ай бұрын

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 6 ай бұрын

Thanks David.

@idogus 6 ай бұрын

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

@x4ms 7 ай бұрын

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

@Excelmacromastery 7 ай бұрын

You're welcome

@vikits21 7 ай бұрын

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 7 ай бұрын

Thanks, will do!

@BuildFriendly 7 ай бұрын

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

@Excelmacromastery 7 ай бұрын

Thanks

@R72Investments 7 ай бұрын

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

@Excelmacromastery 6 ай бұрын

Thanks Marcel

@nancykay3109 7 ай бұрын

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 7 ай бұрын

It's on my list

@excelautomationtool 6 ай бұрын

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 6 ай бұрын

Glad you liked it.

@reng7777 7 ай бұрын

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

@Excelmacromastery 7 ай бұрын

Wow, thank you!

@michaelharpole9416 6 ай бұрын

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 6 ай бұрын

Thanks Michael. More on the way.

@deonvanniekerk7700 7 ай бұрын

Great format Paul!

@Excelmacromastery 7 ай бұрын

Thanks

@vbaclasses3553 7 ай бұрын

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

@Excelmacromastery 7 ай бұрын

Glad you enjoyed it

@annasaheb9678 7 ай бұрын

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

@Excelmacromastery 7 ай бұрын

More to come!

@Pedritox0953 7 ай бұрын

Great video! Its good to know best software practices

@Excelmacromastery 7 ай бұрын

Glad you think so!

@Robbo-tron 7 ай бұрын

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

@Excelmacromastery 7 ай бұрын

Thanks

@kenrichardson2781 7 ай бұрын

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 7 ай бұрын

You're welcome

@SaberTooth2251 6 ай бұрын

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

@s1ngularityxd64 7 ай бұрын

awesome, looking forward for more

@Excelmacromastery 7 ай бұрын

Thanks. Gladc you liked it.

@astollie 7 ай бұрын

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.

@graytonw5238 7 ай бұрын

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 7 ай бұрын

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

@elwont 7 ай бұрын

you're a true hero

@Excelmacromastery 7 ай бұрын

Thanks

@edoggamus 7 ай бұрын

Great stuff Paul

@Excelmacromastery 7 ай бұрын

Thanks

@rond9222 7 ай бұрын

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.

@mtstans 6 ай бұрын

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 3 ай бұрын

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

@nicor1501 7 ай бұрын

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

@Excelmacromastery 6 ай бұрын

Thanks for the feedback

@MUHAMMADNASIR-tv2jx 6 ай бұрын

Excellent

@Excelmacromastery 6 ай бұрын

Thanks

@robbe58 7 ай бұрын

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 7 ай бұрын

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

@quenchedthirst1 7 ай бұрын

Great video

@Excelmacromastery 7 ай бұрын

Glad you like it

@AlexFariaOliveira 7 ай бұрын

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 7 ай бұрын

That's great that your code is readable.

@pawemajewski2430 6 ай бұрын

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.

@b99eu 7 ай бұрын

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

@Excelmacromastery 7 ай бұрын

Glad you like the video. Thanks for the suggestion.

@podden22 7 ай бұрын

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 7 ай бұрын

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

@podden22 7 ай бұрын

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

@Bhavik_Khatri 7 ай бұрын

Great video. How about adding line numbers?

@Excelmacromastery 7 ай бұрын

For what purpose?

@Bhavik_Khatri 7 ай бұрын

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

@hammeedabdo.82 7 ай бұрын

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 7 ай бұрын

Of course.

@hammeedabdo.82 7 ай бұрын

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

@hammeedabdo.82 7 ай бұрын

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

@user-iv6rf2gd5h 6 ай бұрын

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 6 ай бұрын

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.

@user-iv6rf2gd5h 6 ай бұрын

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?

@baphnie 7 ай бұрын

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 7 ай бұрын

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

@baphnie 7 ай бұрын

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

@MC_Kun 7 ай бұрын

I applaud your patience, the original code was unreadable

@Excelmacromastery 7 ай бұрын

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

@clandeszipp4564 2 ай бұрын

Italians! 😂

@jameswilliams5045 7 ай бұрын

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

@Excelmacromastery 7 ай бұрын

Why?

@AlexZabelkin 6 ай бұрын

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