馃攳
Excel VBA basics: Data types, DIM & SET (Learn to use them correctly) - YouTube
Channel: Leila Gharani
[3]
- This is important because
it helps your code run faster.
[7]
You see, the advantage of VBA
[8]
is that it can keep a
lot of things in memory
[11]
and it works through that memory
[14]
to give you the outcome that you want.
[16]
And we work with VBA's
memory when we use variables.
[21]
By default, VBA assigns the
data type called Variant.
[25]
And Variant means that it changes
[27]
depending on the data type.
[29]
So if that data type is
text, number, date and so on.
[33]
It's going to allocate
around 16 bytes of memory
[37]
if it's a number and a
little bit more if it's text,
[40]
so if it's string.
[42]
Now if you know that the
variable you're going to be using
[44]
is a small number,
[46]
because let's say you're just capturing
[48]
page numbers, for example,
[50]
then you can declare
that variable as Byte.
[54]
Guess how much memory space a Byte takes?
[57]
One byte.
[58]
So that's one versus 16.
[61]
And that's a big difference
which become obvious
[64]
on the bigger macros, the
more complex, longer macros.
[69]
The other important thing to know
[70]
is that each data type has a range.
[73]
So let me show you what I mean by that.
[75]
First, let's take a look
at the common data types.
[78]
I've ordered them by popularity
based on my own code.
[84]
You can see that the smaller
the memory space used,
[88]
the smaller the range here.
[90]
So for example, a Byte has
a range from zero to 255.
[96]
So now let's imagine that
that report you were making
[98]
has actually 300 pages and you want to
[101]
keep that number inside the variable
[104]
that you've already declared as Byte.
[107]
You're going to run into a problem
[109]
and that problem is
going to be a VBA error
[112]
because it can't handle that data type.
[114]
It can't put in that
300 or it can't put in
[117]
any number above 255 in this variable.
[121]
So instead of using Byte in this case,
[124]
you could use Integer, which
also takes up a lot less space.
[128]
Just two bytes compared to
the 16 bytes for Variant.
[133]
If you are looping through a lot of rows,
[136]
you're on the safe side to use Long
[139]
because that has a really big data range,
[142]
which can handle the
number of rows in Excel.
[146]
So be careful, don't
declare them as Integer
[149]
if you think that you could have data
[152]
that goes past this number of rows.
[155]
Boolean uses two bytes of memory.
[158]
Double is good for declaring percentages
[162]
or if you're doing calculations
that require high precision.
[166]
String is for text.
[169]
Object is for things
like declaring ranges.
[172]
So if you want to
declare a cell as a range
[176]
or a sheet as a worksheet
[178]
or if you're declaring
workbooks in variables,
[180]
you'll need an Object data type.
[183]
Now Variant can come in handy as well
[185]
because you could run into a case
[187]
that you have a variable
that could be Boolean
[190]
but it could also keep nothing inside.
[194]
So if it's really changing
between different data types,
[198]
it makes sense to declare it as Variant.
[202]
Declaring variables basically means
[204]
that you give VBA a
heads up that it needs to
[207]
free up some memory
space for your variables,
[209]
which will, in most cases,
be less than the default.
[215]
We use the DIM keyboard
before the variable.
[220]
The names that you give to
variables is really up to you.
[224]
But special naming rules apply.
[226]
So for example, you can't have spaces,
[228]
so use underscore instead.
[230]
You can't start with a number.
[232]
You can't use a dot or exclamation mark
[235]
or other obvious symbols like the @ sign
[238]
or the dollar or and and so on.
[241]
Also, try to come up with a nickname
[243]
that's kind of short and
as obvious as you can get.
[247]
So in this case, I'm
declaring the my text variable
[250]
as a String data type,
so basically as text.
[253]
What some programmers like to do
[255]
is to follow a naming convention.
[257]
A common naming convention is to use
[260]
the initials of the data
type in your variable.
[263]
So for example, if something
is an Integer number,
[267]
they would put int before the variable
[270]
or just i before the variable.
[272]
And in this case, if my text is String,
[274]
I would put strmyText.
[277]
I personally don't follow
any special rules except
[280]
some rules that I've created
for myself along the way.
[284]
So just for sheets, I would
use sh before the name.
[288]
For user forms, I also
put an initial before
[291]
that indicates to me what type of control
[294]
I'm referring to in that user form.
[296]
Other than that, I've left my options open
[298]
for picking a name.
[299]
So it's really up to you.
[301]
Pick the method that you
can relate with most.
[303]
Let's say I'm defining last row as Long
[309]
because it could be a big number.
[312]
I have a lot of rows in Excel,
[314]
so I'm going to define it as Long.
[316]
If I didn't put anything here,
[319]
it's automatically defined as Variant
[322]
which takes up more space
than defining it as Long.
[326]
Now I want to assign the number
of rows to this variable.
[331]
Officially, you do this
with a let statement.
[335]
You would say, "let last row equal"
[339]
and I'm just going to do rows dot count,
[341]
so that's the number of
rows in my Excel worksheet.
[345]
So this let statement is something that
[347]
you might not have seen in other projects.
[350]
That's because it's optional
and people don't use it.
[354]
But in reality, what you're doing
[356]
is you're letting this
last row equals that.
[358]
But you don't need it
so you're not going to
[360]
see it in my code either.
[362]
I leave it out.
[363]
But just so that you
know what it is in case
[366]
you see it in other codes
that you have to work with.
[371]
Variables don't just hold
data and string types.
[374]
They can also hold objects.
[377]
Common objects are, for example,
[379]
the Workbook object,
Worksheet, or the range object.
[383]
The way you declare them is like this.
[386]
To assign variables to objects,
[389]
you need something and you
need the set statement.
[392]
So new sheet equals active
sheet would give you a problem.
[396]
You have to say, "set new
sheet equals active sheet".
[400]
This is something that I
forgot a lot at the beginning.
[403]
I still forget sometimes
when I'm writing the code.
[405]
But the way that I remember it is
[407]
the moment you notice that
you've declared something
[410]
that's not a String, that's
not Long, that's not Integer,
[413]
it looks like it's an object
because it's a Workbook,
[415]
Worksheet or range, you need set.
[417]
If not, you get an error.
[419]
I'm going to show you the
type of error you get.
[420]
Whenever you see that, just remember,
[423]
you've probably forgotten the set keyword.
[426]
This tutorial is a part
of my Excel VBA course.
[429]
If you're interested to find out more,
[432]
check out the descriptions
below the video.
[435]
If you like this video, don't
forget to give it a thumbs up
[439]
and for more videos like this one,
[442]
why not subscribe to this channel
[443]
so that you can get
updates when they come out.
Most Recent Videos:
You can go back to the homepage right here: Homepage