Hi, this is Randy with Excel for Freelancers, and in today's VBA basic training, we're going to go over VBA best practices so you can optimize and streamline your coding in order to create best applications you can in as little time with as little effort as possible and without those headaches.

It's going to be a great training, so let's get started.

Just a note before, I do create these VBA basic trainings each and every Saturday, along with Tuesday complete application development, so make sure you do get subscribed and also turn on the notification icon.

That'll ensure that you do get these trainings each and every week.

I create additional training on our Patreon platform.

Make sure you join us on Patreon, and I also have 300 of my best Excel templates that you can download with a link down below, so that'll also help you create applications.

All right, so today I want to go over VBA best practices.

We're going to go over what's the best way to write code, how to comment in, what are the correct names to use, how to develop programming modularly so you can easily view, edit, and update your code, and also what techniques on how to optimize your code.

So let's get into it right away.

First thing we want to do is make sure that we get inside the actual VBA editor, and to do that, we're going to go into the developer and the visual basic here, and then what we're going to do, if we don't have the developer, of course, you can just simply right-click here, and then we're going to customize the ribbon, and then just make sure developer selected.

You can also use a shortcut key, Alt F11.

Once you're in the VBA, you will see something like this, and this is the VBA editor, and inside this VBA editor, we have our sheets.

We only have one sheet right now, and we don't have any modules.

So when we're writing our code, most of the time we're going to be writing our code inside a module.

So the first thing we want to do is to make sure that we do add a module, and so we're going to right-click anywhere, and we're going to insert module.

You can also, from here, insert the module, and the first thing you want to do, as far as best practices are concerned, is naming that module.

When I first started developing, I didn't know how to name these modules.

I would have modules one, two, three, so if we insert another one here, and so now we have module two, and I didn't know what was inside each.

I would have to click on each just to know, so the first thing you want to do, especially if you're going to be developing a larger application with a lot of modules, is to name your module, and to do that, you can go into the properties here, or click F4, and it's going to launch this properties.

Now this window may be snapped here, it may be down here, it may be somewhere, depending upon your screen, but what you want to do is you want to give it a name.

Whatever the application you're developing is, let's say a customer application, we might want to do customer macros, right?

So we have different macros, maybe we have a scheduling macro, and then so the first thing you want to do is name your modules, and that's going to make things a lot easier, so let's just do something like scheduling macros.

Now keep in mind that when we do naming, we can't have any spaces, so if we try to add a module name with space, it won't allow us, so we want to make sure we're going to use underscores for spaces, so something like this, and you can abbreviate as long as it's clear to you when you go in exactly what the names are and what the consistencies.

Now while we're on naming, let's say we're in the customer macros, we're inside this module here, we want to also name our subroutines clear and easily, we want to use consistency throughout.

If you're developing a large application, those names are going to come in handy.

When we need to then assign those names to different buttons and associates, we want to be able to quickly and easily remember them.

Also when you're naming, we want to make sure that we use a combination of capital letters and smaller letters, so let's say I'll do customer, something like add new, and then maybe we'll do customer, and then we'll do save or update.

Now one of the things that I like, let's do the sub first, now when I want to add this new customer somewhere else, and if I'm in another module here, and I write sub schedule, and then add new, so maybe I want to add that new customer, and a good way if I want to remember it, I don't want to necessarily look over, using the combination of lowercase and uppercase is going to be really helpful, because it helps us let us know when we have the right one.

So if I type in add new, you see how it automatically goes to the capital letters, the proper name here.

However, if I get anything wrong inside the name, something like this, and I go over, look how it doesn't change, that's going to tell me right there, I've got something wrong with the name, so it kind of helps us, we don't always have to look back, so that tells me right there, because it didn't go to capital letters, I know that I might have an issue with the macro, so that kind of avoids errors, and makes things really clear.

So when we actually create our macro names, we want to use a combination of upper and lowercase, and that way when we write them out, and we're on another module, we don't quite remember, we type in all lowercase, and we make sure that it goes to the proper.

Now along the lines of naming, we've got naming the modules, we've got naming the macros, we also want to name our variables.

I see a lot when people program something like this, let's say we're going to do dimension

I as long, X as long, and then we'll do dimension C as string.

For me, that doesn't really help me, I don't remember, maybe it's my memory, but when I get back into this application, I'm not going to remember what I is, or X is.

And then we'll do something like for I equals 1 to 10, next I.

So here we see a loop, but I don't understand what I is, what is it?

Is it a row?

Is it a number?

Is it a customer?

I don't understand.

So we want to make sure that when we're naming our variables, it's very, very clear to us and everybody else.

Even if you're not teaching like I am, you want to make sure that when you come back to the application, we understand exactly the purpose of every variable.

And that's why naming variables is so critical.

I would do something like this, customer row, again, using the combination of capital and lowercase letters is going to really help us.

Customer row as long, and we'll do customer column as long.

And then we can do something like customer name as string.

So it's very clear the purpose of our variables simply by looking at the variables.

And I think that's a really critical component because when we're looking at our code or somebody else is looking at our code, or even if we're looking at our own code, a few years later, we want it to be very, very easy.

So once again, we can also use the variable.

If I say for customer row equals 1 to 10, next customer row, again, see, I'm typing in small letters.

If it then goes to the upper, I know that I've got the right variable.

So it's a great way.

So if I get it wrong, it's going to tell me right away that it hasn't changed the uppercase here.

So I know that there's something wrong with the variable.

So not only using correct names, but using a combination of upper and lowercase to help us quickly understand that means as we're typing the code, we know that we've got an incorrect variable and that happens all the time.

So we want to use not only naming the correct names and clear names, but using a combination of upper and lowercase or using some type of a proper name so that we can quickly see when we type incorrect variables.

And that happens to me a lot, even during while I'm recording, I'll type an incorrect variable.

As soon as I moved to the lower line, I see that didn't move to uppercase.

So I know right away something is wrong with my variable.

So that's a great way to understand clearly how to properly name variables.

We want to make sure that we're actually naming that properly.

Now, whatever way you use, it's more of a personal effort.

I've seen people use things like a lowercase long as variable.

So however you want to remember your variables is fine, but as long as it's very clear to you, and that when you return back to your code, you quickly, very quickly understand the purpose of those variables and how they fit within your macro and how those macros fit within your application.

So notice here, I've got customer row and customer column as long variables and customer name.

So everything is within a customer add new or customer saver update macro, which is within a module called customer macros.

So everything is modular and everything fits together properly.

It makes reading your code and understanding your code a lot easier.

So naming is a critical component.

We also use named ranges inside our code, and that can be helpful too.

Another thing we want to make sure of, and let's just say, for example, we have application where we've got, let's say a customer list here and we go into the formulas name manager.

And let's say I have a, let's go ahead and cancel this.

I'm just going to create a new sheet real quickly.

And let's say we have a list of customer names here and I'll just put in customer names.

Fred will make his appearance and Lisa, and let's see Mary.

So I've got a list of customer names.

Now what we want to make sure of also is our named ranges that we possibly will use inside the code should also be named appropriately.

So for example, if I create a brand new one, so let's say customer, and then we'll do names.

Once again, we may have different variables under customer, let's say customer names or customer balances or customer address, different name range.

So we want to use that same modular type of programming, and then we can write a named range.

So it's something like offset.

And then we'll start at the header row.

We'll move one row down.

So that's just in case we don't have any data, no columns over, we're going to use count a.

And then what we're going to do is again, using the header row and a large row, we're using the header row in case there's no data.

We want to make sure.

I need to subtract one for the header row, a single column, and we're going to use the tab key out.

And we're going to use the shift tab key.

We're going to make sure that it's dancing outs around those.

So now I've got a name called customer names.

If I edit that, I can copy it using control C. Now, if I want to refer to that inside the code for any reason, I can do something like this.

We can dimension customer range, let's do name range as a range.

And then we can also set the customer name range equal to, and then we can use brackets for that named range and we can paste it in there, which is customer names.

So here we've got a named range.

Now what we do want to make sure of is that we don't match the name.

In other words, our named range called customer names should not match exactly any variable referring to that.

We want to keep them very, very different and very separate.

So here, what we've done is we've again created a dynamic named range and we're referring it to within the code.

So that can be extremely helpful when we have clear names for one, our variables, clear names for our macros, clear names for our named ranges and clear names for our modules.

So naming is a critical component of any VBA application.

Next up, when we're writing code, we want to actually write it in a way that's readable for us and other people that might be looking at our code.

And we do that with proper spacing and indentation.

So the best way to do that is when we're using for next loops or we're using any kind of a loop or if then statements, we want to make sure to indent properly.

Notice that this is going to make it clear that anything we write in here is inside that for next loop.

So no matter what, let's just do customer row.

We can do something like sheet one dot range a and customer row dot value equals customer row.

So in this code, we see that everything we write was within that.

Now, if we do an if then statement, if customer row is less than five, then so notice now, once we're in the if then statement, we also want to indent everything within there.

So that is clearly stated.

Let me go ahead and show you a little bit larger project that I did so we can see how that would be on a larger project.

So I'm going to open up one of my recent projects.

Let's just do this one, this ERP software here.

So this is a pretty good one.

And we can see some of the way that we do the code inside these larger applications.

Once this code opens up, now we see here inside this large code, we have lots of different sheets and those sheets also must be named too.

And inside these individual codes, let's go ahead and pull in one of those codes here.

We can take a look.

So we've also got a list of modules and we notice that each individual module, customer macros, employee macros, logging out, everything is clearly labeled inside the customer macros.

We can see that inside a macro, we also have proper indentation.

Here we have inside the width, we've moved it over.

We have inside our customer width, we have a width statement.

Inside there, we have a for next.

So everything has been properly indentated.

We also have, again, clear macro names.

And another thing that I wanted to share with you is the ability to have sheet code names.

We'll take a look inside here and we see not only do we have the sheet names such as admin chart data customer, we also have a sheet code name.

And that's a very, very helpful to when we're referring.

Let's go back inside our original sample, which is right here.

Now maybe we want sheet one as a customer sheet.

We also want to make sure that inside that application, we clearly label the sheet name so we can do it from here.

Customer if it's customers or whatever it is, customers, or maybe we'll just do customer form, make it a little clearer.

So the more clear now sheet names, we can use characters such as spacing and so on and so forth.

So that will help us identify the specific sheet.

But when we're inside the coding, we really want to also change the sheet code name.

So let's go ahead and take a look.

We're going to go into the properties here.

And we have both the sheet code name, which is currently sheet one.