- This is important because
it helps your code run faster. You see, the advantage of VBA is that it can keep a
lot of things in memory and it works through that memory to give you the outcome that you want. And we work with VBA's
memory when we use variables. By default, VBA assigns the
data type called Variant. And Variant means that it changes depending on the data type. So if that data type is
text, number, date and so on. It's going to allocate
around 16 bytes of memory if it's a number and a
little bit more if it's text, so if it's string. Now if you know that the
variable you're going to be using is a small number, because let's say you're just capturing page numbers, for example, then you can declare
that variable as Byte. Guess how much memory space a Byte takes? One byte. So that's one versus 16. And that's a big difference
which become obvious on the bigger macros, the
more complex, longer macros. The other important thing to know is that each data type has a range. So let me show you what I mean by that. First, let's take a look
at the common data types. I've ordered them by popularity
based on my own code. You can see that the smaller
the memory space used, the smaller the range here. So for example, a Byte has
a range from zero to 255. So now let's imagine that
that report you were making has actually 300 pages and you want to keep that number inside the variable that you've already declared as Byte. You're going to run into a problem and that problem is
going to be a VBA error because it can't handle that data type. It can't put in that
300 or it can't put in any number above 255 in this variable. So instead of using Byte in this case, you could use Integer, which
also takes up a lot less space. Just two bytes compared to
the 16 bytes for Variant. If you are looping through a lot of rows, you're on the safe side to use Long because that has a really big data range, which can handle the
number of rows in Excel. So be careful, don't
declare them as Integer if you think that you could have data that goes past this number of rows. Boolean uses two bytes of memory. Double is good for declaring percentages or if you're doing calculations
that require high precision. String is for text. Object is for things
like declaring ranges. So if you want to
declare a cell as a range or a sheet as a worksheet or if you're declaring
workbooks in variables, you'll need an Object data type. Now Variant can come in handy as well because you could run into a case that you have a variable
that could be Boolean but it could also keep nothing inside. So if it's really changing
between different data types, it makes sense to declare it as Variant. Declaring variables basically means that you give VBA a
heads up that it needs to free up some memory
space for your variables, which will, in most cases,
be less than the default. We use the DIM keyboard
before the variable. The names that you give to
variables is really up to you. But special naming rules apply. So for example, you can't have spaces, so use underscore instead. You can't start with a number. You can't use a dot or exclamation mark or other obvious symbols like the @ sign or the dollar or and and so on. Also, try to come up with a nickname that's kind of short and
as obvious as you can get. So in this case, I'm
declaring the my text variable as a String data type,
so basically as text. What some programmers like to do is to follow a naming convention. A common naming convention is to use the initials of the data
type in your variable. So for example, if something
is an Integer number, they would put int before the variable or just i before the variable. And in this case, if my text is String, I would put strmyText. I personally don't follow
any special rules except some rules that I've created
for myself along the way. So just for sheets, I would
use sh before the name. For user forms, I also
put an initial before that indicates to me what type of control I'm referring to in that user form. Other than that, I've left my options open for picking a name. So it's really up to you. Pick the method that you
can relate with most. Let's say I'm defining last row as Long because it could be a big number. I have a lot of rows in Excel, so I'm going to define it as Long. If I didn't put anything here, it's automatically defined as Variant which takes up more space
than defining it as Long. Now I want to assign the number
of rows to this variable. Officially, you do this
with a let statement. You would say, "let last row equal" and I'm just going to do rows dot count, so that's the number of
rows in my Excel worksheet. So this let statement is something that you might not have seen in other projects. That's because it's optional
and people don't use it. But in reality, what you're doing is you're letting this
last row equals that. But you don't need it
so you're not going to see it in my code either. I leave it out. But just so that you
know what it is in case you see it in other codes
that you have to work with. Variables don't just hold
data and string types. They can also hold objects. Common objects are, for example, the Workbook object,
Worksheet, or the range object. The way you declare them is like this. To assign variables to objects, you need something and you
need the set statement. So new sheet equals active
sheet would give you a problem. You have to say, "set new
sheet equals active sheet". This is something that I
forgot a lot at the beginning. I still forget sometimes
when I'm writing the code. But the way that I remember it is the moment you notice that
you've declared something that's not a String, that's
not Long, that's not Integer, it looks like it's an object
because it's a Workbook, Worksheet or range, you need set. If not, you get an error. I'm going to show you the
type of error you get. Whenever you see that, just remember, you've probably forgotten the set keyword. This tutorial is a part
of my Excel VBA course. If you're interested to find out more, check out the descriptions
below the video. If you like this video, don't
forget to give it a thumbs up and for more videos like this one, why not subscribe to this channel so that you can get
updates when they come out.
