25 Nooby VBA Habits You Need to Ditch Right Now 

also always upcase the first letter of variables in declarations, but write them in lowcase on coding/using them ==> if any stays lowcase, it means you miswrote it

Something I like to do is use "Option Private Module" at the top of all my modules except for one of them which I call "Public Module". In the public module I put all the subs that will be attached to buttons or activated via macro short cuts. That way only the functionality that I want exposed to the user is available outside the VBA editor. This helps ensure users don't accidentally run subs that are only intended to be called by other subs. The public module can still access the private modules just like a regular module. It's basically like making an interface that only exposes the functionality you want exposed to the user.

Best to watch 0.75 speed and understand what is what. Because i often find you talk too fast.....

great video - thanks for posting!

Great Video, I learned a lot from you, thank you!

I wish there was a channel like yours but for C# which I have started to learn to try and speed things up even more.

A great informative video which gave me ideas on how to speed up my code and make it a lot easier to understand. Great job, Paul Kelly!

The entire video is really great information thank you Paul! I have never considered using Enums to hold column number references but that is a really good idea.

Thank u Paul for this nice Video 📹 👍

Wow, thats a lot of great stuff packed into 8 mins. Valuable Paul.

Great review thanks.

Another very useful video. Thanks Paul.

Thanks for all of your work, Paul!😉🤟😎

Wow! This was great! Thank you!

This video is so very informative. I have been writing code for many years, and some items mentioned I did not know about. Everyday is a learning day. Thanks for sharing!

Thank, it helps me a lot

I found a bookmark in VBA very useful. But I always have my code in my head. Thanks, Paul!

Great video with lots of useful tips. Thanks for sharing!

I've written a ton of VBA code but I'm sure I still have some of those in my code. I tend to reuse working code over and over again. It probably wouldn't hurt me to make another pass and get rid of some of them. You've helped me immeasurably over the years. thanks. Have you ever considered adding in Libre Office Basic to your courses?

Great Video! Thanks as always. I have been following your videos for more than 5 years and still find new things. Thank you!

thanks a lot. very useful 👍

I've been the VBA expert at my office for around 10 years... and I still learned a few new things.

Amazing, as usual! Now I’m scrolling through show notes looking for links to full videos on hints touched upon here. First up: enums! Including those links here would up your well-deserved view counts, I’m sure 😊

Great video Paul. Good to see a new one. Perhaps one on Object Oriented Analysis and Design?

Super interesting, thanks

Another great video, luckily I'm only guilty of 2 or 3 of these, something to work on though!

Thanks for sharing.

This is helpful and reminds me I'm still a n00b for some things!

Fantastic stuff

Thanks for reminding me to these obvious but easy to miss tips. It is always good to keep being reminded if you do not use it often

Good suggestion, even for experts. Another thing I like is using Shift+Tab to remove indentation in a block of code in case you need to reformat it.

I just found your videos - I LOVE YOU!! 🤣🤣🤣🤣🤣

Hi. Thanks for the brillient channel. Would you consider doing a video that covers the differences of Excel VBA running on an Apple Mac OS to running on Windows? My user base have both Apple Mac and Windows machines and in some case things things that work for Windows do not work on an Apple Mac. For example, I noticed that you did a tutorial recently about sending Outlook e-mail, but think this would only work on a Windows machine. Unfortunately I only currently have access to Windows, so it makes it very difficult to write and test code and I generally have to send it to a known Apple Mac user and get them to test. If you could summarise the main differences that would be great.

A great video! Is there a plain text version? I like to annotate things like this, as I'm already doing some of them and others I'd want to add more details as to why I'd want to use them.

Of course like him as profs in his IT field blast stunning... I expect if he could give us a lesson how make combine between class module and dialog box UserForm interaction to optimize the data treatment... Thanks guys!

Great vid Paul- I thought Dim a,b,c as xyz assigned the same variable type all along 😢

Number 25 button assignment works for Form Control buttons but not for Active-X command buttons because their event handler code must be in the sheet containing the button. There doesn't seem much point in making a separate module just for some buttons but not others.

Best video yet

Your videos are pro. I am guilty of some of these.

Great video.

My COBOL teacher hammered it into me to declare variables at the top. Possibly due to the nature of the declaration section

