Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
in this module. I will be talking about my crow's what? Our day.
00:06
So, uh,
00:07
and how are they used? So you will learn about types of my crows are there how to define variables in Mac Rose? How to use message boxes about if then else, if struck else else if structure but Loops
00:25
did the end, I'm going to
00:27
give you
00:29
example on how to fully look excel sheet or a worksheet or filed and similar uses of micro. So we're going to go into detail with some real life examples on what to do in macros
00:44
in first lesson within this module. Ah, let's talk about types of macros.
00:51
So microbes represent the best way to automate your excel file. And when I say automate, there are two levels of to automation. 1st 1 is
01:00
that
01:00
everyday tasks that are
01:03
they can be put in a group
01:07
you can put in the macro,
01:08
and then, instead of repeating 1234 or five or mawr tasks that are
01:19
just following
01:21
the previous one
01:22
in every
01:23
every time you're using them. Ah, you can
01:27
simply record them,
01:30
um,
01:30
and then call them upon with key stroke or or pressing a button.
01:37
Ah, and of course, room 17. Mac, Rose and function Christian. We're going toe deal with each one of those.
01:48
So let's start with an example.
01:51
And here we have a simple calculation. Now it can be much more complex, but it really doesn't matter for the
01:57
purpose of this demonstration.
02:00
So you have
02:02
call May
02:04
Colby. And in currency
02:07
you have a
02:07
plus B.
02:08
And it's simple. It's not in a ray function. It's nothing, just the simple calculation. So we basically
02:15
did this. We created this cell. The double click. We have these.
02:21
Now we're sending this file to somebody and we don't know.
02:24
I don't want them to get the formulas we just want to get, want them to get the data
02:31
and, uh, or for whatever reason, we just don't need any formulas anymore. We're just on the fixed data here.
02:39
So how would we do it
02:42
may
02:44
do this.
02:46
We copy these cells,
02:47
who pays them here as values.
02:51
Then we
02:58
Sorry. We copy them here.
03:01
We face them here,
03:04
his values.
03:06
And then we delete this.
03:08
There are other ways to do it, but it doesn't matter. So we did it.
03:13
And, uh,
03:15
now we want
03:16
to go back
03:21
and automate this thing.
03:23
In order to do that, we first have to
03:28
enable macros, which are by default, disabled.
03:31
And he if you
03:35
have some basic skills
03:37
in excel, you know that we go to the options,
03:42
we go to the customize ribbon,
03:45
and then we click on developer
03:47
books,
03:49
check it in.
03:52
And now we get the developer,
03:55
Deb.
03:57
And now we go to record my crew
04:00
and is going to give us the name. Option two,
04:04
give the name to it.
04:06
And, uh,
04:09
by default is going to be macro one micro too. But we're going to call it
04:15
Korpi, constants, whatever.
04:19
And, uh,
04:21
it's going to be controlled. Plus,
04:27
for example, shift in
04:30
and
04:32
you can put here description if somebody else is going to look at this file, but
04:36
it really doesn't matter.
04:40
So and you click, Okay?
04:45
Oh, yeah. And the name has to have underscore
04:48
there instead of space.
04:51
And now we do whatever we have to do. So control, see
04:58
based values.
05:00
Then select this,
05:02
then copy here
05:05
and then
05:09
delete this
05:11
and then we go to stop recording.
05:15
And now if you go back,
05:18
you know, have formulas here
05:23
and then I press control shift. And
05:28
it does. You see, the you have seen the short flicker on the screen when it was
05:33
copying and deleting cells, but that's it.
05:36
So
05:38
this is how we do the basic recording of a macro
05:43
and historically looking before there was a visual basic for applications.
05:47
This was the only thing you could do with Mac Rose.
05:51
So, um,
05:54
you have seen
05:56
in this first part of this lesson how to record a simple macro Now, this can be much more complex.
06:03
It can involve all sorts of functions.
06:08
But in the end,
06:10
it is just
06:11
recording what you're doing in excel and putting it in Ah
06:17
ah, one routine that repeats that,
06:21
Um, Now, if we
06:25
look a TTE the Mac rose and you want to edit it,
06:31
we will get
06:34
this.
06:38
Sorry,
06:39
we will get this
06:41
so it will create
06:43
17. That's called copy constants without any parameters.
06:47
And then it will be selected range.
06:51
And these are the
06:55
macro commands
06:57
that they're, uh
06:58
ah,
06:59
doing what you I have been doing with within the Excel file. So it's electing range. See, Want to see five.
07:08
Then selection copy means is being copied.
07:12
Then,
07:13
uh, we're selecting range in the one.
07:17
And then we're selection pay special based.
07:23
And these air the explanations off
07:27
what
07:28
it has done
07:30
with all the parameters.
07:33
Then we're against selecting
07:36
cut copy Mon fils Were selection copy
07:41
range,
07:42
select
07:43
selection pay special meaning it's spacing.
07:46
We told the parameters needed.
07:47
And then we select the range D
07:51
and
07:54
clear content and we're ending the sub routine.
07:58
So this is how it's working. So you have any programming knowledge?
08:01
This is a simple program
08:05
that is doing these things now. We could have done these things much simpler
08:11
if we were writing.
08:13
Ah, this
08:16
micro Selves.
08:18
But we're going to talk about that in
08:22
continuing continued
08:24
ruins off this lesson in next video.

Up Next

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

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor