25 Nooby VBA Habits You Need to Ditch Right Now

[@Excelmacromastery]

Related videos Habit 1: Copy using assignment: kzbin.info/www/bejne/l6u6qqaQa9aNjbMsi=QyX5yFuui-5njLP-&t=621 Habit 2: Code name of the worksheet: kzbin.info/www/bejne/nnTRmX2qaLqseNEsi=VMK9bGbuuNnpmmtX&t=176 Habit 3: Declare variables: kzbin.info/www/bejne/h3ipcoGHadGDg6Msi=flGcwYS9e-we1qEK&t=40 Habit 4: Use Enums: kzbin.info/www/bejne/ganIoqyaq8pgea8si=BX7KBZAr6gjCQuAN Habit 5: Use Arrays instead of ranges: kzbin.info/www/bejne/g4uWl62BoNl4bposi=dkTNXjRXrfgDQq_w Habit 6: Getting the correct worksheet: kzbin.info/www/bejne/nnTRmX2qaLqseNEsi=y9OXhRSzVl9NCwhj&t=131 Habit 8: Using Split instead of Mid, Left and right: kzbin.info/www/bejne/jXy6npKmbKaqj8ksi=AxMVhaNAAF45GAfA Habit 11: Use Read-Only parameter when opening workbooks: kzbin.info/www/bejne/l6u6qqaQa9aNjbMsi=G1VCYpO0lKGydOrt&t=536 Habit 13: Use Debug.Print: kzbin.info/www/bejne/qX-bnHiYfNGri9Usi=SsmNoqMx8wiAYL7s&t=464 Habit 24: Use GetOpenFilename to allow user to select a file: kzbin.info/www/bejne/moKlY3-PeNxjgdE Habit 25: Use a module for click events: kzbin.info/www/bejne/qX-bnHiYfNGri9Usi=aWHZ1lrkIXHFhGf0&t=128

[@Husky_Passion]

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

[@ragnarok7976]

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.

[@Excelmacromastery]

Very nice solution.

[@joukenienhuis6888]

Technically an interface is something different, but in functionality it kind of looks like it. Only this “interface” is directly callable 😃

[@ragnarok7976]

@@joukenienhuis6888 Interface in the UI sense. Something that goes in front of all the nitty-gritty and only exposes a few key controls to the user. I realize there was some ambiguity with the programming term there but I couldn't really think of a better word to get the message across.

[@EricHartwigExcelConsulting]

This is really clever! I was not aware of the "Option Private Module" to make all sub's contained in that module even public subs invisible to the user in the macros window dialog box. Thank you for sharing.

[@stevegarside5026]

I did not know about Option Private Module. Nice technique, thanks for sharing!

[@haragorn88]

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

[@Excelmacromastery]

Glad you enjoyed it

[@mikheilchigogidze4224]

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

[@davidtarvin8858]

I've been using VBA for a shorter duration but I feel I get a lot help from these videos.

[@Excelmacromastery]

Glad you like them.

[@Excelmacromastery]

Great to hear

[@fahadsardar6180]

Salute to you, sir. What awesome why you explained it. Look forward to seeing more of such videos

[@EricHartwigExcelConsulting]

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.

[@Squeeonline]

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

[@Excelmacromastery]

Glad you like it?

[@JavierRuizGarcia]

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

[@kcerny43]

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

[@Excelmacromastery]

You're welcome

[@ganlyb]

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

[@hotflashfoto]

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!

[@Excelmacromastery]

Thanks, glad you liked the video. LNC is considered outdated but if it works for you then use it by all means. The thinking is that if you name a variable customerName it is obvious that this is a string. There is no need to include the type in the name.

[@hotflashfoto]

For a string that's true. With the currency, integer, and long data types, it can get messy, though. The original names were customerAmount and count. By adding the data type as part of the name, all doubt as to what kind either one may be is removed. I use them in my code so I don't have to remember or refer back to the declarations. So long as they're declared, they will still be whatever type they are. The naming convention is there for the coder, not the computer.@@Excelmacromastery

[@theexorcist666]

I would also suggest that if you have to scroll up to see the data type of your local variables, then there is probably scope to move some of that code into its own function or sub i.e. Single Responsibility Principle of Clean Code. I think the recommendation is something like max 20 lines of code in a single function or sub. Also, re shortened names like cusAmt and cusNme, whilst it may seem obvious to you, especially when you use it all the time, it just adds more cognitive load when reading someone else's codebase (or even my own old code when revisiting years later!). It's like reading a page filled with typos. You'll probably still be able to understand it but it hurts the eyes, the brain and slows you down. My personal opinion is that the few seconds longer it takes to type is more than made up in reduced cognitive load when reading and understanding. I prefer longer, meaningful names for improved readability (but obviously within reason 🙂).

