### Intermediate Excel

Course
Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

### Video Transcription

00:00
in this. Listen, we're going to talk about what kind of variables you can have in macros and essentially because
00:07
make Rosa part of Excel.
00:10
They have the same data type types of, like those exhibit that exist in Excel, plus more So we have numbers. We have text. We have logical or 1,000,000,000 as it's called in the visual basic for applications. We have a date, and then we have a raise.
00:28
And there is can have one dimension two dimensions, like in Excel but even the Maur,
00:35
so there could be,
00:38
in theory, up to 60 dimensions for a raise.
00:42
But in real life, you can hardly find more than three dimensions necessary for any kind of task. Sometimes when you have some calculations with some
00:55
ranges and, uh, you have different parameters that can influence, then you can have maybe 34 maybe five dimensions of a raise
01:06
that you have to understand one thing
01:07
if you have just the smaller raid that is like Fifth has five dimensions.
01:15
It is huge because it's it's if it's only 10 by 10 in 10 in each dimension, it is actually tend to the power of five. When you look at how many cells can be that kind of array. So it's big. It takes a lot of memory, can slow down calculations,
01:34
and you you should use it only absolutely necessary.
01:42
01:46
um, America variables in Micro's, they're slightly different than don't those who haven't excel because you can define them. Yeah, to be of a different type. So first of all, we have bite. And by, uh,
02:00
is exactly that
02:04
takes up one bite of information,
02:07
and it's ranges from 0 to 255 now.
02:12
The practical usage of bite is, ah,
02:15
very small, except if you're using it is a counter in Lopes,
02:23
and the problem is that you have to be sure that you will limit yourself with 225
02:30
value.
02:31
And it was mostly used in the past when memory was scarce and that this space was scarce because it consumes twice is a little off disk space and memory, as in regular integer,
02:47
which is the next type of data, and the regular integer can have values between miners 32,768 2 plus 32,767. And, um,
03:04
that's it that's doing to Joe and and pretty much good enough for any kind of calculation which is usual. If you need it bigger. You have long one
03:15
which goes
03:16
between minus two point something 1,000,000 2 up, two plus two point something 1,000,000.
03:23
And then you can have Ah,
03:24
decimal numbers on the 1st 1 is single,
03:30
and it can be
03:31
Ah, very big. You see the numbers on the screen? I I won't
03:38
read them. And you can have double,
03:40
which can go from,
03:44
um, you know, up to 10 to the power 308 which is pretty much
03:51
enough for any kind of normal calculation. Then you can have hard currency, and currency can have four decimal places,
04:00
and it can go up to 900 trillion
04:04
better minus or a plus. Ah,
04:10
and you can have a decimal number, which can be
04:15
with the up to 29 digits. So if it's ah, just the one number and then 28 decimal places or 29
04:27
um,
04:28
numbers. If no decimals air used
04:31
went to one digit. Sorry.
04:33
So this is for numeric data and for non numeric data you can have string, which has fixed link, and it goes up to 65,400 characters, which is
04:46
usually quite more than enough.
04:48
Then you can have variable ink string that can go up to two billion characters,
04:54
which is
04:56
unimaginable. Why would you need that much? But maybe sometimes
05:01
you can have 1,000,000,000 or a logical which can be just rule falls. You can have date and date can go from January 1st of the year. 100 to December
05:15
31st off year 9999
05:18
so BC Rilya's of date. They cannot be used in Excel.
05:25
You can have object, which is any embedded object, and you have can have very in data type,
05:31
which, uh, can be an American. Then it has any value as largest double or you can have text, which can
05:42
be as very bowling strength, which means up to two billion characters. So
05:47
05:48
the data types.
05:51
And let's just have a small example
05:56
off how these data types are
06:00
limited. So
06:02
here we have mackerels types of data and I will first run it
06:09
and you get runtime error
06:12
and let's go to debug screen just for you to see
06:17
what ah
06:19
we're talking about. So this is the code off
06:24
this macro,
06:26
and we have defined eye as Bite J is integer and K is double.
06:33
And what happened? We have given I really of 300
06:41
which is about 255 so it won't work. The same is J saying it was K.
06:47
So this is just the example that these things actually do have limitations. So if you try to assign
06:57
toe bite something that's lodging in tow have 55 it just won't work.
07:02
And, uh,
07:04
that's ahh pretty much all
07:08
regarding this lesson. So there won't be,
07:11
um, and the
07:13
examples regarding this particular being it Justin reminder
07:17
to know how to define your variables in in a
07:26
my crew's and also to see the way
07:30
the
07:31
wearables are defined so
07:33
to command dim.
07:35
Then you put the name of the variable. You have to be aware that the name of the variable cannot be the same as any function that exists
07:46
in excel, including those functions that you have
07:50
defined by yourself by writing back rose
07:55
And then you put as and then you define to date the type
08:01
and this is how you define data types in Excel

### Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

### Instructed By

Milan Cetic
IT Security Consultant
Instructor