I still prefer to use the declarations area at the top for all of my variables, which are almost always Private. I can find every one of them, they don't clutter up the sub or functions, and they are all grouped together. I also use a form of LNC to name my variables. So at 5:00 for the "BadVariableNames", the 4 variables would be curCusAmt as Currency, lngCnt as Long, dteCurDay as Date, strCusNme as String. This way, no matter where they're used in my code and no matter who is digging through it, they have a fighting chance of knowing what type of variable it is from the first few characters of the name. I like your suggestions a lot and have been subscribed for some time. I always learn something new and good in each video. This time, I especially like the idea of having a module just for the button click events. That is truly organized!

Thank you for the awesome video. Just one question, are public constants also a problem?

I king of this world.... Declare you as the king of Excel

Great

I guess the range to array is true to be faster. But what if you need to update the data as well? Don’t you lose the range addresse?.

Guilty as charged :) for a few at least !

I'm not an expert by any means but I don't agree with #14. In large programs, keeping track of the variables becomes a nightmare. For example, I need another Long type variable. Did I use j or k already? I have to look for them. When they are all in a block at the top, I can easily see what I have and add to it. When looking up a variable with #14 it's either right above OR near the 1st instance of it being used, so if it's used multiple times across the code, you often have to look for it. With big variable block at the top, any time you see a variable you know where it's declared - at the top. #14 also makes the actual code look longer, cause you have Dim's everywhere, as opposed to having all declarations at the top, and the "meaty" part of the code short and clean. Maybe it's a "me problem", but as a long time viewer of this channel I tried declaring variables as I used them multiple times and just had a worse experience.

I always like to be specific, so every Sub is preceeded with either Public (the default) or Private. I also like named parameters, so will write rng.Copy Destination:=wksData.Cells(1,1)

I'll just leave a comment here for RU-vid to promote this video in gratitude to the author for his work

Thank you for the informative and insightful video...I have a question...As you know, Python has become official within Excel.. Is it possible to build the same previous trading simulator on Excel but using Python, especially since it has become official within Excel? And will the trading simulator have the same performance and quality as the previous trading simulator, that you built?

👍👍

4:19 not sure I agree with this for VBA as it doesn't have block level scope like other languages do.

Password protecting the workbook VBA code to stop meddling.

Very very usefull, because i am a newbie so i have all of those 😂😂😂

the only one i don't agree is using sheet's codenames. I prefer set the sheet to a variable and use the variable. Codenames change too. In my beginnings i messed a lot because of Sheet1 changing like a bird on steroids

What was the 26th?

MFW I realize that "Microsoft" should have called it "Macrohard" intead of "VBA": 😮

Careful for bad habit: Using WITH statement In other languages, like Javascript for example, it is highly discourage using With, because it makes the code harder to read, very hard to maintain, and it actually doesn't improve performance. The only benefit is making the code slightly faster to write the first time. After that its a hell to maintain. Please don't encourage this practice.

😎 Promo'SM

I'm guilty of a few things here

First thought, "noob" has negative connotations. Newbie does not. Probably shouldn't call people noobs just because they're not that good at programming just yet. Small thing, but you know, it's always nice to be nice. Having said that, did you notice how a bunch of your early habits have solutions that then get flagged by your later habits? Storing your data in a variable "data" of type variant is not very specific. It would not be unreasonable to declare it "dim data() as variant" to fully specify that "data" is variable containing a variant type array. Also, declaring variables as you go... I'm not sure that I'm a fan, though of course that's probably down to me having to work with horrendously ugly code that almost exclusively uses the blob design pattern. If you have 1500 lines of code and all the declarations are happening across the entirety of that space then it becomes a real struggle to remember what things are. Having all the declarations in one place, with descriptions as necessary, makes that a little less aggravating. Come to think of it, the blob design pattern probably should have been part of this list. True story, there's actually an upper limit to how much code you can stuff into a single sub, and if you ever get there, you might finally get your boss to sign off on taking the time to restructure a bit. Additionally, while I'm aware that it is considered outdated garbage in modern IDEs, I actually really like Hungerian notation. I like knowing whether I made something an int or a long or a double or a currency or something else just by glancing at the variable name, particularly when I revisit something I made five years ago in a time crunch. I suppose that could be another habit born out of working with legacy code, but it is nice all the same. Last thing, maybe disabling workbook calculation should have been part of this list too? Some people out there really, really like their ridiculously long formula chains that take forever to calculate. Bad things happen when one of the formulas is volatile and everything recalculates for every cell change you make. For similar reasons, if you know that events are not needed then do kill them too. Just remember to set them to their previous state when done. Not doing this is probably only newbie habit 26, though.

VBA needs to be ditched TBH.