[@SimondeLisle-au]

Used to do this but it annoyed me with numbers if they changed type in my code e.g. sinNumber to dblNumber to curNumber. However I saw a friend who just uses one letter prefixes and uses nNumber cNumber, sString, vVariant, bBoolean, dDate and now I prefer that.

[@hotflashfoto]

That's also a good method because it's a standard way of doing things that is logical, meaningful, and descriptive. That's my whole point in a nutshell. Good job!@@SimondeLisle-au

[@josh_excel]

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.

[@Excelmacromastery]

Thanks.

[@edwardkelly1262]

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.

[@djunabug]

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 😊

[@Excelmacromastery]

Good idea

[@martyc5674]

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

[@Excelmacromastery]

It's an easy mistake to make especially if you have used other languages.

[@russballaam1621]

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.

[@BrianFesler]

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

[@Excelmacromastery]

Thanks Brian, OO is such a big topic it really requires a series of video or course.

[@kellybly5189]

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!

[@logic3686]

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

[@donclay3511]

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?

[@Theorist1984]

This video is an absolute treasure

[@Excelmacromastery]

Thanks

[@straight6604]

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.

[@stevegarside5026]

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!

[@Excelmacromastery]

Glad you liked it

[@m-squaredcontractors9720]

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

[@Excelmacromastery]

No. Because the value doesn't change, it doesn't cause problems.

[@m-squaredcontractors9720]

@@Excelmacromastery Noted thank you 😊

[@joukenienhuis6888]

I am not that familiar with constants, but do I understand it correctly that the first named constant in the example is valued as 1 and the second as 2, etc. ?

[@ricos1497]

​@@joukenienhuis6888you're thinking of enums. A constant can be anything, just a fixed value and often public. For example, you might have a public const string of a folder name. Something that you don't want to give the user the functionality to change, but it could be referenced multiple times in your module, so you throw it to the top of the code so that you only have to change it once if you change folder. Obviously that's a rudimentary example and not something you'd do in many cases.

[@Excelmacromastery]

@joukenienhuis6888. As @ricos1497 said, you are thinking of enums. You would use Enums instead of constants for long integer types as they are more flexible than constants.

[@joukenienhuis6888]

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

[@Excelmacromastery]

Glad it was helpful!

[@rrrprogram8667]

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

[@samualgotze9823]

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!

[@ElGordoFreeman]

Those are suggestions! Some of them are better than others.

[@Mesjach]

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.

[@Excelmacromastery]

You said "In large programs, keeping track of the variables becomes a nightmare". One habit I should have included was to keep sub/functions small. They should perform one action and not be more that 15-20 line if possible.

[@ricos1497]

​@@Excelmacromasteryyes! I actually like to break my code down into reusable functions where possible, which I find is much easier for error handling and code reuse. However, I still agree with the OP, and I place the dims at the top (through habit). With smaller subs/functions, it's rarely an issue. I'll maybe try your way sometime.

[@Mesjach]

@@Excelmacromastery Keeping them small and portable is something I have to improve at. It would definitely help this issue as well. In any case, thanks for great content!

[@dj_automaton4300]

Declaring variables at the top is a habit I share as well, but realize it is a bad one. As Paul has said, if you have a long routine, that is bad practice. This is the actual nightmare you speak of. All code grows as we develop it, so grouping the variables as they are used makes it easier to chunk it off to its own routine.

[@VladikHannzzeboff]

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

[@Excelmacromastery]

Thanks 😊

[@deejay127]

Best video yet

[@Excelmacromastery]

Thanks

[@67bm10]

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

[@mixtape8476]

Thank, it helps me a lot

[@Excelmacromastery]

You're welcome

[@joeburkeson8946]

Great review thanks.

[@Excelmacromastery]

Very welcome

[@DarrenSaw]

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

[@josealvesferreira1683]

Super interesting, thanks

[@Excelmacromastery]

Glad you enjoyed it

[@joaocustodio2094]

Another very useful video. Thanks Paul.

[@Excelmacromastery]

You're welcome

[@MrStuartAllan]

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

[@Excelmacromastery]

You're welcome

[@vincentlee7995]

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

[@davidbethke5463]

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.

[@johnwayne8059]

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

[@Excelmacromastery]

You're welcome.

[@oliveroshea5765]

great video - thanks for posting!

[@rautoramarautorama9324]

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

[@Red00022]

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

[@nottydread]

Fantastic stuff

[@Excelmacromastery]

Thanks

[@droidfan]

Wow! This was great! Thank you!

[@Excelmacromastery]

Glad you enjoyed it!

[@nadermounir8228]

Thank u Paul for this nice Video 📹 👍

[@noviceprogrammer2011]

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)

[@Excelmacromastery]

Named parameters are good.

[@GanovAlex]

thanks a lot. very useful 👍

[@Excelmacromastery]

You're welcome

[@sachinbizboy]

Thanks for sharing.

[@Excelmacromastery]

You're welcome

[@workstuff5253]

Password protecting the workbook VBA code to stop meddling.

[@Akens888]

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

[@Excelmacromastery]

It has scope from where the variable is dimmed.

[@joukenienhuis6888]

@@Excelmacromasterythe only problem is that in a large program, the memory allocation is a problem. If you fee memory up front it can run faster. If I remember correctly there is a term for pre and at the point memory allocation 🧐🤔

[@charlesw3120]

Great video.

[@Excelmacromastery]

Thanks

[@ArmPowerWorkouts]

this is goood stuff

[@seamushand8439]

Guilty as charged :) for a few at least !

[@Excelmacromastery]

We've all been there.

[@hammeedabdo.82]

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?

[@Excelmacromastery]

Python is only available within cells to manipulate data. Kind of like advanced formulas. This means it doesn't work with buttons etc. so you couldn't build an equivalent trade simulator with it.

[@hammeedabdo.82]

Even if we use Python libraries that allow us to use Python to build applications on the Excel?@@Excelmacromastery

[@Excelmacromastery]

You could do it with standard Python.

[@hammeedabdo.82]

Do you mean that it is possible to build such a trading simulator using standard Python independently of Excel, or do you mean that it is also possible to build such a simulator on Excel using standard Python?@@Excelmacromastery

[@MC_Kun]

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.

[@Excelmacromastery]

In VBA it makes the code easier to read and it does improve performance. The only downside is, using the objects in the watch window when debugging.

[@yousajtube]

'with' in javascript is superficially like 'With' in VBA but with scary consequences. 'With' in VBA is good practice with no scary consequences, but I try not to nest them because it can be difficult to read in a single screenful of code.

[@henrikijonkoping4694]

Great

[@Excelmacromastery]

Thanks Henrik

[@henrikijonkoping4694]

if i Understand it correctly at 1:53 the text in the middle must be Sheet in other workbook?@@Excelmacromastery

[@ragnarok7976]

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

[@learningfinance6596]

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

[@Excelmacromastery]

Well then you can only get better:-)

[@Husky_Passion]

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

[@tomharrington1453]

I have found that using the sheet name only works if the VBA code is imbedded in the same workbook as the sheet. In other words, if you have a macro file that will be working on data in a separate data file you can’t refer to sheet names in the data file. If I am missing something or doing it wrong please enlighten me.

[@Excelmacromastery]

You mean "code name" only works in current workbook? You can use this code to get a sheet by code name in a different workbook excelmacromastery.com/excel-vba-worksheet/#Code_Name_in_other_Workbooks

[@Excelmacromastery]

What do you mean by "set the sheet to a variable"?

[@ricos1497]

​@@ExcelmacromasteryI think they mean: dim sht as worksheet Set sht = Worksheets("sheet1") It doesn't get round the problem of sheet names changing, thus your way is better in my opinion. If you have changed the sheet name in VBA, then assigning to a variable adds nothing.

[@Excelmacromastery]

@ricos1497 Exactly.

[@Monochromatic_Spider]

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.

[@Excelmacromastery]

Thanks for the feedback.

[@TP-om8of]

What was the 26th?

[@tomharrington1453]

26 for complex procedures, always use a Main procedure that calls a series of sub procedures or functions.

[@TP-om8of]

@@tomharrington1453 27: Dump MS Windows, use Linux; dump Excel, use Python or R

[@kebincui]

👍👍

[@Dexter101x]

I'm guilty of a few things here

[@bill7918]

😎 Promo'SM

[@stevojohn]

VBA needs to be ditched TBH.

[@mathijs9365]

Why? Office Script is not even the same level as VBA. U cant compare VBA with Python. MS stopped developing since 2010.

[@Excelmacromastery]

@mathijs9365 is right. It's fine to say stop using VBA but there isn't a replacement at this time.