Excel VBA Beginner Tutorial Learnit Training English English (auto-generated)

Excel VBA Beginner TutorialYouTube↗ IA↗(long shot)
Education 4.5M views 57.7K likes 22 Nov 2019

Learn Skills Daily 1.1M subscribers United States YouTube↗

(bright music)

- [Instructor] Well, hello there

well hello there and welcome to VBA

and welcome to VBA coding for Excel.

coding for Excel

My name is Chelsea Dohemann

my name is Chelsea diamond and we are

and we are going to be learning

going to be learning a whole heck of a

a whole heck of a lot about Visual Basic for Applications.

lot about Visual Basic for applications

Now, of course, Visual Basic for Applications,

now of course Visual Basic for applications which is

which is what VBA stands for,

what VBA stands for does have the word

does have the word applications in it.

applications in it that sort of implies

That sort of implies more than one application.

more than one application so it does

So it does actually work in the other programs

actually work in the other programs in

in the Microsoft Office suite.

the Microsoft Office suite however in

However, in this particular class,

this particular class we are really

we are really going to be focusing on Excel,

going to be focusing on Excel which is

which is, in my opinion,

in my opinion one of the places in which

one of the places in which it has the greatest utility

it has the greatest utility for the

for the largest number of people.

largest number of people in this

In this beginner class,

beginner class we're going to start off

we're going to start off really nice and simple.

really nice and simple so if you've

So if you've never heard of macros,

never heard of macros or maybe you've

or maybe you've seen one that somebody else made,

seen one that somebody else made you are

you are in the right place.

in the right place if you've already

If you've already created your own macros,

created your own macros don't worry

don't worry, stay tuned

stay tuned and pretty soon we'll be in

and pretty soon we'll be in the middle of amazing stuff for you.

the middle of amazing stuff for you in

In the beginning videos,

the beginning videos we're going to

we're going to start by discussing

start by discussing the basics of VBA

the basics of VBA and its language,

and its language displaying the

displaying the Developer tab of the Excel ribbon,

Developer tab of the Excel ribbon

recording some basic macros, learning some VBA terminology,

recording some basic macros learning some VBA terminology figuring out how to

figuring out how to use the VBA interface,

use the VBA interface which I do have up

which I do have up over here,

over here we will talk about how to

we will talk about how to bring that up together,

bring that up together writing macros

writing macros, debugging macros

debugging macros and adding them as

and adding them as buttons in the ribbon

buttons in the ribbon or in the

or in the interface of Excel.

interface of Excel so I could add it as

So I could add it as an extra button over here,

an extra button over here or I could add

or I could add it directly into the interface as well.

it directly into the interface as well

For this little bit,

for this a little bit you get to just

you get to just sit back and listen and watch.

sit back and listen and watch don't

Don't worry,

worry I won't do anything cool without

I won't do anything cool without you.

you

So what is VBA?

so what is VBA as we discussed earlier

As we discussed earlier,

VBA stands for Visual Basic for

VBA stands for Visual Basic for Applications

applications and on the right hand side

and on the right-hand side of my screen, you will see a window over here

of my screen you will see a window over

that looks pretty old-fashioned.

here that looks pretty old-fashioned and

And that's because VBA is old-fashioned.

that's because VBA is old-fashioned this

This is my VBA window over here on the right-hand side.

is my VBA window over here on the right

And you'll see that there is something that kind of looks

hand side and you'll see that there is something that kind of looks like the

like the old-fashioned ribbon that we used to have,

old-fashioned ribbon that we used to

which really looks more like my quick access toolbar, right?

have which really looks more like my quick access toolbar right and then up

And then up here, I do have these old-fashioned dropdown menus.

here I do have these old-fashioned

So for those of us who thought

drop-down menus so for those of us who

we were done with using those,

thought we were done with using those we

we are going to see them in VBA.

are gonna see them in VBA and we'll talk

And we'll talk about what each one of these does.

about what each one of these does I'm

I'm also going to see in VBA on the left-hand side,

also gonna see in VBA on the left hand

a project panel under here's the properties panel.

side a project panel under here's the

We'll talk a whole lot later

properties panel we'll talk a whole lot

about what each one of these does.

later about what each one of these does

And then on the right-hand side,

and then on the right hand side I will

I will see a coding window over here

see a coding window over here that would

that would actually have code in it,

actually have code in it if I actually

if I actually had code here.

had code here now I don't have any code

Now, I don't have any code here specifically,

here specifically but in my project

but in my project panel on the left-hand side,

panel on the left hand side and we'll go

and we'll go over this in detail,

over this in detail I'm gonna find one

I'm going to find one of my modules that does have some code

of my modules that does have some code

and here my code is.

and here my code is this is what the

This is what the actual code in VBA looks like.

actual code in VBA looks like so let's

So let's talk about what the point of any of this is.

talk about what the point of any of this

VBA is a tool for programming,

is VBA is a tool for programming editing

editing and running application code .

and running application code by

By application code, I mean,

application code I mean whatever the

whatever the host application is, in this case Excel.

host application is in this case Excel

So the whole point of all this language over here is to actually run it

so the whole point of all this language over here is to actually run it using

using Excel. So run it in Excel.

Excel so run it in Excel VBA is not a

VBA is not a standalone program, right?

standalone program right it can usually

It can usually only run code within a host application.

only run code within a host application

So it's going to run all this code inside of Excel itself. It is a combination of Microsoft's

so it's gonna run all this code inside of Excel itself it is a combination of

event-driven program language,

Microsoft's event-driven program

which is a fancy name for

language which is a fancy name for just

just the VB, right, Visual Basic,

the VB right Visual Basic which is what

which is what we tend to call

we tend to call the the language that we

the language that we use here.

use here it's a combination of the

It's a combination of the language with the integrated development environment,

language with the integrated development

which is a really fancy name for the whole VBA interface.

environment which is a really fancy name for the whole VBA interface

One of the really neat things that VBA does allow us to do

one of the really neat things that VBA does allow us to do that we wouldn't

that we wouldn't otherwise be able to do

otherwise be able to do is it allows us

is it allows us to custom design our own functions.

to custom design our own functions so

So let's say in Excel, I need to regularly

let's say in Excel I need to regularly

add some things up.

add some things up so I got some numbers

So I got some numbers here and I want to add them up.

here and I want to add them up there's a

There's a neat function that does that,

neat function that does that it's called

It's called SUM.

sum it allows me to just grab some stuff

It allows me to just grab some stuff here and add it all up.

here and add it all up just like that

Just like that. Now this function has

now this function has already been

already been programmed by Excel, right?

programed by Excel

So the Excel programmers decided,

right so Excel programmers decided oh

oh, there's this thing

there's this thing that would be really

that would be really handy for most people,

handy for most people we'll call it some

we'll call it SUM, it adds some stuff up.

it adds some stuff up now let's say I

Now let's say I need to be able to do something

need to be able to do something for

for which there isn't already a function in Excel.

which there isn't already a function in

In that case,

Excel in that case I'm gonna need to

I'm going to need to make my own;

make my own I can't actually do that in

I can't actually do that in Excel, but I can do that with VBA.

Excel but I can do that with VBA so I

So I can come over here into my VBA interface

can come over here into my VBA interface

and design a function here.

and design a function here and then once

And then once I've designed it in VBA,

I've designed it in VBA I can actually

I can actually use it in Excel.

use it in Excel so if you need to

So if you need to calculate the area of a triangle,

calculate the area of a triangle there's

there's not a function that does that, but you can make one.

not a function that does that but you

If you need to calculate percent of change over time,

can make one if you need to calculate percent of change over time there is not

there is not a standard function in Excel that does that,

a standard function in Excel that does

but you can make one,

that but you can make one you can make

you can make one in VBA and you can use it from then on out.

one in VBA and you can use it from then

And one of the last things we'll talk about,

on up and one of the last things we'll

that's really important to know about

talk about that's really important to

Visual Basic for Applications

know about Visual Basic for applications

is that it does expand

is that it does expand on the abilities

on the abilities of earlier application-specific

of earlier application specific macro

macro programming languages.

programming languages so it expands on

So it expands on earlier languages like Word's Word Basic.

earlier languages like words word basic

Because it expands on these older languages,

because it expands on these older

we're going to see some language that's really old

languages we're gonna see some language

and some language that is new.

that's really old and some language that

With every new incarnation of Microsoft Office,

is new with every new incarnation of

there are updates to VBA as well.

Microsoft Office there are updates to

So you'll notice throughout our learning about VBA,

VBA as well so you'll notice throughout our learning about VBA that you will see

that you will see some coding statements

some coding statements that are a little

that are a little bit old-fashioned,

bit old-fashioned right next to some

right next to some coding statements that are brand new.

coding statements that are brand new and

And we'll talk about that a whole lot more

we'll talk about that a whole lot more

once we actually get there.

once we actually get there

Object Oriented Programming is an excellent description

object-oriented programming is an

for the coding language that we are going to be using

excellent description for the coding language that we are going to be using

as we get to know VBA a little bit.

as we get to now VBA a little bit

Object Oriented Programming is a great descriptor

object-oriented programming is a great

for the kinds of languages that tend to be used

descriptor for the kinds of languages

with a lot of applications these days.

that tend to be used with a lot of applications these days so probably most

So probably most of the applications that you're using

of the applications that you're using

are based on some kind of programming like that.

are based on some kind of programming

Certainly all of the Microsoft,

like that certainly all of the Microsoft

all the standard Microsoft Office applications

all the standard Microsoft Office

are based on an object oriented programming language.

applications are based on an object-oriented programming language

Let's talk about what that is supposed to mean.

let's talk about what that is supposed to mean what that means is that behind

What that means is that behind the scenes, there is all this programming language that happens

the scenes there is all this programming language that happens in each one of

and each one of these statements that happens

these statements that happen starts by

starts by defining some kind of an object.

defining some kind of an object in all

In all of your Microsoft applications,

of your Microsoft applications you've

you've probably noticed that in order to effect

probably noticed that in order to effect

some kind of a change,

some kind of a change

you always have to select

you always have to select on the thing

on the thing that you want to change first.

that you want to change first for

For instance, maybe I want to delete some text

instance maybe I want to delete some

in cell B4. Well, I know because I've been using these programs

text in cell b4 well I know because I've

for a long time that I need to actually click on B4

been using these programs for a long time that I need to actually click on

or select B4 first,

before or select before first before I

before I can actually delete the stuff there.

can actually delete the stuff there

Because I have selected on it,

because I have selected on it I can now

I can now delete the things that are in there.

delete the things that are in there

I could also change the color of a cell, but again,

I could also change the color of a cell

I must select it first.

but again I must select it first so I'm

So I'm selecting F5 here,

selecting f5 here and I'm going to turn

and I'm going to turn it green.

it green in order to make that kind of a

In order to make that kind of a change,

change I do have to select the object

I do have to select the object that I want to change first.

that I want to change first let's say

Let's say now I want to make a change to A1.

now I want to make a change to a 1 is

Is there any way for me to make a change to A1

there any way for me to make a change to

without selecting A1 first?

a 1 without selecting a 1 first the

The answer is no. And the reason is that the beginning

answer is no and the reason is that the

of every programming statement

beginning of every programming statement

must start by defining the object

must start by defining the object I want

I want to make a change to.

to make a change to so in the last

So in the last circumstance, I had to select on the cell

circumstance I had to select on the cell

and then delete the content, right?

and then delete the content right in

In this example here,

this example here I had to select on the

I had to select on the cell first and then turn it green.

cell first and then turn it green I

I cannot make any changes without selecting an object first.

cannot make any changes without

And the reason is that in these statements,

selecting an object first and the reason is that in these statements this is a

this is a really simple example over here,

really simple example over here I want

I want you guys to ignore most of what you see here,

you guys to ignore most of what you

so ignore all this stuff here.

see here so ignore all this stuff here

Just take a look at the very beginning.

just take a look at the very beginning

This statement here at the very top,

this statement here at the very top it's

it's defining an object first, it's saying,

defining an object first it's saying oh

oh, that thing that you already clicked on,

that thing that you already clicked on

do this to it, right?

do this to it right okay so we're gonna

Okay. So we're going to see this terminology used over and over

see this terminology used over and over

and over again.

and over again we're always gonna need

We're always going to need to state an object first.

to state an object first you can kind of

You can kind of think of it like a noun.

think of it like a noun

My dog is going for a walk, right?

my dog is going for a walk right in that

In that case, my dog is the noun, right?

case my dog is the noun right my dog is

My dog is the object of that sentence,

the object of that sentence with that

that statement, right?

statement right so noun and an object

So a noun and an object are sort of synonymous.

are sort of synonymous and in this case

In this case, we are going to be talking a whole lot more

we are gonna be talking a whole lot more

about the other grammatical options that we have

about the other grammatical options that

in VBA programming language,

we have in VBA programming language but

but we're starting with the one called an object.

we're starting with the one called an

So in this case, I want to make changes to cell A1.

object so in this case I want to make

So I have to click on cell A1.

changes to cell a1 so I have to click on

And if I were actually converting this into code,

cell a1 and if I were actually converting this into code you would

you would actually see the beginning of the statement say,

actually see the beginning of the

cell A1, that's the first thing it would say.

statement say cell a1 that's the first

Okay, so that's what object oriented programming

thing it would say okay so that's what

actually means to us.

object-oriented programming actually

If we want to get a little bit more techie with it,

means to us if we want to get a little

we could get into the fact that it's based on, obviously,

bit more techy with it we could get into the fact that it's based on obviously as

as we've already said, the concept of objects,

we've already said the concept of

which are data structures that contain data about an object

objects which are data structures that contain data about an object as well in

as well in the form of properties and also code

the form of properties and also code in

in the form of methods.

the form of methods and again we will be

And again, we will be talking about this all later.

talking about this all later but that is

But that is the basics of

the basics of object-oriented

Object Oriented Programming languages.

programming languages in the next

In the next section,

section we're actually going to be

we're actually going to be jumping up and doing some stuff.

jumping up and doing some stuff

So for everybody,

so for everybody who's just been hanging

who's just been hanging out with me so far.

out with me so far maybe you just been

Maybe you've just been sitting back

sitting back and just watching these

and just watching these first couple of things

first couple things that we talked about

that we talked about, now is the time for us to actually pop up

now is the time for us to actually pop

and start working on some stuff together.

up and start working on some stuff

So what I want you to do is pause the video

together so what I want you to do is pause the video and open up Excel for

and open up Excel for yourself.

yourself so I want you guys to open up

So I want you guys to open up your Excel program,

your Excel program pause the video and

pause the video and do that.

do that and once you have it open

And once you have it open, continue to the video,

continue the video and then we will

and then we will start making

start making those basic modifications

those basic modifications to Excel

to excel so that we can start making

so that we can start making some macros.

some macros

All right, so hopefully you're all caught up with me.

all right so hopefully you're all caught

Hopefully you have already opened Excel

up with me hopefully you have already opened Excel and opening up a completely

and opening up a completely blank workbook is fine.

blank workbook as fine I am in my

I am in my template called book one, right?

template called book 1 right that's the

That's the one that we get into every time we open up Excel

one that we get into every time we open

and just say, we just want a blank workbook.

up its own say we just want a blank

And I do have my window fully expanded here. I am in Excel 2016.

workbook and I do have my window fully expanded Here I am in Excel 2016 however

However, I have mentioned this in the first video,

I have mentioned this in the first video

but it definitely bears repeating.

but it definitely bears repeating if you

If you are using an earlier version of the program

are using an earlier version of the

like Excel 2007, Excel 2010

program like Excel 2007 Excel 2010 or

or Excel 2013,

Excel 2013 everything that we're gonna

everything that we're going to be doing in this class is also doable in each one of those versions of the program.

be doing in this class is also doable in each one of those versions of the

If you are using an older version of that, no worries.

program if you are using an older version of that no worries see there

There might be little things here and there

might be little things here and there

that cause a little bit of a hiccup.

that cause a little bit of a hiccup and

And by simply Googling those little things,

by simply googling those little things

we tend to be able to find fixes very quickly.

we tend to be able to find fixes very

They're usually the very first thing

quickly they're usually the very first

that comes up in our Google search.

thing that comes up in our Google search

Now in Excel 2016, it looks a whole lot like Excel 2013.

now in Excel 2016 it looks a whole lot like Excel 2013 I do have my normal tabs

I do have my normal tabs of the ribbon

of the ribbon on the upper left-hand

on the upper left-hand side of my screen.

side of my screen I have my file tab my

I have my File tab, my Home tab, Insert, Page Layout, Formulas, Data, Review, View.

Home tab insert page layout formulas data review view and then I have this

And then I have this area that says,

area that says tell me what you want to

tell me what you want to do.

do this is one of the new features of

This is one of the new features of 2016. It is pretty cool in here.

2016 it is pretty cool in here I could

I could look for anything,

look for anything like I want to make

like I want to make something bold

something bold

and I could just access the bold tool right there.

and I could just access the bold tool

And it actually turns it on directly from up there.

right there and it actually turns it on directly from up there so if I wanted to

So if I wanted to insert a pivot table

insert a pivot table and I couldn't

and I couldn't figure out where those are, right,

figure out where those are right you can

you can access it there.

access it there it's pretty cool anyway

It's pretty cool. Anyway, so the one tab of the ribbon that I do need

so the one tab of the ribbon that I do

that I don't see is called the Developer tab

need that I don't see is called the Developer tab and we're gonna need to

and we're going to need to turn it on

turn it on in order to be able to play

in order to be able to play with all of the macro stuff

with all of the macro stuff that we're

that we're going to be taking a look at.

gonna be taking a look at so that's the

So that's the first thing that we're going to do.

first thing that we're gonna do anytime

Anytime we want to customize our ribbon,

we want to customize our ribbon that is

that is a customization of Excel.

a customization of Excel anytime we want

Any time we want to customize Excel,

to customize Excel we can access those

we can access those options in the file tab.

options in the file tab and if we go to

And if we go to the file tab all the way down at the bottom,

the file tab all the way down at the

we'll see the menu item that says Options.

bottom we'll see the menu item that says options we can

We can click on that.

click on that that will open our Excel

That will open our Excel Options window.

options window

A bunch of amazing customization features in here.

a bunch of amazing customization

I'm going to have us skip right down

features in here I'm gonna have us skip

to the option that says, Customize the Ribbon.

right down to the option that says customize the ribbon and on the right

And on the right-hand side of the

hand side of the customize the ribbon

Customize the Ribbon window, you will see a list of the tabs of the ribbon

window you will see a list of the tabs

that are enabled currently.

of the ribbon that are enabled currently

And if you have any tabs of the ribbon that are disabled,

and if you have any tabs of the ribbon that are disabled the boxes next to them

the boxes next to them will be unchecked.

will be unchecked in my case I do have a

In my case, I do have a tab of the ribbon called Developer.

tab of the ribbon called developer

However, the box is unchecked.

however the box is unchecked now that's

Now that's actually the tab I want.

actually the tab I want so in order to

So in order to be able to actually get this tab,

be able to actually get this tab I'm

I'm going to click on the box and click, Okay.

gonna click on the box and click OK

And that's going to enable that tab of the ribbon for me.

and that's gonna enable that tab of the

I can click on it and take a look on it.

ribbon for me I can click on it and take a look on it

And that is where we're going to get started here.

that is where we're gonna get started

So that's the first thing that we need to do,

here so that's the first thing that we

do that with me, please.

need to do do that with me please go to

Go to your File tab,

your file tab go all the way down to

go all the way down to Options. We're looking for the ability to Customize the Ribbon.

options we're looking for the ability to customize the ribbon and on the right

And on the right-hand side,

hand side we're gonna find the tab

we're going to find the tab called the Developer tab.

called the Developer tab check the box

Check the box and click Okay.

and click OK why don't you go ahead and

Why don't you go ahead and pause the video

pause the video and make sure that you

and make sure that you have done just that

have done just that and that you can

and that you can actually see all of the options

actually see all of the options on the

on the Developer tab.

Developer tab and once you've done that

And once you've done that, come rejoin me

come rejoin me and then we will actually

and then we will actually start recording some macros.

start recording some macros

Lucky for us these days,

lucky for us these days we don't

we don't actually have to know any coding whatsoever

actually have to know any coding

to create our very own macro

whatsoever to create our very own macro

because we have this amazing tool called

the Macro Recorder.

the macro recorder on the Developer tab

On the Developer tab, on the left-hand side,

on the left hand side you're gonna see a

you're going to see a command group called Code.

command group called code in that

In that command group,

command group you're gonna see a lot of

you're going to see a lot of buttons

buttons that we're gonna be clicking on

that we're going to be clicking on today.

today the very first one that we're

The very first one that we're going to start playing with

gonna start playing with is record macro

is Record Macro. VBA, what it does when we click on Record Macro

VBA what it does when we click on a

is it watches everything that we do in Excel,

record macro is it watches everything that we do in Excel and then it converts

and then it converts it into code.

it into code and once we have that code

And once we have that code stored safely in VBA,

stored safely in VBA we can choose to re

we can choose to re-execute that whenever we want.

execute that whenever we want so let's

So let's say for instance, a couple of times a day,

say for instance a couple times a day

I'm getting these reports from a neighboring department

I'm getting these reports from a neighboring department and it's my job

and it's my job to put on a report title.

to put on a report title and then I also

And then I also have to format that report title.

have to format that report title maybe I

Maybe I have to turn it bold and italics and underline,

have to turn it bold and italics and

and I have to put an outline on it

underline and I have to put an outline

and I have to turn it green

on it and I have to turn it green and I

and I have to make the text white

have to make the text white and I have

and I have to make it bigger.

to make it bigger maybe I have to do

Maybe I have to do that every single time

that every single time I need to put

I need to put this report title in.

this report title in if I need to do

If I need to do that every single time,

that every single time that's six or

that's six or seven steps that I might forget one of,

seven steps that I might forget one of

or maybe I want to be able to delegate

this task to somebody else

this task to somebody else and I'm

and I'm concerned that they will forget one of the steps

concerned that they will forget one of

or possibly I'm asking them to do

the steps or possibly I'm asking them to

a bunch of these things in this process

do a bunch of these things in this

and I'm concerned that they don't actually know

process and I'm concerned that they

how to do one of them, right?

don't actually know how to do one of

Maybe they don't know where to find the border button.

them alright maybe they don't know where to find the border button in that case I

In that case, I would really prefer if I could save the process of going through this

would really prefer if I could save the process of going through this as a macro

as a macro and then have VBA

and then have VBA

redo all these steps for me

redo all these steps for me whenever I

whenever I would like.

would like alright I'm gonna clear out

All right, I'm going to clear out the contents here,

the contents here so we can all do this

so we can all do this together.

together go into the developer tab and

Go into the Developer tab.

I'm gonna ask

And I'm going to ask VBA to

VBA to please watch everything that I do

please watch everything that I do in the next little section and store it as a macro

in the next little section and store it

so that I can rerun it every single time

as a macro so that I can rerun it every

I need to generate a report title.

single time I need to generate a report title

So the Macro Recorder is going to do that for me.

so the macro recorder is gonna do that

I'm going to click on Macro Recorder.

for me I'm gonna click on macro recorder

And the first thing it's going to make me do

and the first thing it's gonna make me

is name this macro that I'm creating.

do is name this macro that I'm creating

It's also going to ask me

it's also going to ask me if I want to a

if I want to assign a shortcut key here,

sort assign a shortcut key here and then

and then it's going to ask me

it's gonna ask me where I want to store

where I want to store this macro. So here are the details here.

this macro so here are the details here

The macro name,

the macro name there is a naming

there is a naming convention for this.

convention for this we must have no

We must have no spaces.

spaces it also has to start with a

It also has to start with a letter.

letter so I'm gonna call this report

So I'm going to call this report title, no space.

title no space if you would like a space

If you would like a space in there,

in there you can just put an underscore

you can just put an underscore in there.

in there that'll help you synthesize a

That'll help you synthesize a space, but you don't have to.

space but you don't have to you can't

You can assign a shortcut key if you would like.

assign a shortcut key if you would like

Maybe I'll call this Control.

maybe I'll call this um control control

Control comes free of charge.

comes free of charge you don't actually

You don't actually get a choice

get a choice whether or not to use the

whether or not to use the Control.

control you always have to have it which

You always have to have it, which is actually a good thing

is actually a good thing because if I

because if I assigned it to something really commonly used,

assigned it to something really commonly

the problem is that it replaces or it overwrites, or it ignores other shortcuts that I have.

used the problem is that it replaces or it overwrites or it ignores other shortcuts that I have so for instance if

So for instance, if I use Control + S all the time to save my documents,

I use control s all the time to save my

if I assigned Control + S to a macro,

documents if I assigned control s to a

what it would do is ignore the save function

macro what it would do is ignore the save function and it would just run my

and it would just run my macro.

macro so I do want to be really mindful

So I do want to be really mindful of this. That's why a lot of us macro recorders,

of this that's why a lot of us macro

frequent macro recorders tend to assign shortcut keys

recorders frequent macro recorders tend

that also have a shift in there.

to assign shortcut keys that also have a

So I'm going to put my Shift + S in

shift in their so I'm gonna put my shift

or my, in this case, I'm going to put Shift + R and you'll see that I do have this

s in or my in this case I'm gonna put shift R and you'll see that I do have

Shift added to the shortcut key.

this shift added to the shortcut key now

Now, there are a couple of different places

there are a couple different places

where you can store your macro.

where you can store your macro you can

You can store it in this workbook,

store it in this workbook or you can

or you can store it in your personal macro workbook.

store it in your personal macro workbook

The differences are,

the differences are if you store it in

if you store it in this workbook,

this workbook it will be able to travel

it will be able to travel with this document.

with this document so you can send it

So you can send it off to a coworker.

off to a co-worker they will also be

They will also be able to use this macro.

able to use this macro so that's kind of

So that's kind of nice. However,

nice however it does mean that you can't

it does mean that you can't really use it

really use it in any of your other Excel

in any of your other Excel documents, unless you also have this one open.

documents unless you also have this one

So if I want to be able to use this macro across the board

open so if I want to be able to use this

in all kinds of different documents as well,

macro across the board in all kinds of different documents as well I'm instead

instead, gonna store it in my personal macro workbook.

gonna store it in my personal macro

Now, for our purposes,

workbook now for our purposes I'm gonna

I'm going to have us store it in this workbook.

have us store it in this workbook that's

That's going to make it easier for us.

gonna make it easier for us this is

This is a really simple example,

really simple example probably gonna

probably gonna want to delete it after we do it.

want to delete it after we do it so

So let's just do this workbook and click Okay.

let's just do this workbook and click OK

And now VBA is watching everything that we do,

and now VBA is watching everything that

and it's going to turn it into code.

we do and it's gonna turn it into code

So the very first thing that I want to do is tell VBA,

so the very first thing that I want to

hey, I always need to put my report title in cell C2.

do is tell the BA hey I always need to put my report title in cell c2 so the

So the way that I'm going to do that,

way that I'm gonna do that even though

even though I'm already clicked on C2,

I'm already clicked on see it on c2 even

even though I already have it selected,

though I had to already have it selected

I'm actually going to take my mouse

I'm actually gonna take my mouse and

and click on it again.

click on it again now because I just

Now, because I just started recording,

started recording that's the very first

that's the very first action that it is going to record.

action that it's gonna record so I'm

So I'm sure that my report title is always going to be in C2

sure that my report title is always gonna be in c2 and now I'm gonna type in

and now I'm going to type in the name of my title.

the name of my title and I'm executing

And I'm executing that with Control + Enter,

that with control-enter which is my

which is my favorite execution method.

favorite execution method the thing I

The thing I like about it

like about it is that it doesn't

is that it doesn't actually navigate me away.

actually navigate me away so if I use

So if I used Enter, it would execute,

enter it would execute but it would

but it would navigate me downward one cell.

navigate me downward one cell whereas if

Whereas if I use Tab, it would execute,

I use tab it would execute but it would

but it would navigate me to the right one cell.

navigate me to the right one cell I'm

So I'm going to be using Control + Enter constantly in these videos.

gonna be using control-enter constantly

And I use it constantly in Excel.

in these videos and I use it constantly in Excel it makes it a whole lot easier

It makes it a whole lot easier for me to execute

for me to execute and and continue to do

and continue to do things I want to be able to do

things I want to be able to do without

without really having to re-click on stuff.

re having to read click on stuff so I'm

So I'm going to be using Control + Enter a whole lot.

gonna be using ctrl enter a whole lot

All right, Control + Enter to execute.

all right ctrl enter to execute go into

Go into the Home tab

the home tab and I'm gonna make all

and I'm going to make all these formatting changes that I

these formatting changes that I would

would like to make.

like to make and you guys can go ahead

And you guys can go ahead and do this with me.

and do this with me so we are choosing

So we are choosing to make some formatting changes.

to make some formatting changes you guys

You guys don't have to do

don't have to do the exact same ones

the exact same ones that I'm doing,

that I'm doing but these are just

but these are just example formatting changes

example formatting changes that I'm

that I'm making here. And once I'm done,

making here and once I'm done I am going

I am going to make sure to go back to the Developer tab

to make sure to go to back to the

and click on Stop Recording.

Developer tab and click on stop recording this is one of those things

This is one of those things that gets forgotten, even by the most experienced

that gets forgotten even by the most

macro gurus out there.

experienced macro gurus out there so you

So you really want to make sure to click on Stop Recording.

really want to make sure to click on

Now that I've clicked on Stop Recording,

stop recording now that I've clicked on

I do have my Record Macro button back.

stop recording I do have my record macro

That's what it looks like

button back that's what it looks like

once I've actually clicked on Stop Recording.

once I've actually clicked on stop

And now I have that macro,

recording and now I have that macro so

so that macro is saved.

that macro is saved so let's say I need

So let's say I need to be able to rerun this

to be able to rerun this in another

in another place. Well, maybe I have another sheet here.

place well maybe I have another sheet here I'm gonna go down and click on this

I'm going to go down and click on this Plus button. In Excel 2010, obviously that looks

plus button in Excel 2010 obviously that

like a different icon there, right?

looks like a different icon there right

But whatever your icon is,

but whatever your icon is I'm clicking

I'm clicking on that icon and create a brand new sheet here.

on that icon and create a brand new

And then I'm going to try and run my macro here.

sheet here and then I'm gonna try and

So I'm going to my Macros button.

run my macro here so I'm going to my macros

I'm going to find that macro that I just created.

I'm gonna find that macro that I just created again that macros button in the

Again, that Macros button in the ribbon,

ribbon that's where I'm gonna find the

that's where I'm going to find the list of

list of all the macros that I have and

all the macros that I have. And here's my list right here.

here's my list right here

And I'm going to find that macro that I created,

and I'm gonna find that macro that I

click on it and click on Run.

created click on it and click on run and

And that's going to run that macro right there,

that's going to run that macro right

all six steps,

there

all six or seven or eight or 600 steps, all at once.

all six steps all six or seven or eight or 600 steps all at once and that's the

And that's the whole point of the Macro Recorder.

whole point of the macro recorder

Now, when we just ran this macro that we just created, the reason why it looked so nice and perfect

now when we just ran this macro that we just created the reason why it looked so

is because it was set up so that it specifically recorded

nice and perfect is because it was set up so that it specifically recorded and

and then ran this macro in cell C2.

then ran this macro in cell c2 and the

And the reason is that by default,

reason is that by default all these

all these macros are going to be recording

macros are going to be recording using

using absolute cell referencing.

absolute cell referencing um just for

Just for those of us

those of us who are not familiar with

who are not familiar with that term,

that term if you've ever seen a cell

if you've ever seen a cell address

address that has these dollar signs of

that has these dollar signs in it, this is just an example,

it this is just an example but if you've

but if you've ever seen a cell address

ever seen a cell address that you've

that you've used in a formula or a function or something,

used in a formula or function or

and it actually had the dollar signs in it,

something and it actually had the dollar

or a bunch of other tools, that'll put it in there as well.

signs in it a bunch of other tools that will put it in there as well so the

So the purpose of those dollar signs is to lock or anchor

purpose of those dollar signs is to lock

the cell reference in place

or anchor the cell reference in place so

so that when you use the autofill handle,

that wouldn't use the autofill handle

maybe it doesn't migrate anywhere,

maybe it doesn't migrate anywhere that

that kind of thing.

kind of a thing now of course we do have

Now, of course,

the standard cell reference that doesn't

we do have the standard cell reference that doesn't have the dollar signs.

have the dollar signs that's just a 1

It's just A1. This is what's called a relative cell reference.

this is what's called a relative cell

So the other one was absolute, that's with the dollar signs,

reference so the other one was absolute that's what the dollar signs this one is

this one is called relative.

called relative and the whole purpose of

And the whole purpose of these regular cell references

these regular cell references is that

is that they do migrate.

they do migrate so if I was trying to

So if I was trying to make a list of calculations here,

make a list of calculations here I'm

I'm just going to set up an example,

just gonna set up an example nobody has

nobody has to do this with me,

to do this with me but if I was just

but if I was just setting up a table here

setting up a table here and I was asking

and I was asking Excel to please multiply this times this,

Excel to please multiply this times this

and then do this over and over and over again

and then do this over and over and over

for the rest of these cells.

again for the rest of these cells but I

But I want you to,

want you to instead of using the

instead of using the original cell references,

original cell references this function

this function is using the cell references in row two.

is using D cell references in row two

Whereas the next formula down is using the cell references

whereas the next formula down is using

from row three, right?

the cell references from row three right

So those cell references are migrating

so those cell references are migrating

and that's what a regular cell reference does.

and that's what a regular cell reference

If I put the dollar signs in there,

does if I put the dollar signs in there

it wouldn't change from F2 to F3,

it wouldn't change from f2 to f3 it

it would stay on, let's say F2, right.

would stay on let's say f2 right ok so

Okay, so if I do want to use relative referencing,

if I do want to use relative referencing

I'm going to need to turn on this button here that says,

I'm gonna need to turn on this button

Use Relative References.

here that says use relative references

Now, why would I want to do that?

now why would I want to do that in this

In this case, my macro did exactly what I wanted it to do,

case my macro did exactly what I wanted

and I didn't click on that button.

it to do when I didn't click on that

Well, let's say for instance,

button well let's say for instance I

I want to be able to run this macro in a different location.

want to be able to run this macro in a

Maybe I want to be able to run it in cell G2.

different location may B I want to be able to run it in cell g2

And then in a few minutes later,

and then in a few minutes later I want

I want to be able to run it in cell D8, right?

to be able to run it in cell d8 right

The problem is that the macro as I've recorded it

the problem is that the macro as I've

doesn't run anywhere else except for C2.

recorded it doesn't run anywhere else

And indeed, if I click on macro and again,

except for c2 and indeed if I click on

you don't have to do this with me,

macro and again you don't have to do

I'm just giving us an example,

this with me I'm just giving us an

and I choose my macro and I click on Run,

example then I choose my macro and I

kind of looks like nothing happened.

click on run it kind of looks like

And the reason is because it can't run anywhere else.

nothing happened and the reason is because it can't run anywhere else so it

So it basically just replaced it

basically just replaced it with what was

with what was already there.

already there

If I select on cell E3 and try to run my macro over here,

if I select on cell III and try to run

again it doesn't work because it only runs in cell C2.

my macro over here again it doesn't work because it only runs in cell c2 so if I

So if I want to be able to have this macro

want to be able to have this macro run

run in all these different spots,

in all these different spots then I'm

then I'm going to need to turn on Use Relative References. So in order for us to create a macro,

going to need to turn on use relative references so in order for us to create

that actually does this,

a macro that actually does this let's go

let's go ahead and click on another cell,

ahead and click on another cell

whichever cell we want to eventually start in before we click on Use Relative Reference.

whichever cell we want to eventually start in before we click on use relative

So in the last example,

reference so in the last example you

you probably noticed the very first thing that we did

probably noticed the very first thing

was click on the cell

that we did was click on the cell where

where we actually want the macro to run.

we actually want the macro to run in

In this case,

this case we're gonna click on the cell

we're going to click on the cell where we want the macro to run,

where we want the macro to run in this

in the first example, anyway,

first example anyway before we actually

before we actually record our macro, okay.

record our macro okay so I've selected

So I've selected on cell C6. I'm turning on my Use Relative Reference button,

on cell c6 I'm turning on my use relative reference button and I'm gonna

and I'm going to click on Record Macro.

click on record macro and we're just

And we're just going to make a really silly example here,

gonna make a really silly example here

we're going to say these are ExpenseTitles. Remember no spaces, has to start with a letter.

we're gonna say these are expense titles remember no spaces has to start with a letter

I'm going to skip the shortcut key.

I'm gonna skip the shortcut key I'm

I'm going to store it in the workbook

gonna store it in the workbook and I'm

and I'm going to click Okay. And we'll say, maybe this is payroll.

gonna click OK and we'll say maybe this is payroll

And maybe the next category is Office Supplies. Telephone.

and maybe the next category is office supplies telephone rent whatever it

Rent, whatever it might be.

might be just enter a couple of

Just enter a couple of different kinds of expenses there

different kinds of expenses there or

or whatever you would like.

whatever you would like and when you're

And when you're done, of course, make sure to execute.

done of course make sure to execute I am

I am using Control + Enter to execute that.

using control enter to execute that and

And then of course at the end,

then of course at the end I'm gonna

I'm going to click on Stop Recording.

click on stop recording

Okay, now I'm going to go ahead

okay now I'm going to go ahead and use

and use this in different circumstances.

this in different circumstances so maybe

So maybe I have a table over here

I have a table over here

and I want to be able to populate

I don't want to be able to populate all

all of my expense categories under here.

of my expense categories under here I

I can click on my Macros button,

can click on my macros button find my

find my ExpenseTitles and click on Run,

expense titles and click on run and it's

and it's going to run it over here.

going to run it over here and then I

And then I could also do the same thing over here.

could also do the same thing over here

The expenses I care about.

expenses I care about

And I'll run my macro over here.

and I'll run my macro ver here

So in this circumstance,

so in this circumstance I have created a

I have created a macro that can run

macro that can run in many different

in many different locations.

locations now you might be asking

Now you might be asking yourself at this point,

yourself at this point why wouldn't I

why wouldn't I always turn on relative references?

always turn on relative references the

The reason is that sometimes we definitely have the need

reason is that sometimes we definitely

to record our macro and run it in exactly the same spot.

have the need to record our macro and run it in exactly the same spot we're

We're going to be able to use this Record Macro feature

going to be able to use this record

in a bunch of different situations.

macro feature in a bunch of different

So we're not actually going to need to code it by hand,

situations so we're not actually going

which is really nice.

to need to code it by hand which is

But depending on the kind of thing that we're recording,

really nice but depending on the kind of thing that we're recording it would be

it would be handy to use absolute references.

handy to use absolute references let me

Let me give this a few examples.

give this a few examples if you're going

If you're going to create a macro,

to create a macro that's supposed to

that's supposed to sort stuff, right?

sort stuff right so maybe I have a list

So maybe I have a list and I always need to

and I always need to sort my expenses

sort my expenses, for instance.

for instance if my expenses are always

If my expenses are always going to be in that same column,

going to be in that same column in that

in that data set, I'm going to want to use

data set I'm gonna want to use the sort

the sort using the absolute references,

using the absolute references which

which means I'm going to want to

means I'm gonna want to turn off

turn off relative references

relative references before I actually

before I actually record that macro.

record that macro and the reason is that

And the reason is that sort macros work best

sort macros work best if you actually

if you actually use absolute referencing.

use absolute referencing filtering

Filtering macros are going to work exactly the same way.

macros are going to work exactly the

So again, sorting and filtering are great things

same way so again sorting and filtering

to use the Macro Recorder for,

are great things to use the macro

so long as you want to allow people

recorder for so long as you want to

to sort or filter by a specific thing in particular.

allow people to sort or filter by a specific thing in particular if that's

If that's the case,

the case those kinds of macros actually

those kinds of macros actually work a whole lot better

work a whole lot better if we use

if we use absolute referencing

absolute referencing and we'll see this

and we'll see this more in action

more in action as we continue through

as we continue through the course.

the course make sure that you've

Make sure that you've actually recorded a macro

actually recorded a macro using relative

using relative referencing.

referencing so again the process that we

So again, the process that we went through to do that

went through to do that is we did turn

is we did turn on Use Relative References.

on use relative references then we

Then we recorded our macro,

recorded our macro of course we named it

of course, we named it, chose to store it in the macro workbook,

chose two stored in the macro workbook

we clicked, Okay.

we clicked ok we then completed all of

We then completed all of the things that we wanted to do.

the things that we wanted to do and when

And when we were done,

we were done we clicked on stop

we clicked on Stop Recording

recording and then we actually tested it

and then we actually tested it out

out and ran it in a few different spots

and ran it in a few different spots. Why don't you pause the video right now and give that a shot

why don't you pause the video right now and give that a shot if you haven't done

if you haven't done so already,

so already or maybe give it another shot

or maybe give it another shot if you've already done it

if you've already done it just to kind

just to kind of get used to the process.

of get used to the process when you're

When you're done, go ahead and unpause the video

done go ahead and unpause the video and

and we will continue with some videos

we will continue with some videos

specifically about creating some useful macros.

specifically about creating some useful macros

Welcome back. All right.

back all right so what we're gonna be

So what we're going to be doing now is creating some very simple,

doing now is creating some very simple

yet very useful macros using the Macro Recorder.

yet very useful macros using the macro

Now, hopefully you've already downloaded

recorder now hopefully you've already

this file called Simple Macros.

downloaded this file called simple

If not, now would be a good time to pause the video

macros if not now would be a good time

and go ahead and download that particular file.

to pause the video and go ahead and download that particular file it should

It should be right there in your Alert at Anytime Interface.

be right there in your alert at any time interface and once you have it go ahead

And once you have it, go ahead and unpause the video

and unpause the video and we'll continue

and we'll continue with the exercises.

with the exercises okay I already have

Okay, I already have it

it so I'm gonna go ahead and click on it

so I'm going to go ahead and click on it. And here it is, it opened up for me.

and here it is it opened up for me

Now, let's say I have the

now let's say I have the regular

regular exercise

exercise of formatting certain numerical

of formatting certain numerical values.

values and I have a few challenges here

And I have a few challenges here. I do need these phone numbers here.

I do need these phone numbers here

However, all those phone numbers are actually for the UK.

however all those phone numbers are actually for the UK now they're showing

Now they're showing up like American phone numbers here.

up like American phone numbers here and

And so what I want to do

so what I want to do is convert this

is convert this into another kind of formatting,

into another kind of formatting but

but you'll notice if I go into my

you'll notice if I go into my number

Number Formatting dialog box here,

formatting dialog box here I can go down

I can go down to Special and choose Phone Number.

to special and choose phone number and I

And I could change this to something like UK,

could change this to something like UK

but it just converts it to general formatting, right?

but it just converts it to general formatting right so what I'm gonna do is

So what I'm going to do is actually create my own custom number formatting,

actually create my own custom number

and I'm not going to do it out here like normal,

formatting and I'm not gonna do it out here like normal because if I did it out

because if I did it out here,

here I would have to do it over and over

I would have to do it over and over and over again,

and over again every time I want it to

every time I wanted to do this.

do this what I'm gonna do is I'm gonna

What I'm going to do is I'm going to turn it into a macro.

turn it into a macro that way I can use

That way I can use it over and over and over again,

it over and over and over again whenever

whenever I would like to

I would like to using the keyboard

using the keyboard shortcut, much faster.

shortcut much faster so here we go I'm

So here we go. I'm going to start by going to the Developer tab

gonna start by going to the developer

and I'm going to record my macro,

tab and I'm going to record my macro but

but before I do so I'm going to ask myself two things.

before I do so I'm gonna ask myself two

First of all,

things first of all do I need to be

do I need to be using relative references

using relative references or absolute

or absolute references, right?

references right so do I need to turn on

So do I need to turn on the Use Relative References button?

the use relative references button so

So the real question, let me ask myself is,

the real question that we ask myself is

do I want to be able to use this particular macro

do I want to be able to use this

in this spot, in this spot,

particular macro in this spot in this

in this spot, in this spot, et cetera?

spot and this spot in this spot etc the

The answer to that is yes.

answer to that is yes so therefore I

So therefore I want to be able to use relative references

want to be able to use relative

so I have the flexibility to use this macro,

references so I have the flexibility to

wherever I would like.

use this macro wherever I would like

Then I am going to ask myself

then

whether I have already selected on the cell

I'm going to ask myself whether I have already select hit on the cell that I do

that I do want to format

want to format before I click the record

before I click the Record Macro button,

macro button because I'm using relative

because I'm using relative references,

references so I need to make sure the

so I need to make sure the first thing

first thing the macro recorder records

the Macro Recorder records is, oh, the cell that I'm already in, right?

is oh the cell that I'm already in right

If I clicked on Record Macro

if I clicked on record macro and then

and then the first thing I did was like click over here.

the first thing I did was like click

The first thing it's going to record is,

over here the first thing it's gonna

okay, move two cells to the right,

record is okay move to cells to the

move two cells down, right?

right move to cells down right don't

Don't want it to do that,

want it to do that gonna do a bunch of

it's going to do a bunch of wrong stuff in the wrong places,

wrong stuff in the wrong place that is

exactly where I don't want it to.

exactly where I don't want it to so I'm

So I'm selecting the cell before I start.

selecting the cell before I start and

And then I'll click on Record Macro,

then I'll click on record macro and I'm

and I'm going to name this one

gonna name this one

UKPhone

UK phone

and I'll assign a shortcut key for this one.

and I'll assign a shortcut key for this

And maybe the key is Shift + E.

one and maybe the key is shift a it's

It's safer to do Control + Shift shortcuts

safer to do control shift shortcuts

instead of Control shortcuts.

instead of control shortcuts there are

There are only three left that are unused

only three left that are unused and

and actually with the newer version of Excel,

actually with the newer version of Excel

there's only two

there's only to control something

control something shortcuts that are unused.

shortcuts that are unused so it's much

So it's much safer to do Control + Shifts,

safer to do control shift there are a

there are a lot more of those available.

lot more of those available so I'll use

So I'll use Control + Shift + E for England and click Okay.

control shift in E for England and click

And here I go,

OK and here I go I'm gonna go to the

I'm going to go to the Home tab of the ribbon, find my number, my Number command group,

Home tab of the ribbon find my number my

what I like to call the number formatting command group

number command group what I like to call the number formatting command group and

and I'm going to use this little button

I'm going to use this little button in

in the lower right hand corner of that command group

the lower right hand corner of that

to bring me into my number

command group to bring me into my number

formatting area.

formatting area so this is my format

So this is my Format Cells dialogue box.

cells dialog box I'm on the tab called

I'm on the tab called Number,

number and I'm gonna go down to my

and I'm going to go down to my custom number formats.

custom number formats and here we go I'm

And here we go, I'm going to change the formatting of this particular cell.

gonna change the formatting of this particular cell and here's what I want

And here's where we want it to show up. I always want the UK area code to show up at the beginning.

it to show up I always want the UK area code to show up at the beginning so the

So the country code for the UK is +44

country code for the UK is +4 4 so

so that's what I'm going to want first.

that's what I'm gonna want first then

Then I'm going to tell Excel,

I'm gonna tell Excel ok now you can

okay, now you can start displaying my numbers here

start displaying my numbers here and it

and it should be like this:

should be like this number number number

number, number, number, dash,

number never never - or more numbers and

four more numbers. And there are a few different ways

there are a few different ways to see UK

to see UK phone numbers displayed.

phone numbers displayed it's been a

It's been a while since I've been there,

while since I've been there but I think

but I think this is the way it was displayed

this is the way it was displayed last

last time I was there.

time I was there so that's the way I'm

So that's the way I'm going to do it.

gonna do it you guys could do it any way

You guys could do it any way you'd like,

you'd like and I'll go ahead and click

and I'll go ahead and click on Okay.

on okay and then that number is

And then that number is formatted just like that.

formatted just like that wonderful okay

Wonderful. Okay.

so I'm gonna go to my Developer tab and

So I'm going to go to my Developer tab and Stop Recording.

stop recording and now I should be able

And now I should be able to select

to select all the cells in this column

all the cells in this column and format them just like that.

and format them just like that so I'm

So I'm selecting all the cells in this column using,

selecting all the cells in this call I'm

I've already clicked on the top numerical cell there,

using I've already clicked on the top

and I'm using Control + Shift + Down

numerical cell there and I'm using

to select everything in that particular column.

control shift down to select everything in that particular column and I'm using

And I'm using my keyboard shortcut,

my keyboard shortcut which is control

which is Control + Shift + E for England.

shift e for England and there we go

And there we go. All my numbers are formatted like that,

all my numbers are formatted like that

going to go to the list on the right-hand side

I'm gonna go to the list on the

to practice it.

right-hand side to practice it here we

Here we go, selecting on the top phone number cell,

go selecting on the top phone number

and I'm using Control + Shift + Down

cell and am using control shift down and

to select all those guys.

select all those guys and then using

And then using Control + Shift + E

ctrl shift e to actually change to that

to actually change to that formatting.

formatting and it's just as easy as that

And it's just as easy as that. Alright. Maybe I work in HR

alright maybe I work in H R and I have

and I have the duty

the duty of keeping track of everybody's

of keeping track of everybody's birthdays.

birthdays however I really want to be

However, I really want to be sensitive about people's,

sensitive about people's anybody who

anybody who might feel a little bit age sensitive.

might feel a little bit age sensitive

And so I don't want to display

and so I don't want to display their

their birth year on their birthdays.

birth year on their birthdays I do want

I do want everybody to be able to know

everybody to be able to know what their

what their birth day is, but not their birth year.

birthday is but not their birth year so

So I would change my number formatting here.

I would change my number formatting here

Now I'm going to do this again as a macro

now I'm gonna do this again as a macro

so that I can do it whenever I want to

with the keyboard shortcut. Okay, so

with the keyboard shortcut okay so here

here we go.

we go here's another example of using

Here's another example of using relative references,

relative references want to be able to

want to be able to do this in a bunch of different places,

do this in a bunch of different places

so I am using relative references.

so I am using the relative references I

I have clicked on a cell

have clicked on a cell that has the

that has the values in it that I want to format

values in it that I want to format

before I actually click on Record Macro.

before I actually click on record macro

So I'm all good.

so I'm all good so I'm gonna click on

So I'm going to click on Record Macro and here we go.

record macro and here we go

Here's the second macro we're going to make.

here's the second macro we're gonna make

This is all going to be about birthdays.

this is all gonna be about birthdays so

So I'm going to say BirthdayNoYear.

I'm gonna say birthday no year

And my shortcut key for this one is going to be B,

and my short cookie for this one is

B for birthday and I'll click Okay.

gonna be bb4 birthday and I'll click OK

And here we go.

and here we go I'm going to the Home tab

I'm going to the Home tab of the ribbon

of the ribbon and on the right hand side

and on the right-hand side, you'll see it's currently listed as a date format,

you'll see it's currently listed as a

but I want to make this a little bit more interesting.

date format but I want to make this a

So I'm going to the Number,

little bit more interesting so I'm going to the number more options button which

More Options button, which brings me into the Format Cells dialogue box.

brings me into the format cells dialog

And the left tab is number.

box and the left tab is number I'm going

I'm going down to Custom

down to custom and I'm gonna assign this

and I'm going to assign this as Month

as month and maybe I want it to show up

and maybe I want it to show up as the full month, so I'm going to put four M's in there.

as the full month so I'm gonna put four

That's going to show up as July

M's in there that's going to show up as

and you'll see up in this sample area

July and you'll see up in this sample

that it is telling me how this thing

area that it is telling me how this

is going to display so far.

thing is going to display so far so I

So I want the month and I want the day,

want the month and I want the day

two digits is fine.

two digits is fine

Okay. And I'll click Okay.

okay and I'll click okay and that's it

And that's it. I'm all done.

I'm all done and go into the Developer

Go into the Developer tab, Stop Recording.

tab stop recording and I can grab all

And I can grab all the cells in this particular column

the cells in this particular column and

and use Control + Shift + B for birthday

use ctrl shift B for birthday and there

and there it is converted.

it is converted

Maybe I'll do that on the right-hand side as well.

maybe I'll do that on the right-hand

Control + Shift + Up this time

side as well control shift up this time

and Control + Shift + B for birthday.

and control shift B for birthday

All right, so I've been able to do that fairly easily.

all right so I've been able to do that

Now I did that with

fairly easily now I did that with the

the Number Formatting dialog box, right,

number formatting dialog box right the

the Format Cells dialogue box using the Number tab. So sometimes what I need to do

format cells dialog box using the number tab so sometimes what I need to do can't

can't actually be accomplished by

actually be complet

those Number Formatting options, even the custom ones.

those number formatting options even the

And so I'm going to need to

custom ones and so I'm gonna need to rig

rig it up a little bit more interestingly,

it up a little bit more interestingly

for instance here,

for instance here I have a list of a

I have a list of a bunch of people's

bunch of people's social security

social security numbers. And my issue is that

numbers and my issue is that I don't

I don't want anybody

want anybody to be able to see anything

to be able to see anything, but the last four digits of their social security number.

but the last four digits of their social

And so I've decided I'm going to

security number and so I've decided I'm

convert this entire document

gonna convert this entire document so

so that the only data that's stored here

that the only data that's stored here is

is the last four digits of their social security number.

the last four digits of their social

Now there's not an easy way to change the formatting here,

security number now there's not an easy way to change the formatting here so I'm

so I'm going to have to use some functions to do it.

gonna have to use some functions to do

Now, there are a couple of functions I really like. We do have the RIGHT function.

it now there are a couple functions I really like we do have the right

This is one of my favorite text functions,

function this is one of my favorite text

pulls a certain number of characters

functions pulls a certain number of

from one side of a cell,

characters from one side of a cell in

in this case, the right side.

this case the right side there's also

There's also one called LEFT,

one called left and there's also one

and there's also one called MID.

called mid this one's really easy you

This one's really easy.

just choose the text that you want to

You just choose the text that you want to pull characters from,

pull characters from or the numbers of

or the numbers of course,

course that you want to pull numbers

that you want to pull numbers from.

from you select that cell and then you

You select that cell, and then you tell it

tell it how many characters you want all

how many characters you want to pull, right? So it's as easy as that.

right so it's as easy as that now let's

Now let's say I want to make it

say I want to make it a little bit more

a little bit more interesting. I want to get this thing to

interesting I want to get this thing to

also display some characters

also display some characters on the left

on the left-hand side of those four digits

hand side of those four digits that sort

that sort of imply that they're hidden there.

of imply that they're hidden there so

So I'm going to put in a parentheses

I'm gonna put in a parenthesis and put

and I'm going to use the asterisks,

it and I'm gonna use the asterisk

and this is how I'm going to want it to show up.

and this is how I'm going to want it to

So the asterisks on the left-hand side with the dashes,

show up so the asterisks on the left hand side with the dashes and then I

and then I want to combine that.

want to combine that so I'm going to use

So I'm going to use my ampersand.

my ampersand want to combine that with

I want to combine that with the value that's returned by the RIGHT function.

the value that's returned by the right function so I'm going to go ahead and

So I'm going to go ahead and execute that.

end execute that and then I'm gonna ask

And then I'm going to ask this function to please

this function to please populate this

populate this for the rest of the column.

for the rest of the column

Now, eventually what I'm going to do

now eventually what I'm going to do is

is copy and paste this over here

copy and paste this over here to replace

to replace the social security values.

the social security values but

But unfortunately,

unfortunately what I got going on here

what I got going on here is these cells

is these cells are requiring information

are requiring information from these cells, right?

from these cells right so if I did that

So if I did that right now,

right now if I copied and pasted it

if I copied and pasted it right now,

right now it would still have the

it would still have the function.

function the function is still looking

The function is still looking for stuff from E8, right,

for stuff from 8 right so it would give

so it would give me a reference warning.

me a reference morning so what I'm gonna

So what I'm going to do instead of that

do instead of that is copy and paste

is copy and paste this function.

this function so I'm gonna copy it and

So I'm going to copy it. And then I'm going to just paste the values that are

then I'm gonna just paste the values

returned by the function.

that are returned by the function so I

So I can use my regular Control + C to copy,

can use my regular ctrl C to copy but

but then I'm going to go over to my handy Paste button

then I'm gonna go over to my handy paste

and click on the triangle underneath it.

button and click on the triangle

And I'm going to choose Paste Values,

underneath it and I'm gonna choose paste

one of my favorite buttons.

values one of my favorite buttons

All right, now I still have my flashing marquee around it,

all right now I still have my flashing

so I don't have to copy it again.

marquee around it so I don't have to

However, if I wanted to, I could,

copy it again however if I wanted to

but I'm going to go over here

like you could but I'm gonna go over

in a social security number column.

here in a social security number column

And this is where I'm actually going to

and this is where I'm actually gonna

paste those values, and here I go pasting them.

paste those values and here I go

I just pasted it with the Enter key.

pasting him I just pasted it with the

You could do it with Control + V as well,

Enter key you could do it with ctrl V as

but Enter's designed to paste once

well but enter is designed to paste once

and remove it from the clipboard, which is why I like it.

and remove it from the clipboard which is why I like it okay great so we got

Okay, great. So we got those values pasted over here.

those values pasted over here looks

It looks pretty good.

pretty good so I'm gonna go over here

So I'm going to go over here and delete

and delete all this stuff over here

all this stuff over here, using Control + Shift + Down to select it all,

using ctrl shift down to select it all

then hitting my Delete key to remove it.

then hitting my Delete key to remove it

And finally,

and finally I am going to select that

I am going to select that very first cell there,

very first cell there the cell that I

the cell that I started with,

started with and I'm gonna turn off my

and I'm going to turn off my macro.

macro

Okay, so that's the process that I'm going to go through.

okay so that's the process that I'm

Let's do it.

gonna go through let's do it now

Now, normally when you're doing this,

normally when you're doing this this is

this is like a five step process.

like a five-step process so this is the

So this is the time when you would want to

time when you would want to start to

start to write down every single step

write down every single step and then

and then you'd follow every single step.

you'd follow every single step as soon

As soon as it gets beyond a few steps,

as it gets beyond a few steps that's

that's what you want to do.

what you want to do and indeed I've

And indeed, I've written down all the steps for myself,

written down all the steps for myself so

so I'm ready to go.

I'm ready to go so I'm gonna click on

So I'm going to click on Record Macro here and here we go.

record macro here and here we go we're

We're going to call this the SocialLastFour

gonna call this the social last four and

and I'll use Control + Shift + S for that particular shortcut. And here we go.

I'll use ctrl shift s for that particular shortcut and here we go

Great, so we were able to record

great so we were able to record those

those couple of simple macros

couple of simple macros that really made

that really made our job a whole lot easier,

our job a whole lot easier getting these

getting these birthdays and these phone numbers

birthdays and these phone numbers to

to show up exactly like we want.

show up exactly like we want now I have

Now I have a larger challenge.

a larger challenge and so this is gonna

And so this is going to require a few steps for me.

require a few steps for me I'm gonna

I'm going to need to put a function in there.

need to put a function in there I'm also

I'm also going to need to put a formula in there,

gonna need to put a formula in there and

and then I'm going to need to copy stuff

then I'm gonna need a copy stuff and

and paste it as values.

paste it as values that's gonna be a

That's going to be a little bit of a process.

little bit of a process and so what I'm

And so what I'm starting to do at this point,

starting to do at this point at this

at this point in my macro recording career is

point in my macro recording career is

start to write down all the steps

start to write down all the steps that

that are required by my macro.

are required by my macro so all of the

So all of the steps that I actually want recorded,

steps that I actually want recorded I'm

I'm going to break them down on a piece of paper

going to write them down on a piece of

and that's going to help me

paper and that's gonna help me when I

when I actually record the macro,

actually record the macro not to make

not to make any mistakes,

any mistakes because obviously any

because obviously any mistakes that I made

mistakes that I made while I'm recording

while I'm recording do get recorded, right?

do get recorded right so I've written

So I've written down all my steps,

down all my steps and now we're gonna go

and now we're going to go ahead and do this.

ahead and do this so here's my dilemma I

So here's my dilemma. I have a social security numbers column,

have a social security numbers column

and I only want the last four digits to display.

and I only want the last four digits to

What I'm going to be doing is using a combination

display what I'm gonna be doing is using

of a formula and a function to do that.

a combination of a formula and a function to do that so I'm gonna be

So I'm going to be saying,

saying hey Excel and you don't have to

hey, Excel, and you don't have to do this with me right now,

do this with me right now we're all

we're all gonna do it together in just a second.

gonna do it together in just a second

But what I'm going to do is tell Excel,

but what I'm gonna do is tell Excel hey

hey, I want you to use this cool function called RIGHT.

I want you to use this cool function called write and you're going to use

And you're going to use that function

that function of pull them write four

and pull the right four digits from that number.

digits from that number

Okay, so it's going to pull the right four digits.

so it's gonna pull the right four digits

Now I'm going to make it a little bit more interesting

now I'm gonna make it a little bit more

because maybe I want this number to show up,

interesting because maybe I want this

with the kind of giving the implication

number to show up with the kind of

that there are other digits that somebody else can't see.

giving the implication that there are other digits that somebody else can't

If I wanted to do that,

see if I wanted to do that I just put in

I just put in some asterisks, right? So I put in, maybe I'd use an asterisks

some asterisks right so I put in maybe I'd use an asterisks

couple times, right?

couple times right so I'd use it for the

So I'd use it for the first three digits,

first three digits for the second two

for the second two digits. And I put dashes in between those numbers

digits and I put dashes in between those

and then a dash after those asterisks.

numbers and then a dash after those asterisks so that right after that it'll

So that right after that,

populate the four digits from the

it'll populate the four digits from the right-hand side

right-hand side with social security

of the social security number. Now, any time I actually want characters

number now any time actually want

to show up in my cell at the end,

characters to show up in my in my cell

I do have to surround them with quotation marks.

at the end I do have to surround them

So I'm going to go ahead and do that.

with quotation marks so I'm going to go ahead and do that

And then in between my formula that I got going on there

and then in between my formula that I

and the function itself,

got going on there and the function

I have to tell Excel,

itself I do I have to tell Excel hey I

hey, I want you to combine this stuff.

want you to combine this stuff and the

And the way that I do that is with the ampersand,

way that I do that is with the ampersand

that's the shorthand for it.

that's the shorthand for it okay and

Okay, and then when I execute that,

then when I execute that that's how my

that's how my number is going to show up.

numbers going to show up so that's great

So that's great. And then when I'm all done here,

and then when I'm all done here what I'm

what I'm going to do is I'm going to copy that

gonna do is I'm gonna copy that and I'm

and I'm going to paste it over here,

gonna paste it over here over the Social

over the social security numbers.

Security numbers now I can't actually

Now I can't actually copy and paste

copy and paste the formula and function

the formula and function here because it's using some cell references.

here because it's using some cell

Which cell references?

references which cell references it's

It's actually using the same cell

actually using the same cell where I

where I want to paste it to,

want to paste it to that's gonna be a

that's going to be a problem.

problem and so what I'm gonna end up

And so what I'm going to end up doing is

doing is copying this and then pasting

copying this and then pasting only the values over here

only the values over here in this social

in the social security number column.

security number column make sense all

Makes sense?

right here we go

All right, here we go.

all right so I'm going to start by

All right, so I'm going to start by selecting the cell that I want to

selecting the cell that I that I want to

begin working on.

begin working on and I am gonna use

And I am going to use relative references

relative references because I want to be

because I want to be able to process this macro

able to process this macro in another

in another spot later on.

spot later on and I'm gonna click on

And I'm going to click on Record Macro

record macro and I'll call this

and I'll call this particular macro

particular macro

SocialLastFour.

social last for

And my shortcut key for this one

and my shortcut key for this one maybe

maybe is Control + Shift + S

is ctrl shift s and I'll click on ok and

and I'll click on Okay. And here we go.

here we go I'm doing it step by step

I'm doing it step-by-step, being really careful.

being really careful ok so I'm going to

Okay, so I'm going to the cell that is two cells right of that.

the cell that is two cells right of that

And I'm going to put in my formula here and my function.

and I'm gonna put in my formula here and

So I'm going to need to put in my asterisks, dash and another dash at the end,

my function so I'm gonna need to put in my asterisks - and another - at the end

following that with my quotation marks,

following that with my quotation marks

then I got my handy ampersand,

then I got my handy ampersand which is

which is followed by the characters

followed by the characters that are

that are going to be returned by the RIGHT function.

going to be returned by the write function

And it's going to pull from my social security number cell,

and it's gonna pull from my social

and it's going to pull four characters there

security number so and it's gonna pull four characters there I'm gonna execute

and I'm going to execute that.

that

And once I've executed that,

and once I've executed that I'm going to

I'm going to auto-fill the rest of my column here.

autofill the rest of my column here

Okay, so we're good so far.

okay so we're good so far now what I got

Now what I got to do is copy

to do is copy

and paste these as the values, right?

and paste these as the values

And once I do that,

and once I do that you'll see that the

you'll see that the values here that show up

values here that show up in the formula

in the formula bar is no longer the formula or the function.

bar is no longer the formula or the

It's just those strict values that were returned

function it's just those strict values

by this particular process.

that were returned by this particular

Now I do still have the flashing marquee

process now I do still have the flashing

surrounding the selection,

marquee surrounding the selection so I

so I can also paste it in another location.

can also paste it in another location so

So I'm going to have it pasted here as well

I'm gonna have it paste it here as well

in the social security number column.

in the social security number column and

And I'm just going to use my Enter key to do that,

I'm just gonna use my Enter key to do

because what it's going to do is paste that

that cuz what it's gonna do is paste

and remove the original selection from the clipboard,

that and remove the original selection

which is really nice.

from the clipboard which is really nice

So I got my values showing up over there.

so I got my values showing up over there

And now what I want to do is delete it

and now what I want to do is delete it

from this column on the right-hand side.

from this column on the right hand side

So all I'm going to do is use Control + Shift + Down

so all I'm gonna do is use ctrl shift

to select all that content.

down to select all that content and I'm

And I'm hitting my Delete key.

hitting my Delete key and that's gonna

And that's going to remove it.

remove it now that I'm done I'm just

Now that I'm done, I'm just going to select the cell that I started on here.

gonna select the cell that I started on

And I'm done recording that particular macro.

here and I'm done recording that

So here I go,

particular macro so here I go Developer

Developer tab, I'm going to Stop Recording

tab I'm gonna stop recording and I can

and I can test it out on the table on the right-hand side.

test it out on the table on the

So here I have my social security numbers

right-hand side so here I have my social

and I'm going to use Control + Shift + S,

security numbers and I'm gonna use ctrl

which is my shortcut key.

shift s which is my shortcut key and

And just like that,

just like that I get all these converted

I get all of these converted that quickly.

that quickly now as handy as this is it

Now, as handy as this is, it did require a few steps.

did require a few steps so I did have to

So I did have to write it down.

write it down so that was part of the

So that was part of the practice here,

practice here is making sure to write

is making sure to write down all the steps.

down all the steps why don't you go

Why don't you go ahead and do that?

ahead and do that take a moment to pause

Take a moment to pause the video,

the video write down the few steps that

write down the few steps that we want to do here.

we want to do here after you write it

After you write it down, make sure that you have recorded all three of these macros.

down make sure that you have recorded all three of these macros we're gonna

We're going to make sure

make sure that we've recorded the simple

that we've recorded the simple ones that we started off with.

ones that we started off with and then

And then the more complex one that we did

the more complex one that we did for the

for the third example,

third example which had a few different

which had a few different steps there.

steps there

Okay, go ahead and do that

go ahead and do that and come back and

and come back and then we'll continue

then we'll continue with even more

with even more interesting and useful macros.

interesting and useful macros

Welcome back.

welcome back so in the last few examples

So in the last few examples, we did get to do a couple of actions.

we did get to do a couple of actions

They were simple, they were formulaic, or they are formatting options.

they were simple they were formulaic or

This time,

they are formatting options this time

we're going to use some of the bigger tools in Excel

we're gonna use some of the bigger tools

and see that a lot of these bigger tools

in Excel and see that a lot of these

actually work just as well with macros.

bigger tools actually work just as well with macros okay so we what we're going

Okay. So what we're going to be doing

to be doing is creating a few sort

is creating a few sort macros and a few filter macros.

macros and a few filter macros now when

Now, when we're actually recording this

we're actually recording this on the

on the Developer tab,

Developer tab you're gonna want to leave

you're going to want to leave this

this use relative reference button on

Use Relative Reference button unchosen. And the reason is that our sort and filter macros

Chozen and the reason is that our sort

are going to work best using absolute references.

and filter macros are going to work best using absolute references so that's what

So that's what I'm going to do.

I'm gonna do and then I'm gonna click on

And then I'm going to click on Record Macro,

record macro and here I go I'm gonna

and here I go. I'm going to make my first sort macro.

make my first sort macro this is gonna

This is going to sort by the department

sort by the Department and then by the

and then by the last name and I'll click Okay.

last name and I'll click OK and then I'm

And then I'm going to go to the Data tab

gonna go to the data tab where I find my

where I find my Sort Tool

sort tool and I'm gonna add my two

and I'm going to add my two levels. The first one is by department

levels the first one is by Department

and the second one is by last name and I'll click Okay. And there it is.

and the second one is by last name and I'll click OK and there it is as easy as

As easy as that I'm done with my macro,

that I'm done with my macro go to my

go to my Developer tab and Stop Recording.

Developer tab and stop recording and I'm

And I'm going to make one more,

gonna make one more then I go to the

then I go to the Developer tab, click Start Recording Macro.

Developer tab click start recording

And this time I am going to make a sort macro

macro and this time I am going to make a

that has to do with sorting by the status

sort macro that has to do with sorting

and then the first name.

by this status and then the first name

Go into my Data tab,

go into my data tab choosing my sort

choosing my sort button.

button this time I'm gonna sort by

This time, I'm going to sort by status first and then by first name and I'll click Okay.

status first and then by first name and I'll click OK now I'm done go to the

Now I'm done, go to the Developer tab, click Stop Recording.

Developer tab click stop recording

Here's the really cool part.

here's the really cool part if we have

If we have coworkers who are not able to sort and filter,

co-workers who are not able to sort or

maybe they don't know how to use those tools.

an and filter maybe they don't know how to use those tools anything that's a

Anything that's a macro,

macro I can actually turn into a button

I can actually turn into a button on the interface

on the interface and they can just click

and they can just click on that button

on that button and it will do what they

and it will do what they want to do.

want to do so if I go to the insert tab

So if I go to the Insert tab,

so I'm on the Developer tab of the

so on the Developer tab of the ribbon,

ribbon

and I'm going to the Insert button and in the Insert Menu,

and I'm going to the insert button and in the insert menu you'll see a

you'll see a bunch of fantastic form controls here.

bunch of fantastic form controls here we

We will go over a few more of these

will go over a few more of these

throughout the course of the class,

throughout the course of the class but

but I'm going to start with talking about

I'm gonna start with talking about the

the Button macro, it's on the top left.

button macro it's on the top left I'm

I'm going to choose that.

gonna choose that and when I bring my

And when I bring my cursor back into the interface,

cursor back under the interface it looks

it looks like I'm about to draw a square.

like I'm about to draw a square and

And that's kind of what it looks like when I click and drag

that's kind of what it looks like when I

to actually create this thing.

click and drag to actually create this thing

And this is going to be my sort,

and this is going to be my sort this

this is going to be my sort by department,

going to be my sort by department and

and then last and I'll click Okay.

then last and I'll click OK and I can

And I can right click on this to edit the text

right click on this to edit the text and

and I can actually change the text to say

I can actually change the text to say

Sort Dept and Last,

sort Department and last

and then maybe I'll put it in another one for my second sort

and then maybe I'll put it in another

and my second source is the sort by status and then first,

one for my second sore to my second sort is the sort by status and then first and

and I'll click Okay again.

I'll click ok again

Editing my text here,

editing my text here

Sort Status, and then First Name.

sort status and then first name

And now my end users,

and now my end users folks who might not

folks who might not already know how to use sorting,

already know how to use sorting they can

they can just click on these and have it sort

just click on these and have it sort

exactly like they wanted to.

exactly like they wanted to

The same thing works for filtering.

the same thing works for filtering so

So I'm going to make a couple of filtering macros.

I'm gonna make a couple of filtering

I'm going to click on Record Macro,

macros I'm gonna click on a record macro

and this one is going to be a filter

and this one is going to be a filter for

for my operations department.

my operations department and I want to

And I want to see all the people that work full time

see all the people that work full-time

in the operations department.

in the operations department

Okay. So I'm clicking on Okay.

okay so I'm clicking on okay

I did start my recording here,

I did start my recording here going to

going to the Data tab,

the data tab my filters already on so

my filter's already on.

I'm going to go to the department and

So I'm going to go to the department and say, okay, I want to find operations.

say ok I want to find operations

There it is.

and choosing just the simple operations

I'm choosing just a simple operations and I'll click Okay.

and I'll click OK

And then I also want to find people that are full time.

and then I also want to find people that

So I'm going to choose, select all to unselect all,

are full-time so I'm gonna choose select

and I'm choosing full time, full time.

all to unselect all and I'm choosing

There are a couple of different people.

full-time full-time there are a couple

There's a dash in a few of them and I'll click Okay.

different people there's a - and a few of them and I'll click OK and these are

And these are all the people in my operations department

all the people in my operations

that worked full time

department that work full-time and I'll

and I'll go to the Developer and I'll stop recording.

go to the developer and I'll stop

So that's one.

recording so that's one let's say I need

Let's say I need to make another one for my coworker.

to make another one for my coworker

Alright, so I'm going to record another one.

all right so I'm gonna record another

And this one is going to filter

one and this one is going to filter

folks who work between 35

folks who work between 35 to 40 hours

to 40 hours. And I'll click Okay.

and I'll click OK and over here in the

And over here in the hours worked column,

hoursworked column I can now apply a

I can now apply a number filter,

number filter these number filters work

these number filters work just as well. And I'll say between 35

just as well and I'll say between 35 and

and 40

forty

click Okay.

click okay and then I'm done so I'm

And then I'm done. So I'm going to stop recording

gonna stop recording and then maybe I'll

and then maybe I'll make one more for them,

make one more for them that's just gonna

that's just going to clear the filter.

clear the filter

ClearMyFilter. I'll click Okay.

clear my filter I'll click okay

And now I'm going to go back to the Data tab

and now I'm gonna go back to the data

and I'm just going to click on this Clear button.

tab and I'm just gonna click on this

That's it, going back to the Developer, Stop Recording.

Clear button that's it going back to the developer stop recording and now of

And now of course, just like before

course just like before I can create

I can create these nice-looking buttons here,

these nice-looking buttons here

and this one is going to filter for

operations full time and I'll click Okay.

operations full-time and I'll click OK you

Maybe I'll make that a little bit wider.

maybe I'll make that a little bit wider

Okay. I'll put another button here.

okay I'll put another button here

That's to filter 35 to 40 hours and I'll click Okay.

35 to 40 hours and I'll click okay

And then I'm just going to put the nice one in there

and then I'm just gonna put the nice one

that's going to clear everything,

in there that's gonna clear everything

and there I'm done.

and there I'm done now my co-workers

Now, my coworkers should be able to filter

should be able to filter by operations

by operations in full time and then clear it

in full time and then clear it and see

and see filtered 35 to 40 hours.

if they'll turn thirty five to forty hours and so they can see them both at

And they can see them both at the same time

the same time if they filter one of them

if they filter one of them

and then filter the next one as well

and then filter the next one as well, okay.

okay so why don't you go ahead and try

So why don't you go ahead and try that. Pause the video,

that pause the video record at least one

record at least one sort macro and at least one filter macro.

sort macro and at least one filter macro

Make sure to not use relative references.

make sure to not use relative references

And once you have those recorded and test them out,

and once you have those recorded and test them out then come back and we will

then come back and we will talk about some even more interesting macros that we can record.

talk about some even more interesting macros that we can record

Welcome back.

welcome back one of the other fantastic

One of the other fantastic tools

tools that works really well with the

that works really well with the macro recorder

macro recorder is cell and sheet

is cell and sheet protection.

protection now I'm actually going to use

Now I'm actually going to use a special pathway

a special pathway through which to

through which to select cells, to lock and not to lock.

select cells to lock and not to lock

Normally you could click on any cell

normally you could click on any cell

that you wanted your end users to be able to modify

that you wanted your end-users to be

after you protected your sheet.

able to modify after you protected your

And you could go to the Format tab and say,

sheet and you could go to the format tab

I want to unlock those.

and say I want to unlock those that's

That's how we would create cells that an end user

how we would create cells that an end

actually could modify.

user actually could modify however I'm

However, I'm not actually going to select these cells.

not actually going to select these cells

Because once I select these cells,

because once I select these cells that's

that's what actually gets recorded.

what actually gets recorded and I can't

And I can't use this on any differently built spreadsheets.

use this on any differently built

So instead I'm going to use the

spreadsheet so instead I'm gonna use the

Find and Select tool in Excel.

find and select tool in Excel that's on

That's on the Home tab.

the Home tab alright let's go ahead and

All right, let's go ahead and do this.

do this going to the Developer tab

Go into the Developer tab, clicking on Record Macro

clicking on record macro and I am gonna

and I am going to record this macro.

record this macro I'm gonna call it

I'm going to call it InputCellFormat

input cell format

and then Protect,

and then protect

then I'll click Okay.

and I'll click OK

And now I'm going to go to the Home tab of the ribbon

and now I'm gonna go to the Home tab of

and all the way on the right-hand side,

the ribbon and all the way on the

I'm going to find the option that says,

right-hand side I'm gonna find the

Find and Select.

option that says find and select I'm

I'm going to choose, Go To Special.

gonna choose go to special

And in here I'm going to choose Constants,

and in here I'm going to choose constants particularly constants that

particularly constants that are numerical values.

are numerical values so I'm going to

So I'm going to ignore Text, Logicals and Errors.

ignore texts logicals and errors just

Just the number of values that are constant,

the number values that are constants and

and I'm going to click Okay.

I'm going to click OK these tend to be

These tend to be input values,

input values which tend to be the kind

which tend to be the kind of thing that my end user can modify.

of thing that my end user can modify so

So I'm going to go ahead and unlock these,

I'm going to go ahead and unlock these

going to my Format tab.

going to my format tab I'm going to

I'm going to unlock these guys.

unlock these guys I'm also going to

I'm also going to change the colors ever so slightly

change the colors ever so slightly so

so that my end users will know that

that my end users will know that they're

they're supposed to be able to modify these cells.

supposed to be able to modify these

And then I'm going to set up my sheet with the protection

cells and then I'm going to set up my

that's going to protect every other cell in the worksheet

sheet with the protection that's gonna protect every other cell in the

by clicking on Protect Sheet.

worksheet by clicking on protect sheet

I'm not going to apply a password here.

I'm not going to apply a password here

I'm just going to click Okay.

I'm just gonna click OK and I'm done go

And I'm done. Go into the Developer tab and Stop Recording.

into the Developer tab and stop recording and if we want to test this

And if we want to test this and see if it works,

and see if it works we can go to a

we can go to a completely different-looking sheet here,

completely different looking sheet here

which very much looks completely different.

which very much looks completely

And it should still work here

different and it should still work here

because of the pathways that we used.

because of the pathways that we used so

So I'll go to my Macros button up here,

I'll go to my macros button up here I'll

I'll find my macro, which has to do with InputCellFormatProtect

find my macro which has to do with input cell format protect and I'll click on a

and I'll click on Run. And there we go.

run and there we go and if it's worked

And if it's worked perfectly,

perfectly I should be able to click on

I should be able to click on any one of these cells

any one of these cells and not be able

and not be able to enter any information.

to enter any information so there I go

So there I go, clicking,

clicking and indeed I do get that error

and indeed I do get that error message, but I should be able to go up to cell B3.

message but I should be able to go up to

This should be a valid input cell,

cell b3 this should be a valid input

and I should be able to change this to another value. And there we go.

cell and I should be able to change this to another value and there we go I was able

I was able to do that.

to do that it did modify all of the

It did modify all of the formulas in my spreadsheet

formulas in my spreadsheet and there we

and there we are.

are if you haven't done so already take

If you haven't done so already, take the time now to pause the video

the time now to pause the video and try

and try and create this macro for yourself as well.

and create this macro for yourself as

You'll go to the first sheet here.

well you'll go to the first sheet here

You will start recording your macro.

you will start recording your macro

You'll go to the Home tab,

you'll go to the Home tab use your find

use your Find and Select tool and Go To Special,

and select tool and go to special to

to find only Constants, only numerical constants.

find only constants only numerical

You'll click Okay, format those, unlock them.

constants you'll click OK format those unlock them and then you will protect

And then you will protect your sheet.

your sheet

After you've done that,

after you've done that make sure to

make sure to click Stop Recording,

click stop recording and then go to the

and then go to the next sheet in line,

next sheet in line which is called test

which is called Test and run your macro on this sheet

and run your macro on this sheet and see

and see if it works.

if it works when you're done come on

When you're done, come on back and we will move on to even more complicated macros.

back and we will move on to even more complicated macros

Welcome back.

welcome back for the rest of the course

For the rest of the course, we're going to start actually writing some VBA scripting.

we're going to start actually writing some VBA scripting in order to make any

In order to make any edits,

edits we're going to be spending quite a

we're going to be spending quite a bit of time

bit of time in the VBA interface so

in the VBA interface. So let's go ahead and get used to it now.

let's go ahead and get used to it now

On the right-hand side of my screen,

on the right hand side of my screen I do

I do have the Visual Basic for Applications window open.

have the Visual Basic for applications window open now

Now most of it is gray,

most of it is grey but on the left hand

but on the left-hand side I do have

side I do have what's referred to as the

what's referred to as the Project Explorer over here.

project Explorer over here and I do also

And I do also have the Properties window

have the properties window that I can

that I can see over here as well. Now I do expect to see an area over here

see over here as well now I do expect to

where I actually have some code,

see an area over here where actually

however, that's not present

have some code however that's not present because I

because I don't have any code in here.

don't have any code in here I don't have

I don't have any code in here

any code in here cuz I don't have any

because I don't have any macros in here.

macros in here

On the left-hand side

on the left hand side in my project

in my project Explorer window,

Explorer window normally if I actually

normally, if I actually had some macros in here,

had some Macker in here you would see a

you would see a folder that said Modules

folder that said modules and that folder

and that folder called Modules

called modules would contain all of the

would contain all of the different macros

different Macker that I have for this

that I have for this particular file.

particular file so in order to get a

So in order to get a module in here,

module in here I'm gonna go to the

I'm going to go to the Insert Dropdown menu in the tool bar.

insert drop-down menu in the toolbar I'm

I'm going to choose Module.

gonna choose module and that inserts

And that inserts this module for me.

this module for me it also inserts a

It also inserts a Modules folder

modules folder and I instantly have

and I instantly have Module1

module 1 and I could go ahead and type

and I could go ahead and

in whatever code I want to now of course

type in whatever code I want to. Now of course the code actually has to make sense,

the code actually has to make sense so

so what I just typed would be not particularly helpful,

what I just typed would be not particularly helpful but I do have my

but I do have my code window up here on the right-hand side.

code window up here on the right hand

So that's exactly what I wanted.

side so that's exactly what I wanted

There are a couple other features

there are a couple other features that I

that I do want us to be able to see

do want us to be able to see before we

before we move on and start doing some scripting.

move on and start doing some scripting I

I want us to be able to see the immediate window.

want us to be able to see the immediate

Now, I can't see it right now

window now I can't see it right now so

so I'm going to go to my View dropdown menu,

I'm gonna go to my view drop down menu

and I'm going to choose my immediate window.

and I'm going to choose my immediate window

My immediate window is this area

my immediate window is this area at the

at the bottom of the screen.

bottom of the screen now normally there

Now, normally there is no script in there whatsoever,

is no script in there whatsoever

but I do have my immediate window down here.

but I do have my immediate window down

And this immediate window is amazing.

here and this immediate window is

It allows us to be able to run little bits of code

amazing it allows us to be able to run

and also ask questions of our workbook.

little bits of code and also ask questions of our workbook so let's say

So let's say for instance,

for instance I want to ask a question

I want to ask a question like, how many worksheets do I have in my workbook?

like how many worksheets do I have in my

If I'm going to ask a question,

workbook if I'm going to ask a question

I'm always going to start with my question mark,

I'm always going to start with my

and then I can type something like Worksheets.Count

question mark and then I can type something like worksheets count

and we'll be able to explore this a little bit later

and we'll be able to explore this a

and see why exactly that little bit of code I just typed in

little bit later and see why exactly that little bit of code I just typed in

makes sense.

make sense

But I can ask a question like this and just hit Enter.

but I could ask a question like this and

And then I get that answer.

just hit enter and then I get that

I can also ask a question like,

answer I can also ask a question like

hey, what value is in range cell B2?

hey what value is in range cell b2

So what value is in B2?

so what value is in b2

And it will tell me exactly which text

and it will tell me exactly which text

is in that particular cell, right?

is in that particular cell right I can

I can also choose to execute little bits of code here.

also choose to execute little bits of

People do this for different reasons.

code here people do this for different

Maybe they just want to make a quick edit,

reasons maybe they just want to make a

or maybe they want to be able to test out a line of code

quick edit or maybe they want to be able to test out a line of code and actually

and actually see if it's going to work.

see if it's going to work this is a

This is a great place to test out your code.

great place to test out your code so

So maybe I would say something like,

maybe I would say something like hey

hey, that cell that I have selected on right now,

that cell that I have selected on right now

when it comes to horizontal alignment,

when it comes to horizontal alignment

casing doesn't really matter here,

kasing doesn't really matter here I want

I want it to be centered.

it to be centered so I'll say XL Center

So I'll say xlCenterAcross

across

the selection.

selection and when I hit enter it

And when I hit Enter, it actually executes that bit of code.

actually executes that bit of code and

And now it is centered there in cell B2.

now it is centered there in cell b2 if I

If I changed my mind and I wanted this to be left alignment,

change my mind and I wanted this to be

I could say xlLeft

left alignment I could say Excel left

and execute that.

and execute that and there we go that is

And there we go, that is left aligned now.

left aligned now the last thing we want

The last thing we want to understand

to understand about our interface or be

about our interface, or be able to see about our interface,

able to see about our interface our VBA

our VBA interface that is,

interface that is before you move on and

before we move on and start doing

start doing the coding for the rest of

the coding for the rest of the class,

the class is we want to know where our

is we want to know where our Object Browser is.

object browser is and we want to know

And we want to know what it is.

what it is this is another thing that I

This is another thing that I want to be able to see.

want to be able to see so I'm gonna go

So I'm going to go to the View dropdown menu

to the view drop-down menu and choose my

and choose my Object Browser.

object browser and I'll see this extra

And I'll see this extra window

window that pops up here on the right

that pops up here on the right-hand side. Now this is a library of all of the objects

hand side now this is a library of all of the objects and collections and

and collections and properties and methods

properties and methods and all the cool

and all the cool kinds of stuff

kinds of stuff that you can do with VBA

that you can do with VBA. Now, when we're first starting out,

now when we're first starting out we're

we're just going to learn a few handy ones

just gonna learn a few handy ones that

that are good to know as we're getting started with VBA.

are good to know as we're getting

However, it is very nice to know that this library's in here

started with VBA however it is very nice to know that this libraries in here so I

so I can consult this library.

can consult this library

Okay. So go ahead and pause the video

so go ahead and pause the video and make

and make sure that your interface, your VBA interface

sure that your interface your VBA

looks like mine.

interface looks like mine that is we

That is we should have a Project Explorer

should have a project Explorer on the

on the upper left-hand side.

upper left-hand side in that project

In that Project Explorer,

Explorer we should have a folder that

we should have a folder that says Modules, right?

says modules right and I got that folder

And I got that folder that said Modules

that said modules because I actually

because I actually inserted a module.

inserted a module so I went to my insert

So I went to my Insert dropdown menu

drop-down menu and I inserted a module

and I inserted a module right there.

right there and that's what gave me that

And that's what gave me that folder here, right there.

folder here

And when I got that folder here and I got Module1,

right there and when I got that folder here and I got module 1 I also got this

I also got this code window.

code window so we do want to make sure

So we do want to make sure that we have

that we have this code window up here we

this code window up here. We should also have a Properties window down here,

should also have a properties window

that usually comes standard.

down here that usually comes standard

The other thing that we do want to see

the other thing that we do want to see

is our immediate window,

is our immediate window which is on the

which is on the bottom of our screen.

bottom of our screen now in order for me

Now, in order for me to see that I did have to go to the View dropdown menu

to see that I did have to go to the view drop down menu

and choose Immediate window.

and choose a mediate window now a second

Now a second ago, I did also show us the Object Browser.

ago I did also show us the object

We're not going to be using the Object Browser right now,

browser we're not going to be using the

but I did want us to know exactly where to find it.

object browser right now but I did want us to know exactly where to find it so

So just go ahead and make sure that you do see

just go ahead and make sure that you do

your Immediate window though, at the bottom of your screen.

see your immediate window though at the

And you have a Code window up here

bottom of your screen and you have a code window up here on the upper

on the upper right-hand side.

right-hand side go ahead and pause your

Go ahead and pause your video

video and make sure that you have it and

and make sure that you have it and then come back.

then come back and we're actually going

And we're actually going to do some scripting.

to do some scripting

For this next section, we're going to be using a file called Font Styles.

section we're going to be using a file called font styles and I have that file

And I have that file open on my screen up here

open on my screen up here called font

called Font Styles.

styles now is a good time to pause the

Now is a good time to pause the video and grab the file called Font Styles.

video and grab the file called font

Then open up VBA on the right-hand side of your screen

styles then open up VBA on the right-hand side of your screen so that

so that you can see them both side by side.

you can see them both side by side and

And once you have VBA open,

once you have VBA open go ahead and

go ahead and unpause the video and we'll continue.

unpause the video and we'll continue

When we get a little bit more experienced

when we get a little bit more

in creating macros,

experienced in creating macros we'll

we'll find ourselves creating them from scratch.

find ourselves creating them from

We're going to be doing that in just a minute,

scratch we're going to be doing that in

but before we do,

just a minute but before we do let's see

let's see how the recorder actually builds them.

how the recorder actually builds them if

If we have our Excel window and VBA window side by side,

we have our Excel window and VBA window

we can watch the macro creation as the recorder works.

side-by-side we can watch the macro creation as the recorder works so let's

So let's go ahead and record this macro here.

go ahead and record this macro here what

What we're gonna be doing

we're going to be doing is we're going

is we're going to be changing the font

to be changing the font of this

of this particular spreadsheet here.

particular spreadsheet here so let's say

So let's say, for instance,

for instance one of the departments that

one of the departments that gets this financial report

gets this financial report needs to be

needs to be able to, or prefers to see it in a particular font styling

able to or prefers to see it in a

such as Times New Roman or Verdana

particular font styling such as Times New Roman or verdana or Calibri or

or Calibri or something like that.

something like that and so every time I

And so every time I create this report,

create this report I have the issue of

I have the issue of needing to go here

needing to go here and use my font drop

and use my Font dropdown menu

down menu and go down and find Times New

and go down and find Times New Roman.

Roman and maybe that takes me 10 seconds

And maybe that takes me 10 seconds or so,

or so but it would be a whole lot more

but it would be a whole lot more handy if I had a macro

handy if I had a macro that would

that would automatically just change

automatically just change the formatting

the formatting of the entire document here.

of the entire document here and that's

And that's what we're going to be doing.

what we're going to be doing and as

And as we're doing this,

we're doing this make sure to keep an

make sure to keep an eye on the right-hand side,

eye on the right-hand side where we are

where we are going to be able to see certain changes

going to be able to see certain changes

being made by the macro recorder.

being made by the macro recorder all

All right, let's go ahead and get started.

right let's go ahead and get started

We're going to the Developer tab.

we're going to the Developer tab and on

And on the left-hand side,

the left hand side you're gonna find

you're going to find your Code command group up at the top.

your code command group up at the top

You will see a little button that says Record Macro. Now, depending on how large you have your window open,

you will see a little button that says record macro now depending on how large you have your window open it might look

it might look like a small icon like this,

like a small icon like this

or it may look like a larger icon like this

or it may look like a larger icon like

that actually says Record Macro.

this that actually says record macro it

It'll all depend on how big you have your Excel window open.

all depends on how big you have your

Mine is small, like this.

Excel window open mine is small like

I'm going to go ahead and click on Record Macro here,

this I'm gonna go ahead and click on record macro here and we'll call this

and we'll call this one, let's call this one TimesNewRoman,

one let's call this one times new roman'

no spaces, has to start with a letter

no spaces has to start with a letter and

and I could assign a shortcut key.

I could assign a shortcut key

Maybe I'll assign the shortcut key Control + Shift + T

maybe I'll assign the shortcut key ctrl

and I'm holding down shift + T, so capital T.

shift T and I'm holding down shift and T

And that does make my shortcut key

so capital T and that does make my

Control + Shift + T.

shortcut key ctrl shift T I don't have

I don't have the option to

the option to not have control in the

not have Control in the shortcut key.

shortcut key and I am gonna store it in

And I am going to store it in this particular workbook

this particular workbook and I'm gonna

and I'm going to click Okay. And here we go.

click OK and here we go the first thing

The first thing I'm going to be doing is

I'm gonna be doing is clicking on this

clicking on this area in between my row headers

area in between my row headers and my

and my column headers on the top left.

column headers on the top left this

This selects everything in my worksheet.

selects everything in my worksheet and

And then I'm going to go to the Home tab

then I'm gonna go to the Home tab and

and I'm going to choose

I'm gonna choose to change all of the

to change all of the font here to Times New Roman

font here to Times New Roman and I'll

and I'll scroll all the way down into the Ts

scroll all the way down and to the t's

and find Times New Roman.

and find Times New Roman and there we go

And there we go. And I'll click on my Developer tab and hit the Stop button.

and I'll click on my Developer tab and hit the stop button and that's created

And that's created this particular macro here.

this particular macro here now on the

Now on the right-hand side,

right hand side you'll see that this new

you'll see that this new folder was created called Modules.

folder was created called modules I

I didn't have any macros in here ahead of time,

didn't have any macros in here ahead of

so I didn't have this folder a few seconds ago,

time so I didn't have this folder a few

but the moment I started to record a macro,

seconds ago but the moment I started to

I did get this folder

record a macro I did get this folder and

and now I can open up this folder

now I can open up this folder and take a

and take a look in the module

look in the module that has actually

that has actually been created here called Module1.

been created here called module one and

And if I double click on that,

if I double click on that I will see the

I will see the code window that was created here for me.

code window that was created here for me

So I do have this macro called TimesNewRoman,

so I do have this macro called times new

and this is what the macro recorder recorded.

roman' and this is what the macro recorder recorded now let's say for

Now, let's say for instance, that I want to also be able to send this

instance that I want to also be able to

to a different department

send this to a different department and

and that other department, they really prefer

that other department they really prefer

the font style Verdana.

the font style for Donna in this case

In this case, I'm going to record a macro.

I'm gonna record a macro so I'm gonna

So I'm going to click on record

click on record and I'm gonna call this

and I'm going to call this one Verdana and I'm going to click Okay.

one or Dona and I'm gonna click ok and

And here we go, I'm selecting all my text first,

here we go I'm selecting all my text first

going to the Home tab,

going to the Home tab finding my font

finding my Font Styles, and I'm going to go and find Verdana here.

styles and I'm gonna go and find verdana here

Okay, notice when I do that,

okay notice when I do that did you see

did you see what happened over here in my code window?

what happened over here in my code

A bunch of code got recorded by the macro recorder here.

window a bunch of code got recorded by the macro recorder here and so far that

And so far that code says,

code says this is the name of my macro

this is the name of my macro, and this is some comments about it.

and this is some comments about it it's

It's called the Verdana macro.

called deferred auto macro that's the

That's the comment, very simple.

comment very simple and then it says all

And then it says all of my cells select,

of my cells select and we'll get into

and we'll get into this a little bit more,

this a little bit more but then I have

but then I have this big old chunk of code

this big old chunk of code that happened

that happened here and I'm done with my macro.

here and I'm done with my macro so I'm

So I'm going to the Developer tab

going to the Developer tab and I'm going

and I'm going to hit the Stop key.

to hit the stop key

So if I want to switch this back to Times New Roman,

so if I want to switch this back to

I can do Control + Shift + T,

Times New Roman I can do control shift T

that is the shortcut that I assigned to this

that is the shortcut that I assigned to

that it changes all of the text there

this that it changes all of the text

to my TimesNewRoman macro.

there to my Times New Roman macro but

But let's go ahead and take a look

let's go ahead and take a look at what

at what the macro recorder recorded.

the macro recorder recorded so we have

So we have our first macro up here.

our first macro up here it's everything

It's everything from the word Sub down through the end,

from the word sub down through the end

where it says, End Sub.

where it says and sub now you find that

Now you'll find that all of your macros

all of your macros are going to have

are going to have this kind of a structure.

this kind of a structure they're gonna

They're going to need a beginning statement

need a beginning statement and an ending

and an ending statement.

statement I like to refer to these as

I like to refer to these as bookends.

bookends they help hold everything

They help hold everything inside together.

inside together

Okay, so I do have at the beginning Sub and End Sub,

okay so I do have at the beginning sub

and I'll see that again in my other macros here,

and end sub and I'll see that again in my other macros here I see the next one

I see the next one, the Vedanta macro says Sub and End Sub right there.

the verdona macro says sub and end sub right there okay so that's always on the

Okay, so that's always on the outside. Some other patterns I want to make note of

outside some other patterns I want to

are the fact that all of this text is green.

make note of are the fact that all of this text is green and at the beginning

And at the beginning of these lines

of these lines are these little

are these little apostrophes. That apostrophe is used

apostrophes that apostrophe is used to

to comment out certain lines of stuff.

comment out certain lines of stuff so if

So if I wanted to make a note to myself for later on,

I wanted to make a note to myself for

maybe I want to say,

later on maybe I want to say

this is for the development department,

this is for the development department

maybe they prefer that particular look,

for that particular look and I'm gonna

and I'm going to hit my Enter key,

hit my Enter key and then notice that

and then notice that that shows up as green.

that shows up as green so the nice part

So the nice part is that this

is that this isn't actually gonna be run

isn't actually going to be run as code. This is just a note for me, right?

as code this is just a note for me right

This is called commenting out,

this is called commenting out and I'll

and I'll be referring to this throughout the course.

be referring to this throughout the

And indeed other macro coders

course and indeed other macro coders are

are going to be using this term as well,

going to be using this term as well

commenting out.

commenting out now commenting out is

Now commenting out is also used

also used for other strategies as well

for other strategies as well.

for instance when I start to look at my

For instance, when I start to look at my code here

code here that the macro recorder

that the macro recorder created,

created there's actually a ton of stuff

there's actually a ton of stuff here that I don't need.

here that I don't need normally if I

Normally, if I were to create this macro on my own,

were to create this macro on my own it

it would be a whole lot more simple.

would be a whole lot more simple but the

But the macro recorder has a habit of recording

macro recorder has a habit of recording

a bunch of extra stuff that's not needed.

a bunch of extra stuff that's not needed

You'll see this line that says,

you'll see this line that says do you

do you want to put a strike through on the text?

want to put a strikethrough on the text

And it says false, which means no,

and it says false which means no no I

no, I don't want to strike through.

don't want to strikethrough no I don't

No, I don't want a superscript.

want a superscript no I don't want a

No, I don't want a subscript.

subscript no I don't want an outline on

No, I don't want an outline on the font. No, I don't want to shadow, right?

the font no I don't want a shadow right

So we're basically seeing all these,

so we're basically seeing all these I

I don't want this, I don't want that,

don't want this I don't want that I

I don't need this, I don't need that.

don't need this I don't need that this

This is all just extra stuff

is all just extra stuff that the macro

that the macro recorder throws in

recorder throws in because it doesn't

because it doesn't really know

really know whether or not you need it

whether or not you need it.

so it just throws it in there and says

So it just throws it in there and says, nope, you don't need it.

nope you don't need it

But it turns out that all of this stuff

but it turns out that all of this stuff

is actually completely unnecessary.

is actually completely unnecessary now

Now let's go ahead and take this concept

let's go ahead and take this concept

with what we've seen so far, where it says, Cells.Select

with what we've seen so far where it says cells dot select and then we're

And then we're going to take this little statement here that

gonna take this little statement here

says Selection.Font, name it, the name of that font,

that says selection font name it

it's Times New Roman.

the name of that font it's Times New Roman

And what we're going to do is

and what we're gonna do is we're gonna

we're going to condense this macro down

condense this macro down to only have

to only have that little piece of text,

that little piece of text because that

because that turns out is all I need.

turns out is all I need so all of this

So all of this other stuff in here,

other stuff in here the with statement

the With statement, all of these things down through End With,

all of these things down through end

I actually don't need that stuff.

with I actually don't need that stuff

And we'll talk about later today,

and we'll talk about later today why it

why it says With and End With.

says with and end with that's a

That's a particular coding structure

particular coding structure that you can

that you can create yourself.

create yourself it's handy for a couple

It's handy for a couple of different reasons,

of different reasons but it turns out

but it turns out for us, we don't actually need it.

for us we don't actually need it all we

All we need is the statement that says Selection.

need is the statement this says selection

I don't need any of this.

I don't need any of this

And all I needed to say is Selection.Font

and all I needed to say is selection dot font

and if I were to run this, it would run perfectly fine.

and if I were to run this it would run perfectly fine now let's go ahead and

Now let's go ahead and run our Verdana macro

run our of or Donna macro so we can

so we can switch our text back to Verdana

switch our text back to ver Donna so

so that then we can test our Times New Roman macro.

that then we can test our times new roman' macro I do have my for Donna

I do have my Verdana macro down here.

macro down here and if I place my cursor

And if I place my cursor at the beginning of this macro,

at the beginning of this macro I can run

I can run it directly from VBA.

it directly from VBA if you look up in

If you look up in the tool bar in VBA,

the toolbar in VBA you'll see a button

you'll see a button that looks like a play button.

that looks like a play button and the

And the screen tip that pops up for me says,

screen tip that pops up for me says run

Run The Sub, right? Sub is short for sub procedure,

the sub right sub is is short for sub

which we'll be talking about in a little bit,

procedure which we'll be talking about

but it says, run this up.

in a little bit but it says run the sub

Okay, I'm going to do that.

okay I'm gonna do that I'm gonna click

I'm going to click on my play button.

on my play button and it runs that

And it runs that entire macro,

entire macro which turn all of the text

which turned all of the text into Verdana.

into ver Donna

Now I do have my Times New Roman macro now,

now I do have my times new roman' macro

and I want to see if it still works.

now and I want to see if it still works

I've deleted a bunch of lines of code,

I've deleted a bunch of lines of code

and I just want to see if it still works.

and I just want to see if it still works

So I'm going to click in my TimesNewRoman macro,

so I'm gonna click in my times new

and I'm going to click on my play button and indeed there,

roman' macaron and I'm going to click on my play button and indeed there it

it actually worked.

actually worked so I didn't need all

So I didn't need all this extra stuff

this extra stuff that the macro recorder

that the macro recorder included.

included and you guys are gonna find

And you guys are going to find that across the board,

that across the board when the macro

when the macro recorder records stuff,

recorder records stuff about half of the

about half of the time, it records a bunch of stuff that we don't actually need.

time it records a bunch of stuff that we don't actually need so let's keep that

So let's keep that in mind.

in mind the reason why you might want to

The reason why you might want to remove this is just because

remove this is just because if you have

if you have a really, really very large macro,

a really really very large macro it can

it can be somewhat time consuming to run that macro

be somewhat time consuming to run that

and having little lines of extra code in here

macro and having little lines of extra

that are not necessary does make our macro longer to run.

code in here that are not necessary does make our macro longer to run you'll see

You'll see that I have one, two, three, four, five, six, seven, eight,

that I have 1 2 3 4 5 6 7 8 9 extra

nine extra lines of code and only one line of necessary code here, right?

lines of code and only one line of necessary code here right this is sort

This is sort of considered the same line.

of considered the same line

So this is going to make my macro,

so this is gonna make my macro having

having all this unnecessary stuff here is going to end up

all this unnecessary stuff here is gonna

making my macro 10 times longer to run

end up making my macro 10 times longer to run

than this one up here. So in some cases I do want to remove

than this one up here so in some cases I

the extra things that the macro recorder puts in there.

do want to remove the extra things that the macro recorder puts in there this is

This is one of those cases.

one of those cases if you haven't done

If you haven't done so already go ahead and do that.

so already go ahead and do that what

What we're doing is

we're doing is we are recording a couple

we are recording a couple of simple macros here.

of simple macros here we do have the

We do have the Excel file called Font Styles open.

excel file called font styles open and

And what we did is we recorded two macros,

what we did is we recorded two macros

one called TimesNewRoman, and one called Verdana.

one called times new roman' and one called verdona the times new roman'

The TimesNewRoman macro, what we did is we modified it

macro what we did is we modified it so

so that it only had these rows of information.

that it only had these rows of

And these were consolidated from this line and a combination of the second two lines here.

information and these were consolidated from this line and a combination of the second two lines here go ahead and do

Go ahead and do that,

that

consolidate your TimesNewRoman macro,

consolidate your times new roman' macro

so that it only has the lines of code

that you actually need.

that you actually need and when you're

And when you're done with that,

done with that come on back and we'll

come on back and we'll continue working with editing macros.

continue working with editing macros

Welcome back.

welcome back in this next section we're

In this next section, we're going to be modifying

going to be modifying some macros that

some macros that we've already created

we've already created or editing the

or editing the macros in the VBA editor.

macros in the VBA editor so let's say

So let's say we've created this macro called Verdana

we've created this macro called verdona

and it converts all the texts in our worksheet

and it converts all the text in our

to Verdana.

worksheet to verdona

However, it turns out that the department

however it turns out that the department

that we thought wanted Verdana,

that we thought wanted verdona

they actually now want Courier New.

they actually now want courier new so

So instead of having a Verdana macro,

instead of having a verdona macro I'm

I'm going to need a Courier New macro.

gonna need a courier new macro you can

You can go into the VBA interface

go into the VBA interface and directly

and directly make any edits that you want to

make any edits that you want to directly

directly in the code window.

in the code window for instance in this

For instance, in this line of code right here,

line of code right here where we have

where we have the font type populated,

the font type populated we can simply

we can simply replace the word Verdana with

replace the word for Donna with

Courier New.

we're new

And we'll see that this actually runs.

and we'll see that this actually runs if

If I click on the play button, there we go.

I click on the play button there we go

Now all of the text in my Excel file

now all of the text in my excel file is

is converted to Courier New.

converted to courier new of course I'll

Of course I'll want to clean this up a little bit.

want to clean this up a little bit and

And so the name of my macro, which is up here,

so the name of my macro which is up here

it's always after the word Sub.

it's always after the word sub-sub

Sub stands for Sub procedure.

stands for sub procedure and we'll get

And we'll get into that a little bit when we start

into that a little bit when we start

creating macros completely from scratch.

creating macros completely from scratch

But here I go,

but here I go I'm just modifying this

I'm just modifying this macro that already exists,

macro that already exists and I'm gonna

and I'm going to rename it.

rename it I'm gonna call it courier new

I'm going to call it Courier New.

now remember you can't have any spaces

Now, remember you can't have any spaces there.

there

And then down in here where I've put

and then down in here where I've put a

a little note in for myself that says,

little note in for myself that says oh

oh, this is a Verdana macro.

this is a verdona Macra I'm gonna say

I'm going to say this is a Courier New macro.

this is a courier new macro

And a little note for myself about

and a little note for myself about which

which department that's actually for.

department that's actually for

Now, just like before,

now just like before

the macro recorder still has a bunch of code in here

the macro recorder still has a bunch of

that's unnecessary.

code in here that's unnecessary so if

So if you haven't already done so

you haven't already done so with your

with your second macro here,

second macro here go ahead and make it

go ahead and make it look like the first.

look like the first

We're going to remove all the unnecessary code in here.

we're gonna remove all the unnecessary code in here now normally what somebody

Now, normally what somebody would do is

would do is they would go in here and

they would go in here and delete this,

delete this but we actually recommend

but we actually recommend that you don't do that.

that you don't do that what we recommend

What we recommend is that you move certain pieces of font around, such as this one that says

is that you move certain pieces of font around such as this one that says dot

.Name = "Courier New",

name equals courier new and you just

and you just move this around.

move this around so I'm going to move

So I'm going to move this up after the font,

this up after the font and then I'm

and then I'm going to,

gonna

and if you get messages like this, it's okay,

and if you get messages like this it's

because we're still in the process of working with this.

okay because we're still in the process of working with this but what I am gonna

But what I am going to do is I'm going to put my little apostrophe

do is I'm gonna put my little apostrophe

and I'm going to put the apostrophe

and I'm gonna put the apostrophe before

before every single line that I'm thinking about deleting

every single line that I'm thinking about deleting and what's handy about

and what's handy about this

this

is that it allows me to actually keep the code.

is that it allows me to actually keep

So when I test the code out in just one second,

the code so when I test the code out in just one second I'm gonna make sure that

I'm going to make sure that it actually works

it actually works without all of this

without all of this code in here, because the VBA program

code in here because the VBA program is

is not going to run

not going to run any of this commented

any of this commented out code here. It's going to skip it.

out code here it's gonna skip it and so

And so I'm just testing to make sure that my macro

I'm just testing to make sure that my

is actually going to work if I delete all this stuff.

macro is actually gonna work if I delete all this stuff and if I test it out and

And if I test it out and indeed it does work,

indeed it does work which apparently it

which apparently it did, I can run my TimesNewRoman again,

did I can run my times new roman' again

to see the change.

to see the change there's times new

There's TimesNewRoman,

roman'

then I'll run Courier New,

Noren career new and that worked as well

and that worked as well.

and now that I'm confident that it

And now that I'm confident that it actually worked

actually worked without these lines of

without these lines of code. Now I can actually go in here and delete these lines.

code now I can actually go in here and delete these lines now that I'm

Now that I'm confident that it didn't work.

confident that it didn't work sometimes

Sometimes we'll be modifying macros and we delete some code

we'll be modifying macros and we delete

and then we try and run it

some code and then we try and run it and

and we get into a bunch of issues

we get into a bunch of issues with it's

with it saying that it has a runtime error

saying that it has a runtime error or

or it's not working or some kind of a problem arises.

it's not working or some kind of a

And for that reason, we want to be careful

problem arises and for that reason we

to not completely delete the code

want to be careful to not completely

until we're absolutely confident that we don't need it.

delete the code until we're absolutely confident that we don't need it in fact

In fact, in the next example,

in the next example we're going to talk

we're going to talk about running into issues

about running into issues when we are

when we are modifying macros manually

modifying macros manually and we have

and we have some kind of a bug in the process.

some kind of a bug in the process that

That process will be called

process will be called the debugging

the debugging process.

process and that's next so if you

And that's next. So if you haven't done so already,

haven't done so already go ahead and

go ahead and modify your macros here

modify your macros here so that we don't

so that we don't have any of the

have any of the unneeded macro recorder

unneeded macro recorder code. And when you're done,

code and when you're done you can go

you can go ahead and save this file and close out of it

ahead and save this file and close out

because we're going to be opening another one

of it because we're going to be opening

in the next section.

another one in the next section

Welcome back.

welcome back for the next section we're

For the next section,

going to be using a file called

we're going to be using a file called Debugging. So take a moment to pause the video

debugging so take a moment to pause the

and make sure you have the file called Debugging open,

video and make sure you have the file called debugging open and it's helpful

and it's helpful to snap your windows left and right.

to snap your windows left and right so

So I'm going to take my Excel window and snap it left.

I'm gonna take my Excel window and snap

And then here in Windows 10,

it left and then here in Windows 10 now

now I can just click on the Visual Basic Window

I can just click on the visual basic

on the right-hand side.

window on the right hand side if you

If you don't already have visual basic open,

don't already have Visual Basic open of

of course you can click on your Visual Basic button in Excel

course you can click on your Visual Basic button in Excel and then open up

and then open up Visual Basic on the right-hand side

visual basic on the right hand side and

and make sure that you can actually see

make sure that you can actually see some

some of the code here.

of the code here you may need to click

You may need to click on modules and click on Module1

on modules and click on module one in

in order to be able to see the code here.

order to be able to see the code here go

Go ahead and pause to do that.

ahead and pause to do that and then come

And then come on back.

on back when we start scripting you're

When we start scripting, you're going to find that sometimes

going to find that sometimes it doesn't

it doesn't go quite right.

go quite right and in fact this is part

And in fact, this is part of the adventure of scripting.

of the adventure of scripting all of us

All of us do run into trouble

do run into trouble and macros don't go

and macros don't go right all the time

right all the time and when they don't

and when they don't go right, we need to fix them.

go right we need to fix them so it's

So it's going to be advantageous

gonna be advantageous to understand how

to understand how to use the helpful tools in VBA

to use the helpful tools in VBA to

to discover what may have gone wrong with a macro.

discover what may have gone wrong with a

This is called debugging.

macro this is called debugging we're

We're going to be using some tools in the VBA toolbar

going to be using some tools in the VBA

in order to debug our macro here.

toolbar in order to debug our macro here

Now, some of the tools

now some of the tools that I really like

that I really like to use for debugging

to use for debugging aren't actually in

aren't actually in the toolbar currently.

the toolbar currently you can customize

You can customize this toolbar very easily.

this toolbar very easily if you

If you right-click on some of the empty space

right-click on some of the empty space

on the right-hand side,

on the right hand side you can go down

you can go down to where it says Customize.

to where it says customize

And then you can choose to go to the left-hand side

and then you can choose to go to the

and see a bunch of things

left hand side and see a bunch of things

that are in each one of these categories here.

that are in each one of these categories

I'm going to find the debugging category,

here I'm gonna find the debugging

and I'm going to grab a couple of tools here

category and I'm gonna grab a couple of

that I like to use all the time.

tools here that I like to use all the

I'm going to find the tool that says Step Into, and I'm going to click and drag Step Into,

time I'm gonna find the tool that says step in - I'm gonna click and drag step

and I'm going to place it up here.

in - and I'm gonna place it up here

And I'm also going to grab Compile Project,

and I'm also gonna grab compile project

that's going to help me too.

that's gonna help me to there are a

There are a couple other tools in here that are nice to use.

couple other tools in here that are nice

A lot of people would like to use Toggle Breakpoint.

to use a lot of people like to use

You could grab that as well.

toggle breakpoint you could grab that as well that basically allows you to just

That basically allows you to just run a piece of your code

run a piece of your code instead of the

instead of the whole macro itself.

whole macro itself

And there's some other ones in here that are helpful.

and there's some other ones in here that

I'm just going to grab those two, three.

are hopeful I'm just gonna grab those

So we want Step Into, Compile Project,

two three so we want step in to compile

and Toggle Breakpoint.

project and toggle breakpoint and once

And once you've done that,

you've done that go ahead and click on

go ahead and click on Close and you should have those tools up in your toolbar in VBA.

close and you should have those tools up in your toolbar in VBA go ahead and

Go ahead and pause the video

pause the video and make sure that you

and make sure that you do have those.

do have those

Okay, so here we have an example of a macro

okay so here we have an example of a

that is about to go awry.

macro that is about to go awry on the

On the tab called End Product in Excel,

tab called end product in Excel we'll

we'll see what this is actually supposed to look like

see what this is actually supposed to

when this macro runs appropriately.

look like when this macro runs appropriately however as we're about to

However, as we're about to see in just a second,

see in just a second this macro is not

this macro is not going to run appropriately

going to run appropriately and we're

and we're going to need to follow the code

gonna need to follow the code and see

and see where it starts to run into problems.

where it starts to run into problems

You'll notice that I have extra tabs

you'll notice that I have extra tabs

down here at the bottom.

down here at the bottom I have this one

I have this one that says End Product

that says end product but I also have

but I also have these ones that say Backup,

these ones that say backup backup of the

Backup of the Backup

backup and then I have the original data

and then I have the original data over here.

over here a couple things to be aware of

A couple of things to be aware of

when we are creating and running macros

when we are creating and running macros for the very first time

for the very first time or when we are

or when we are debugging them,

debugging them things to be aware of are

things to be aware of are that macros are very hard to undo.

that macros are very hard to undo in

In fact, the program Excel itself

fact the program Excel itself will not

will not undo any action that is executed by a macro

undo any action that is executed by a

meaning anything that you,

macro meaning anything that you that a

that a macro does to your spreadsheet, you can't undo it. So that's one problem.

macro does to your spreadsheet you can't undo it so that's one problem now a lot

Now, a lot of coders like to input little pieces of code

of coders like to input little pieces of

that actually allow them to reverse those actions,

code that actually allow them to reverse

but that only works on a really simple scale.

those actions but that only works on a really simple scale and so it becomes

And so it becomes helpful for us,

helpful for us when we are starting to

when we are starting to create these macros

create these macros to always create not

to always create not just a backup, but a backup of the backup.

just a backup but a backup of the backup

We never want to run our macro on the original data,

we never want to run our macro on the

unless we are completely confident

original data unless we are completely

that it's going to work very well.

confident that it's going to work very

So here I go,

well so here I go I'm gonna go to the

I'm going to go to the backup of backup sheet

backup of backup sheet and I'm gonna run

and I'm going to run this macro.

this macro now remember it's supposed to

Now, remember it's supposed to look like

look like the sheet that says end

the sheet that says End Product here.

product here on my backup of the backup

On my backup of a backup, this is just a data dump.

this is just a data dump and I need to

And I need to create this report on a regular basis,

create this report on a regular basis

maybe five times a day or something like that.

maybe five times a day or something like that

I've created a macro, that's supposed to do it.

I've created a macro that's supposed to

So I'm going to go to my macros button

do it so I'm gonna go to my macros

and I'm going to find the macro called CreateReport

button and I'm gonna find the macro

and I'm going to click on Run.

called create report and I'm gonna click

Now, obviously that doesn't look

on run now obviously that doesn't look

exactly like I probably wanted it to look, right?

exactly like I probably wanted it to look right I'm just gonna go to backup

I'm just going to go to Back Up

and make an extra copy of this where I'm

and make an extra copy of this, where I'm going to continue to test this.

gonna continue to test this so I'm going

So I'm going to the Backup Sheet

to the backup sheet and I'm choosing

and I'm choosing Move or Copy.

move or copy I'm gonna select create a

I'm going to select Create a Copy

copy and I'll put it ready for the one

and I'll put it ready for the one called Backup of Backup and click Okay.

called backup a backup and click OK and

And now I have backup two and here we go.

now I have backup - and here we go now

Now I'm actually going to see

I'm actually gonna see where the macro

where the macro starts to go wrong.

starts to go wrong if I want to get this

If I want to get this macro to execute line by line,

macro to execute line by line then I'll

then I'll actually be able to watch and see

actually be able to watch and see where

where I start to run into problems.

I start to run into problems so I'm

So I'm going to go ahead and do that.

gonna go ahead and do that the tool

The tool that's going to allow me to do that is this button

that's gonna allow me to do that is this

that we just grabbed and put in our tool bar,

button that we just grabbed and put in

which is called Step Into.

our toolbar which is called step in -

Now, you can use the keyboard shortcut,

now you can use the keyboard shortcut

which is F8 to Step Into your macro.

which is f8 to step into your macro I'm

I'm going to use the button here

gonna use the button here because that

because that allows you to see it easily on my screen.

allows you to see it easily on my screen

However, I highly encourage you

however I highly encourage you to try

to try and use the shortcut keys.

and use the shortcut keys alright so I'm

All right, so I'm going to step into my macro here.

gonna step into my macro here and once I

And once I do that,

do that you'll see that this line of

you'll see that this line of code gets highlighted.

code gets highlighted and now that line

And now that line of code was run.

of code was run and now it's

And now it's highlighting the next line of code.

highlighting the next line of code

Notice that this says,

notice that this says the last one that

the last one that we were on says, oh, Sub CreateReport.

we were on says o sub create report so

So that's just the name of my macro.

that's just the name of my macro this

This next line says Insert Rows and Columns.

next line says insert rows and columns

And you'll notice down here

and you'll notice down here that I have

that I have the ability to create this macro

the ability to create this macro called

called InsertRowsAndColumns.

insert rows and columns and I'm actually

And I'm actually using it in this other macro up here.

using it in this other macro up here

So the process that's being controlled by that macro

so the process that's being controlled

is actually written in the code that's down here,

by that macro is actually written in the

more of it we can't see that's down there,

code that's down here more of it we can't see that's down there but I'm

but I'm running it up in this macro up here.

running it up in this macro up here

We'll talk about this later,

we'll talk about this later but this is

but this is called calling a macro,

called calling a macro which means that

which means that you're just executing the code

you're just executing the code in that

in that macro.

macro

Okay, so I'm going to continue to click on Step Into,

okay so I'm going to continue to click

and you'll see that it hops down to that macro

on step into and you'll see that hops

so we can actually run the code that's in that one.

down to that macro so it can actually run the code that's in that one and then

And then in a few minutes,

in a few minutes it'll hop back up and

it'll hop back up and run the code that's in the next macro.

run the code that's in the next macro

All right, so here we go.

alright so here we go here's our macro

Here's our macro called Insert Rows and Columns,

called insert rows and columns and I'm

and I'm going to Step Into,

gonna step into and it's gonna select

and it's going to select rows two through four.

rows two three four and once I click on

And once I click on Step Into,

step into it's actually gonna do that

it's actually going to do that. And you'll see in Excel,

and you'll see in Excel it's selecting

it's selecting rows two through four.

rows two through four now the next line

Now the next line of code here says, oh, Selection.Insert.

of code here says Oh selection insert so

So it's going to be inserting some rows here

it's going to be inserting some rows

and watch what happens when I do that.

here and watch what happens when I do that

Okay. So the user probably meant to

okay so the user probably meant to

insert rows above all the data here,

insert rows above all the data here but

but that's not what they did.

that's not what they did they inserted

They inserted rows above. Row two has now been moved down to row five

rows above row two is has now been moved

and we have a few extra rows,

down to row five and we have a few extra

but we probably meant for what was in row one

rows but we probably meant for what was

to go down there as well.

in row one to go down there as well so

So that's going to be one thing

that's going to be one thing that we're

that we're going to need to fix.

going to need to fix we're going to need

We're going to need to change that row two to row one.

to change that Row two to Row one

All right.

all right now my macro is gonna select

Now my macro is going to select column A, it says Column A Select, okay here we go.

column a says column a select okay here

I'm going to run that,

we go I'm gonna run that clicking on

clicking on Step Into.

step into there it goes it selects

There it goes, it selects Column A and then it's going to take the selection

column a and then it's gonna take the

and insert something to the right.

selection and insert something to the

And I'm just going to see what that looks like.

right and I'm just gonna see what that

Okay, that inserted another column to the left.

looks like okay that inserted in another

That looks pretty good to me.

column to the left that looks pretty good to me

All right, and now we're on the last line

all right and now we're on the last line

that says End Sub.

that says end sub once I actually run

Once I actually run that code that says End Sub

that code that says n sevens gonna hop

it's going to hop me back up,

me back up because remember we are

because remember we are running this larger macro up here

running this larger macro up here and we

and we are just calling to these smaller macros in order.

are just calling to these smaller macros in order

So it looks like the next macro that's going to run

so it looks like the next macro that's

is this one called InsertText.

gonna run is this one called insert text

Okay, I'm going to Step Into this process.

okay I'm gonna step into this process

And this one says, here's my sub procedure InsertText.

and this one is says here's my sub procedure insert text okay and then

Okay, and then we're going to hop down

we're gonna hop down to the next line of

to the next line of code and that is range A1 Select, okay.

code and that is range a1 select ok and

And it selected range A10.

it's selected range a10

And then the next line of code says

and then the next line of code says

active cell put in the words, "Our Global Company,"

active cell put in the words our global

and we'll talk about what all this coding stuff means,

company and we'll talk about what all

but just go ahead and watch this.

this coding stuff means but just go ahead and watch this so my guess is that

So my guess is that the end user

the end user didn't actually mean to put

didn't actually mean to put this word in our global,

this word in our global these words our

these words, "Our Global Company" in cell A10.

global company in cell a10 all that they

All that they meant to put that up as a title in cell A1.

meant to put that up as a title in cell

So I'm going to go ahead and correct that.

a1 so I'm gonna go ahead and correct

There we go.

that there we go all right and I'm gonna

All right, and I'm going to continue

continue stepping through my macro here

stepping through my macro here. So range A2 is going to get selected next,

so range a2 is gonna get selected next and then we're gonna put in the word

and then we're going to put in the word, "Stock Prices." And then in cell B4,

stock prices and then in the in cell b4

we're going to put in "Symbol" and "Open" and "High", "Low", "Close",

we're gonna put it in symbol and open and high-low

all of these neat little headers here

close all of these neat little headers

are automatically being inserted.

here are automatically being inserted so

So that's great. No problems there.

that's great no problems there I'm gonna

I'm going to click on Step Into,

click on step into and now it's running

and now it's running the third macro

the third macro in my original large

in my original, large macro at the top.

macro at the top it's gonna run this

It's going to run this third macro called FormatText. Okay, let's Step Into that one, so here we go.

third macro called format text okay let's step into that one so here we go

We're walking through FormatText,

we're walking through format text and

and I'm looking at my Excel sheet and it says

I'm looking at my excel sheet and it

columns C through G select.

says column C through G select and it's

And it's going to format all of those numerical values

gonna format all of those numerical

as currency figures.

values as currency figures okay that

Okay, that looks good to me.

looks good to me all right now it's

All right, now it's going to select columns H through J

gonna select columns H through J and

and format these all as percent.

format these all as percent that's good

That's good. And then it's going to select cell A1

and then it's gonna select cell a1 and

and change the way that cell A1 looks.

change the way that cell a1 looks of

Course I don't have anything in cell A1

course I don't have anything in cell a1

because I made a mistake, right.

because I made a mistake

I accidentally put that text in cell A10, but when I run this again next time

right I accidentally put that text in cell a10 but when I run this again next

I've already made those corrections.

time I've already made those Corrections

So that should get fixed.

so that should get fixed

And then it's going to change the look and feel

and then it's gonna change the look and

of cell A2 there,

feel of cell a2 there and I'll continue

and I'll continue running through my macro,

running through my macro which changes

which changes the look of my headers as well.

the look of my headers as well and then

And then my macro is going to select all these columns

my macro is going to select all these

and auto-fit them.

columns and autofit them and that's very

And that's very nice.

nice thank you very much and sub and

Thank you very much. End Sub and there I'm through it.

there I'm through it okay so I watched

Okay.

all these mistakes as they were

So I watched all these mistakes as they were happening

happening and I corrected them so I

and I corrected them. So I should be able to now

should be able to now go to the sheet

go to the sheet called Backup, make another copy,

called backup make another copy

and then find this item called Backup 3

and then find this item called backup three and let's go ahead and run our

and let's go ahead and run our macro on it.

macro on it and we should have debunked

And we should have debugged that whole thing.

that whole thing and there goes just the

And there it goes just the way it's supposed to look.

way it's supposed to look

If you haven't done so with me,

if you haven't done so with me go ahead

go ahead and take the moment to pause the video,

and take the moment to pause the video

walk through this macro with the Step Into tool and make sure that all of the bugs are fixed in your macro.

walk through this macro with the step into tool and make sure that all of the bugs are fixed in your macro don't

Don't forget to continue to back up your file

forget to continue to backup your file

every single time before you test it.

every single time before you test it and

And when you've done that,

when you've done that go ahead and come

go ahead and come back and we'll move on.

back and we'll move on

For the next couple of sections,

for the next couple of sections we're

we're going to be talking about a file called

gonna be talking about a file called

Creating Macros From Scratch.

creating macros from scratch we're going

We're going to be using this particular file

to be using this particular file to

to create a macro from scratch.

create a macro from scratch so make sure

So make sure that you have this file open.

that you have this file open you can

You can pause the video right now to do that.

pause the video right now to do that

Open up this file,

open up this file and then also open up

and then also open up VBA

VBA and make sure that you can see this

and make sure that you can see this bit of coding

bit of coding on the right-hand side

on the right-hand side that will be located in Module1,

that will be located in module one go

Go ahead and do that now.

ahead and do that now now the very first

Now the very first macro we're going to use

macro we're gonna use isn't going to be

isn't going to be made from scratch

made from scratch because it's already

because it's already in here.

in here we do have a macro on the

We do have a macro on the right-hand side here.

right-hand side here now we are going to

Now we are going to run this and just see what it does.

run this and just see what it does so

So keep an eye on the Excel document on the left-hand side.

keep an eye on the Excel document on the

And we're going to go ahead

left-hand side and we're gonna go ahead

and run this macro and see what it does.

and run this macro and see what it does

So this macro looks like it puts in an extra row at the top.

so this macro looks like it puts in an

It puts in some headers here.

extra row at the top it puts in some

We have employee ID, last name, first name.

headers here we have employee ID last name first name those are our headers

Those are our headers. And then it also turns them bold.

and then it also turns them bold okay so

Okay. So we're going to be looking at the code

we're gonna be looking at the code on

on the right-hand side

the right hand side and starting to read

and starting to read it as though these were normal sentences.

it as though these were normal sentences

The very first line here says Rows.

the very first line here says Rose it

It starts with the word Rows.

starts with the word Rose following that

Following that we have the exact row

we have the exact row we want to be

we want to be talking about in parentheses.

talking about in parentheses now this is

Now this is also surrounded by quotations.

also surrounded by quotations you're

You're going to see this a whole lot.

gonna see this a whole lot so this is

So this is the syntax of this particular object statement. Rows are an object.

the syntax of this particular object statement rows are an object and like we

And like we were saying at the beginning of class,

were saying at the beginning of class

every object oriented programming

operates in a very similar manner.

operates in a very similar manner so

So objects are always stated first.

objects are always stated first and then

And then after we state the object, in this case Rows,

after we state the object in this case Rose then we can say what we want to do

then we can say what we want to do with it,

with it or what color we want it to be

or what color we want it to be or what we want it to do.

or what we want it to do something like

Something like that.

that but we always must state the object

But we always must state the object first.

first so here we have the statement it

So here we have this statement that says Rows, specifically, row one.

says Rose specifically Row 1 and then

And then we're saying, we want to Insert.

we're saying we want to insert so this

So this little statement of Insert,

little statement of insert this is a

this is a different kind of terminology.

different kind of terminology so we have

So we have our objects,

our objects that's that's our roast

that's our Rows, that's our object.

that's our object and then we have this

And then we have this thing called Insert.

thing called insert insert is what's

Insert is what's called a method

called a method and let's take a look at

and let's take a look at some VBA grammar.

some VBA grimmer now let's start by

Now let's start by talking about objects.

talking about objects there are many

There are many different kinds of objects

different kinds of objects that you can

that you can use to start your statements.

use to start your statements these are

These are just some common ones.

just some common ones sheets are objects

Sheets are objects, tables, charts, cells,

tables charts cells columns and rows are

columns and rows are all objects.

all objects and you can use them to

And you can use them to start your statements in VBA.

start your statements in VBA following

Following these objects,

these objects then we need to be able to

then we need to be able to say what we want to do with them

say what we want to do with them or what

or what we want,

we want or have some way to describe

or have some way to describe them, right?

them right maybe we want it to be yellow

Maybe we want it to be yellow or something like that.

or something like that

Properties are the way that we describe certain objects.

properties are the way that we describe

So if we have a cell, that's cell A1,

certain objects so if we have a cell that's cell a1 maybe you want it to be a

maybe you want it to be a specific color.

specific color maybe you want $1 to show

Maybe you want a dollar to show up as a specific format,

up as a specific format or maybe you

or maybe you want your text

want your text to show up with a

to show up with a particular look.

particular look these are descriptions

These are descriptions about the way that any object is going to look

about the way that any object is going to look or basically just descriptions

or basically just descriptions about them.

about them you can think of these as

You can think of these as adjectives.

adjectives and then the third kind of

And then the third kind of terminology we'll talk about is methods.

terminology we'll talk about his methods

Methods do something.

methods do something

You can think of these like verbs.

you can think of these like verbs

So here in the macro coding that we have over here,

so here in the macro coding that we have

we can see that each one of our lines

over here we can see that each one of

is starting with an object, of course.

our lines is starting with an object of

And then following Rows,

course and then following rows I'm

I'm saying I want to insert extra rows.

saying I want to insert extra rows

Inserting, that's an action.

inserting that's an action so insert is

So insert is a method.

a method following that line we'll say

Following that line will say that

that the next thing we want to do is

the next thing we want to do is select range A1,

select range a 1 which means select cell

which means select cell A1, or rather refer to cell A1.

a1 or rather refer to cell a1 and then

And then after that, we're saying

after that we're saying we want the

we want the value of cell A1

value of cell a1 to be the words

to be the words, "Employee ID" or "Emp ID".

employee ID or EMP ID and then we do the

And then we do the same thing for cells B1 and C1.

same thing for cells b1 and c1 and then

And then at the bottom of our macro code,

at the bottom of our macro code here we

here we are then selecting some rows.

are then selecting some rows so we're

So we're saying Rows A1

saying rows a1

and then specifically that row, I want to alter the font.

and then specifically that row I want to alter the font I want to make the font

I want to make the font bold. And the way that we do this is we say Bold = True.

bold and the way that we do this is we say bold equals true and the reason is

And the reason is that bold can either be or not be,

that bold can either be or not be but

but there aren't any more options

there aren't any more options for

for whether bold is or is not

whether bold is or is not or or ways

or ways that bold can be.

that bold can be it can either be bold

It can either be bold or not bold.

or not bold and so for that reason that

And so for that reason, that is a logical

is a logical explanation there that we

explanation there that we have in the terms of true or false.

have in the terms of true or false so we

So we do say Bold = True.

do say bold equals true if I wanted to

If I wanted to turn off bold,

turn off bold I would just say bold

I would just say Bold = False.

equals false and then at the very last

And then at the very last line here, I am just grabbing all my content

line here I am just grabbing all my

or rather deselecting all of my content

content or rather deselecting all of my

by selecting on range A1.

content by selecting on range a1

Let's go ahead and build this ourselves.

let's go ahead and build this ourselves

All right, let's move on to the sheet called, MakeMacro.

all right let's move on to this sheet called make macro and we are still in

And we are still in the file called

the file called creating macros from

Creating Macros From Scratch.

scratch and I'm on the sheet called make

And I'm on the sheet called MakeMacro.

macro this is where I'm actually going

This is where I'm actually going to be able to create my macro from scratch.

to be able to create my macro from

Now let's just remember what my macro

scratch now let's just remember what my

needs to accomplish here.

macro needs to accomplish here we need

We need to insert a row

to insert a row at the top of all of our

at the top of all of our data, and then we're going to insert headers

data and then we're gonna insert headers

in that top row for each column.

in that top row for each column the

The third thing we're going to do is turn Row on Bold.

third thing we're gonna do is turn row 1

And the fourth thing we're going to do is de-select. So let's go into VBA and we're going to create

bold and the fourth thing we're gonna do is deselect so let's go into VBA and

a whole new module for ourselves.

we're gonna create a whole new module

So instead of using Module1,

for ourselves so instead of using module

I want us to actually insert a new module.

1 I want to actually insert a new module

So use your Insert dropdown menu and choose Module.

so use your insert drop-down menu and choose module and that inserts a new

And that inserts a new module for us.

module for us now the other thing that

Now, the other thing that we need to do

we need to do is insert a new procedure

is insert a new Procedure.

so I'm going to insert again and

So I'm going to Insert again and choosing Procedure.

choosing procedure and now I have to

And now I have to figure out

figure out what kind of a procedure

what kind of a procedure I want to make. Now, procedures are a way of saying like,

I want to make now procedures are a way

this is a large macro,

of saying like this is a large macro so

so you can put a bunch of different macros in a procedure,

you can put a bunch of different macros

but they're basically the same kind of thing.

in a procedure but they're basically the

They just run a bunch of code.

same kind of thing they just run a bunch

I'm going to name my macro here, or my procedure here.

of code I'm gonna name my macro here or my procedure here and I'm gonna name

And I'm going to name this

this format

FormatDataTest.

data test

There are a few different kinds of procedures

there are a few different kinds of

that you could choose.

procedures that you could choose you

You could choose a sub procedure.

could choose a sub procedure this is

This is just a regular macro.

just a regular macro so sub procedures

So sub procedures are just like many procedures.

are just like many procedures you can

You can think of them as like regular macros.

think of them as like regular macros and

Maybe you'd have a big macro

maybe you'd have a big macro which you

what'd you think of as a procedure, right?

think of as a procedure right okay the

Okay.

other kind of procedure that you can

The other kind of procedure that you can have is a function procedure.

have is a function procedure at the near

At the near the end of this course,

the ends of this course we're going to

we're going to be talking about how to

be talking about how to design your own

design your own functions.

functions so if there's not a function

So if there's not a function, you already not a function that you would like in Excel.

you already not a function that you would like in Excel

Maybe there's a function in Excel

maybe there's a function in Excel that

that you would like to be there.

you would like to be there maybe you

Maybe you need to accomplish some kind of a task

need to accomplish some kind of a task

and there isn't a function already for it in Excel.

and there isn't a function already for

And so you're having to build it

it in Excel and so you're having to

using a formula all the time.

build it using a formula all the time

In this case,

in this case what you can do is actually

what you can do is actually build it as a macro

build it as a macro and then it becomes

and then it becomes a usable function in Excel.

a usable function in Excel so that's

So that's pretty cool.

pretty cool however we're gonna stick to

However, we're going to stick to the basics for now.

the basics for now we're choosing the

We're choosing the sub procedure,

sub procedure and then we're not gonna

and then we're not going to be using this a whole lot,

be using this a whole lot but just as an

but just as an explanation,

explanation we have the options between

we have the options between public or private.

public or private we're gonna be using

We're going to be using public

public for the majority of this class

for the majority of this class because public macros are macros that can be

because public macros our Macker that

used in other areas.

can be used in other areas there macros

There are macros that can be used by other macros even.

that can be used by other macros even

Our previous example, where we were formatting text,

our previous example where we were formatting text you probably noticed

you probably noticed that that macro

that that macro called to other macros

called two other macros,

which is something we're gonna be

which is something we're going to be talking about later.

talking about later we will need those

We will need those macros to be public

macros to be public in order to be able

in order to be able to do that.

to do that

So I've named my macro FormatDataTest

so I've named my macro format data test

and I'm clicking Okay.

and I'm clicking okay

Now I have my bookends here.

now I have my book ends here my first

My first bookend says Public Sub, public sub procedure.

book end says public sub public sub

And then my ending bookend says End Sub,

procedure and then my ending book end says and sub and I'm gonna need these

and I'm going to need these bookends to surround any group of code,

bookends to surround any group of code

basically holds everything together.

basically holds everything together

All right, I am going to give myself

alright I am gonna give myself a little

a little bit of space here.

bit of space here so I'm putting a few

So I'm putting a few hard Enters in there,

hard enters in there hitting enter a few

hitting Enter a few times,

times and this is gonna give me space to

and this is going to give me space to actually design my code here.

actually design my code here alright so

All right, so I'm just going to follow

I'm just gonna follow these steps that

these steps that we lined out here.

we lined out here the very first step

The very first step that I'm going to do is insert a row.

that I'm gonna do is insert a row and of

And of course, I always have to name my object first.

course I always have to name my object

So I'm saying Rows.

first so I'm saying Rose that's my very

That's my very first object here.

first object here

And I'm going to explain which rows I want to select.

and I'm gonna explain which Rose I want to select

I want Rows 1

I want rose one

and I want the entirety of Row 1.

and I want the entirety of Row 1 and

And that's how we describe that to VBA

that's how we describe that to VBA with

with this colon in between 1, 1:1.

this colon in between 1 1 : 1 then we

And we surround both of those with the quotation marks.

surround both of those with the quotation marks

And we surround the quotation marks with this parenthesis.

and we surround the quotation marks with those parentheses

Following my object here,

following my object here I'm always

I'm always going to put a period

going to put a period that lets VBA know

that lets VBA know what the differences

what the difference is between my

between my objects and my methods

objects and my methods and my properties

and my properties and all that stuff.

and all that stuff so I'm putting my

So I'm putting my period in here and then I am putting in

period in here and then I am putting in

the name of the method that I want to happen with this row.

the name of the method that I want to happen with this row so I want to grab

So I want to grab this row

this row and then I want to insert

and then I want to insert another row.

another row all right now that I've

All right, now that I've inserted a row at the top,

inserted a row at the top the next thing

the next thing I'm going to want to do

I'm going to want to do is put in some

is put in some headers here.

headers here so I'm gonna select range

So I'm going to select range A1.

a1 that's where my first header is gonna

That's where my first header is going to go.

And the value of range A1 should be

and the value of range a 1 should be the

the title and the header name, employee,

title and the header name employee EMP

"Emp ID". Okay. And my next header is going to go on range b1.

ID okay and my next header is gonna go on range b1

You don't have to worry about being case sensitive here,

you don't have to worry about being case

and that's going to be "Last Name"

sensitive here and that's gonna be lastname

and then C1

and then Siwon

is their "First Name".

is their first name

Now, once we put in those headers there,

now once we put in those headers there I

I do want to take all those headers

do want to take all those headers that

that I've made in row 1 and turn it all bold.

I've made in row 1 and turn it all bold

So I'm going to tell VBA, please grab row or rows,

so I'm gonna tell VBA please grab row or

specifically, Row 1.

rows specifically row 1

And the fonts in there,

and the font in there

I want you to turn it bold.

I want you to turn it bold

And the way that I express that of course is with TRUE.

and the way that I express that of

I'm in the habit of typing in TRUE with all caps,

course is with true I'm in the habit of

but of course you don't have to.

typing and true with all caps but of course you don't have to and in fact

And in fact, when I hit Enter here,

when I hit enter here see how it makes

see how it makes that a proper capitalization.

that a proper capitalization

All right, and what I'm all done,

all right and when I'm all done this is

this is just for good measure.

just for good measure it's absolutely

It's absolutely not necessary.

not necessary all I'm gonna do is just

All I'm going to do is just de-select here. Meaning I already had the row selected, right?

deselect here meaning I already had the row selected right that was going to

That was going to turn it bold.

turn it bold and so it's still going to

And so it's still going to be selected

be selected unless I deselect on range

unless I de-select on range A1 and I'll just put in range A1

a1 and I'll just put in range a one

Select,

select that'll just select that one cell

that'll just select that one cell on the top left.

on the top left

Go ahead and pause the video.

go ahead and pause the video and when

And when you're done writing out all of your script here,

you're done writing out all of your

come on back and we'll run the code.

script here come on back and we'll run the code you may notice at

You may notice at times that a little window pops up as you're typing in

times that a little window pops up as

certain objects or methods or properties.

you're typing in certain objects or

This is called the IntelliSense window.

methods or properties this is called the intellisense window it's not a term that

It's not a term that you have to remember,

you have to remember but it is very

but it is very important to know that it exists

important to know that it exists because

because it's pretty handy. See here, I do have

it's pretty handy see here I do have the

the IntelliSense window

intellisense window suggesting that

suggesting that maybe I'm looking for the term Select

maybe I'm looking for the term select

and I get this little icon on the left-hand side, that's the icon for methods.

and I get this little icon on the left hand side that's the icon for methods

And I could double click on that.

and I could double click on that and

And that would insert the rest of that

that would insert the rest of that if I

if I hadn't finished filling it out already.

hadn't finished filling it out already

Okay, so here we go.

okay so here we go let's go ahead and

Let's go ahead and test this out.

test this out I am going to place my

I am going to place my cursor at the top of my macro

cursor at the top of my macro and click

and click on my play button.

on my play button

And the macro I'm looking to run is called FormatDataTest.

and the macro I'm looking to run is called format data test that's the one

That's the one we just made

we just made and I'm gonna click on run

and I'm going to click on Run and there it goes.

and there it goes

Go ahead and test that out.

go ahead and test that out you can test

You can test it out on multiple different sheets here,

it out on multiple different sheets here

if you would like.

if you would like and once you've tested

And once you've tested that out and practiced, if you would like,

that out and practiced if you would like

come on back

come on back and then we'll be making

and then we'll be making some more interesting macros.

some more interesting macarons

For the rest of this course,

for the rest of this course we're going

we're going to be creating some of our own scripting

to be creating some of our own scripting

or a lot of our own scripting.

or a lot of our own scripting in order

In order to be able to refer to any given cell

to be able to refer to any given cell

we're going to want to know,

we're gonna want to know or be a little

or be a little bit familiar with the

bit familiar with the range selection

range selection object, or rather the range object.

object or rather the range object

Now, there are many different ways

now there are many different ways to

to express the range object,

express the range object but the basics

but the basics just says, I'm going to select a range.

just says I'm gonna select a range that

That range is, let's say C3.

range is let's say C 3 I've selected

I've selected this cell inside

this cell inside this completely blank

this completely blank workbook here

workbook here using that particular

using that particular range object.

range object and I'm gonna show you that

And I'm going to show you that in just a moment.

in just a moment I've typed in that cell

I've typed in that cell "select this cell". So let's say my cursor is down here

select this cell so let's say my cursor

somewhere else in the interface.

is down here somewhere else in the

And I want to be able to select that particular cell.

interface and I want to be able to select that particular cell I can simply

I can simply type in range.

type in range

That's my object.

that's my object now I'm gonna need to

Now I'm going to need to specify

specify exactly which range I want to

exactly which range I want to select, which I do using the syntax parentheses, quotation marks

select which I do using the syntax parentheses quotation marks then the

then the actual cell I want to select

actual cell I want to select or cells I

or cells I want to select.

want to select

And then I close it out with the quotation marks

and then I close it out with the

and a ending parentheses.

quotation marks and a ending parenthesis

Now, if I actually want to select that item,

now if actually want to select that item

I am going to have to put

I am gonna have to put a select method

a Select method on the end of this object

on the end of this object that actually

that actually not just refers to that range, but selects it.

not just refers to that range but selects it and when I hit my Enter key

And when I hit my Enter key, you'll see that I have now selected that given cell.

you'll see that I have now selected that given cell

That's the most basic way

that's the most basic way to make a

to make a simple cell selection in VBA,

simple cell selection in VBA but there

but there are a whole lot more ways

are a whole lot more ways that you can

that you can make a cell selection.

make a cell selection and it all depends

And it all depends on what you want to do.

on what you want to do let's just look

Let's just look at a few different ways

at a few different ways that we can make

that we can make a particular cell selection.

a particular cell selection

Using the range object,

using the range object we can use the

we can use the simple example of selecting Range C3

simple example of selecting range c3 in

in the example that I just showed us,

the example that I just showed us I

I could also say a range inside of a range.

could also say a range inside of a range

We do that by typing Range.Range.

we do that by typing range dot range

What this does,

what this does is it essentially

is it essentially navigates

navigates from the beginning of the

from the beginning of the spreadsheet or A1, if you will,

spreadsheet or a1 if you will to range

to Range B2.

And then after that,

and then after that I make another jump

I make another jump to another range

to another range that isn't actually the

that isn't actually the cell B2, but rather what it means is that it's moving over two

cell b2 but rather what it means is that it's moving over to and moving down too

and moving down two,

so you can't have a range inside of a

so you can have a range inside of a range.

range you don't see this happen very

You don't see this happen very often.

often

You do see the macro recorder do it, however,

you do see the macro recorder do it

so don't be fooled

however so don't be fooled when you see

when you see it done by the macro recorder.

it done by the macro recorder it does

It does mean a range within a range,

mean a range within a range which does

which does not refer to the cell

not refer to the cell as you would be

as you would be familiar with it

familiar with it as the regular cell b2

as the regular cell B2. What it means is that it's offsetting past that cell.

what it means is that it's offsetting past that cell

You can also use the object called Cells.

you can also use the object called cells

Cells uses numerical values to refer to the column.

cells uses numerical values to refer to the column now the benefit of using

Now, the benefit of using cells is that VBA and Excel both like to use integers

cells is that VBA in Excel both like to

rather than text strings.

use integers rather than text strings so

So you'll find that this one sometimes

you'll find that this one sometimes

works a little bit faster.

works a little bit faster you can also

You can also use the shorthand,

use the shorthand which is the cell

which is the cell address,

address surrounded by the square

surrounded by the square brackets.

brackets and then later on in this

And then later on in this course,

course we are going to be using the

we are going to be using the offset method.

offset method if we were going to be

If we were going to be using that to select cell C3,

using that to select cell c3 we would

we would start with something like,

start with something like let's say cell

let's say cell A1, which we have there in the fifth example range,

a1 which we have there in the fifth

A1.Offset,

example range a1 dot offset offset is my

offset is my method.

method and what that does is it

And what that does is it essentially moves us in a certain direction.

essentially moves us in a certain

The Offset method does use integers

direction the offset method does use

to specify how far down and how far over we're going.

integers to specify how far down and how

The first one is how far down we're going.

far over we're going the first one is

The second one is how far over we're going.

how far down we're going the second one is how far over we're going and we are

And we are going to cover this

gonna cover this later on in the course

later on in the course as well.

as well so you don't need to memorize it

So you don't need to memorize it right now.

right now the final way that we could

The final way that we could refer to a range at this level of VBA coding anyway,

refer to arrange in at this level of VBA

is by using a variable.

coding anyway is by using a variable now

Now we're going to be talking about variables

we're gonna be talking about variables a

a whole lot later.

whole lot later so this is not something

So this is not something that you need

that you need to memorize right now

to memorize right now. However, if I stored a particular cell reference

however if I stored a particular cell

or cell range as a variable,

reference or cell range as a variable

let's call it myRange for example.

let's call it my range for example later

Later on, when I want to be able to refer

on what I want to be able to refer to

to that particular range, I can say,

that particular range I can say oh I'm

oh, I'm going to arrange which range?

going to arrange which range the range

The range that I named myRange.

that I named my range and those are the

And those are the many different ways

many different ways that we could refer

that we could refer to ranges. And they all worked great.

to ranges and they all work great if I

If I were to do this in the immediate window,

were to do this in the immediate window

I could say Range C3.Select.

I could say range C 3 dot select or in

Or in our second example,

our second example

we started by choosing range B2

we started by choosing range b2

and then we said, range B2 again,

and then we said range b2 again which

which you may or may not remember,

you may or may not remember basically

basically offsets from the original

offsets from the original and makes that

and makes that selection.

selection and I'm just going to go ahead

And I'm just going to go ahead and click on a random cell over here

and click on a random cell over here so

so that you can actually see this in action.

that you can actually see this in action

And when I execute this, I have selected on cell C3.

and when I execute this I have selected on cell c3

Now I could also use the Cells object,

now I could also use the sells object

which I described earlier.

which I described earlier sells uses

Cells uses integers, okay.

integers okay and we'll see in that

And we'll see in that little hint window, the Row Index.

little hint window the row index so the

So the row that I would like to choose,

row that I would like to choose and then

and then the column that I would like to choose.

the column that I would like to choose

Course, I do need to put in my Select method there.

course I do need to put in my select method there

And there we go, it'll select it.

and there we go it'll select it now of

Now, of course, if you actually want to see this in action,

course if you actually want to see this

I'll click over here for you and show you, there we go.

in action I'll click over here for you and show you there we go

And it works.

and it works

I can use my shorthand using the square brackets,

I can use my shorthand using the square

C3 square bracket,

brackets c3 square bracket

and that works as well.

and that works as well

And I can also use an offset,

and I can also use an offset which again

which again, we are not needing to learn right now.

we are not needing to learn right now I

I just want you to see

just want you to see that there are many

that there are many different options for you.

different options for you so I can start

So I can start by selecting any given cell

by selecting any given cell and then

and then move in a certain direction.

move in a certain direction let's say

Let's say two rows down and two rows over,

two rows down and two rows over which is

which is going to place us at cell C3,

going to place us at cell c3 if I

if I select it, of course.

selected of course

And there we go.

and there we go so those are the many

So those are the many different ways

different ways that you can express a

that you can express a range in VBA.

range in VBA and you'll want to keep

And you'll want to keep those in mind

those in mind as we're going throughout

as we're going throughout the course.

the course if you haven't done so

If you haven't done so already,

already open up a completely blank

open up a completely blank workbook,

workbook open up your visual basics for

open up your Visual Basics for Application window

application window on the right-hand

on the right-hand side, go ahead and type in any cell in your Excel spreadsheet.

side go ahead and type in any cell in your Excel spreadsheet and I want you to

And I want you to practice using these ranges

practice using these ranges to actually

to actually select that cell,

select that cell using the different

using the different options that you have available.

options that you have available go ahead

Go ahead and come back

and come back and use any of the given

and use any of the given options that are listed on the slide here

options that are listed on the slide

to actually select that range.

here to actually select that range

The next most important selection tool

the next most important selection tool

that we're going to have is the method called Select,

that we're going to have is the method

which we've already seen

called select which we've already seen

and the object called Selection.

and the object called selection they are

They are very closely related,

very closely related but they do very

but they do very different things.

different things as we saw in the last

As we saw in the last example,

example I could select a particular

I could select a particular range

range by referring to that range

by referring to that range directly

directly with the name of that range

with the name of that range using the Range object.

using the range object and then after

And then after that, I could say Select and that'll select Range C3.

that I could say select and that will select range c3 now that I've actually

Now that I've actually selected that item,

selected that item I can now refer to it

I can now refer to it using the object called Selection.

using the object called selection now

Now notice the difference between selection and range.

notice the difference between selection

Selection does not require me

and range selection does not require me

to actually specify a cell name,

to actually specify a cell name meaning

meaning the range

the range [Music]

is an absolute cell reference.

is an absolute cell reference it

It absolutely always refers to a particular given cell, whereas the Selection object instead refers to

absolutely always refers to a particular given cell whereas the selection object instead refers to what has already been

what has already been selected. So this can be rather dynamic.

selected so this can be rather dynamic

We can use this in all different kinds of situations.

we can use this in all different kinds

And indeed later on, throughout the course,

of situations and indeed later on

we're going to be using this bit of code

throughout the course we're going to be using this bit of code to be able to

to be able to allow other bits of code

allow other bits of code to make a

to make a selection for us,

selection for us and then be able to run

and then be able to run whatever kind of specifications we would like here.

whatever kind of specifications we would like here

Now that we have selected this given cell,

now that we have selected this given

we can do a number of important things.

cell we can do a number of important

I could put a particular value in this cell.

things I could put a particular value in

I could say something like, "Hello world".

this cell I could say something like hello world and that changes the text

And that changes the text that is actually placed in that cell to

that is actually placed in that cell to

whatever I chose.

I chose

I can also change some of the features

I can also change the some of the

of this particular cell.

features of this particular cell I could

I could say, okay, the interior

say okay the interior color and you

color, and you don't have to memorize this,

don't have to memorize this of this cell

of this cell is something like 65535

is something like 65535 which I happen

which I happen to have memorized. It is also known as VB yellow.

to have memorized it is also known as VB

It is one of the most typical color selections

yellow it is one of the most typical

that you can make in visual basic.

color selections that you can make in Visual Basic should you be interested

Should you be interested, you can absolutely go into the slide deck

you can absolutely go into the slide

that has been provided for you for this course.

deck that has been provided for you for

And there are some slides

this course and there are some slides

that are all about color specifications in VBA. There are groups called Theme Colors and VB Colors

that are all about color specifications in VBA there are groups called theme colors and VB colors that have a

that have a specific number of assignments already in VBA.

specific number of assignments already

These are the lists of the possible assignments

in VBA these are the lists of the

that we could use.

possible assignments that we could use

You do not have to remember these right now.

you do not have to remember these right

We will be taking a look at a couple of them,

now we will be taking a look at a couple

but basically you can come back to the slide

of them but basically you can come back

anytime you would like to be able to check these out.

to this slide anytime you would like to

There are other options for

be able to check these out there are

how you can color code things in VBA.

other options for how you can color code things in VBA and that is using what's

And that is using what's referred to as RGB colors or hex colors or hexadecimal.

referred to as RGB colors or hex colors or hexadecimal you can Google or Bing

You can Google or Bing either of these options,

either of these options either of these

either of these terms. And you will get nice websites

terms and you will get nice websites

with full listings of different kinds of RGB colors,

with full listings of different kinds of

depending on which particular hue you are interested in.

RGB colors depending on which particular hue you are interested in if we were to

If we were to use either of these,

use either of these I could just say

I could just say Selection.Interior.Color = 65535.

selection interior color equals 65535

That's the hex code for this.

that's the hex code for this or I could

Or I could say something like VB yellow or VB blue.

say something like V be yellow or V be

I'll say blue so that it actually changes the color

blue I'll say blue so that it actually

and we can see it happen there.

changes the color and we can see it

There we go.

happen there there we go the interior has changed now

The interior has changed now to blue. That's how we use the VB colors.

to blue that's how we use the VB colors

You can also use the same colors by saying,

you can also use the same colors by

hey, this is going to be a theme color.

saying hey this is gonna be a theme

And we could refer to a specific theme color by saying X, L,

color and we could refer to a specific theme color by saying X L you'll see

you'll see XL starting a lot of these property specifications, xlThemeColor,

excel starting a lot of these property specifications Excel theme color

we'll say Dark1.

we'll say dark one

Now Dark1 happens to not be very dark. We'll try Dark2.

to not be very dark we'll try dark too

That also happens to not be very dark,

and to not be very dark but we can't see

but we can see it in action.

it in action

And then finally you can choose any of your RGB colors

and then finally you can choose any of your RGB colors

by simply saying RGB first

by simply saying RGB first and then

and then specifying the three digits of that color.

specifying the three digits of that color

Like so.

like so

Now of course,

now of course selection is used for much

Selection is used for much more than just color.

more than just color you could say

You could say Selection.Font

selection dot font

like we did earlier and say, I want to turn that font bold.

like we did earlier and say I want to turn that font:bold

And there we go, it's bold.

and there we go it's bold it's kind of

It's kind of hard to tell,

hard to tell cuz that cell is a little

because that cell is a little bit dark,

bit dark

but that text has bolded there.

that text has bolded they're here let's

Here, let's use both of them together.

use both of them together

Now you can see that it's actually bold

now you can see that it's actually bold

and that's how we use the Selection object.

and that's how we use the selection

We are also going to be using that

object we are also going to be using

a whole lot throughout the course.

that a whole lot throughout the course

So if you haven't done so already,

so if you haven't done so already go

go ahead and get a few practices in using

ahead and get a few practices in using

the Selection object

the selection object and come on back

and come on back

and then we'll start doing some very

and then we'll start doing some very interesting scripting.

interesting scripting

It's at this point in VBA programming,

it's at this point in VBA programming

where we're going to need to start being able to insert

where we're gonna need to start being

certain contents or certain values into given cells.

able to insert certain contents or certain values into given cells now

Now there is a property that does that very nicely for us,

there is a property that does that very

and it's called Value.

nicely for us and it's called value it

It works very well with either the Range object

works very well with either the range

or the Selection object.

object or the selection object those are

Those are the two

the two that we're going to be focusing

that we're going to be focusing on right now.

on right now I could simply state my

I could simply state my Range object such as cell A1,

range object such as cell a1

and I could say,

I could say I want the value of that

I want the value of that particular cell to be the word

particular cell to be the word

And that gets inserted there.

and that gets inserted

Now I've already done that in cell C3 as well

now I've already done that in cell c3 as

I've inserted something in cell C3

well I've inserted something in cell c3

and I turned it bold

and I turned it bold in the last example

in the last example. Let's say now I want to be able to clear contents

let's say now I want to be able to clear

from a given cell.

contents from a given cell if I wanted

If I wanted to clear the contents from cell A1, for instance,

to clear the contents from cell a1 for

I would simply refer to cell A1 using my Range object.

instance I would simply refer to cell a1 using my range object

And I would say, Clear.

and I would say clear

Notice there are a bunch more options here.

notice there are a bunch more options

We have Clear,

here we have clear we have clear

we have Clear Comments. We have Clear Contents.

comments we have clear contents that

That will keep the formatting,

will keep the formatting but remove the

but remove the contents from a given cell.

contents from a given cell you can also

You can also clear the format specifically,

clear the format specifically so not the

so not the contents but the formats only.

contents but the formats only clear is

Clear is for Clear all.

for clear all and when I hit that it

And when I hit that, it absolutely clears everything from that given cell.

absolutely clears everything from that

If you haven't done so already

given cell if you haven't done so

take a moment to use your immediate window

already take a moment to use your

and practice using the Range.Value

immediate window and practice using the range value and the range start clear

and the Range.Clear. And when you've done that one,

and when you've done that one practice

practice the exact same with Selection.Value

the exact same with selection dot value

and Selection.Clear.

and selection dot clear

Often we get content that comes in from a data dump,

often we get content that comes in from

or maybe we have a coworker

a data dump or maybe we have a co-worker

that does some data entry for us.

that does some data entry for us either

Either way, sometimes we get a bunch of content

way sometimes we get a bunch of content

and it's our job to spend quite a bit of our own time,

and it's our job to spend quite a bit of

just being able to format that content.

our own time just being able to format

And so being able to turn that into a macro

that content and so being able to turn that into a macro would be highly

would be highly advantageous for us.

advantageous for us I often have

I often have circumstances in which I get a bunch of content

circumstances in which I get a bunch of

and I need to rename all the sheets

content and I need to rename all the

that came in with that particular content.

sheets that came in with that particular content in order to do that I have to be

In order to do that, I have to be somewhat familiar with the sheets

somewhat familiar with the sheets and

and active sheet objects.

active sheet objects now I'm going to

Now I'm going to use my immediate window

use my immediate window to exemplify

to exemplify these first few examples.

these first few examples if I wanted to

If I wanted to name the active sheet,

name the active sheet which happens to

which happens to be sheet four in this case,

be sheet 4 in this case I could simply

I could simply refer to active sheet.

refer to active sheet that's the name of

That's the name of the object.

the object we could use a property

We could use a property called Name

called name

and I'll just name that "Portfolio4".

and I'll just name that portfolio for

And when I hit enter,

and when I hit enter that has renamed my

that has renamed my sheet to "Portfolio4".

sheet to portfolio for now let's say I

Now, let's say I want to be able to move

want to be able to move to a different

to a different one of my sheets.

one of my sheets I can do that in a few

I can do that in a few different ways.

different ways I can do that using the

I can do that using the Select method,

select method but I am gonna need to

but I am going to need to refer to which object

refer to which object I need to go to

I need to go to first.

first so I could say something like

So I could say something like sheets,

sheets

the first sheet, in order.

the first sheet in order

If I used sheets one,

if I used sheets one and the first sheet

the first sheet was in the order of sheets in my workbook,

was in the order of sheets in my workbook or I could refer to the sheet

or I could refer to the sheet by saying sheet one.

by saying sheet one that happens to be

That happens to be the name of sheet one right now.

the name of sheet one right now

And then I could say Select.

and then I could say select so if I

So if I wanted to turn this into a macro

wanted to turn this into a macro that

that automatically went in and renamed all my sheets for me,

automatically went in and renamed all my

I could easily do that by navigating to a given sheet,

sheets for me I could easily do that by navigating to a given sheet using the

using the Select method. And then naming that using the Name property.

Select method and then naming that using the name property why don't you go ahead

Why don't you go ahead and practice that.

and practice that

Insert a new module, using the Insert dropdown menu,

insert a new module using the insert

create a module for yourself and create a procedure as well.

drop-down menu create a module for yourself and create a procedure as well

I'm going to name mine, Testing123,

I'm going to name mine testing one two three

and I'm going to put a macro in here

and I'm gonna put a macro in here that's

that's going to go to each sheet

gonna go to each sheet

and then rename it

whatever you'd like.

whatever you'd like when you're done you

When you're done, you should have all four sheets selected in your workbook.

should have all 4 sheets selected in your workbook and then you should have

And then you should have each one renamed.

each one renamed when you're done coding

When you're done coding that,

that go ahead and run it make sure that

go ahead and run it and make sure that it works. And then come on back.

it works and then come on back

Welcome back.

welcome back the last little bit of

The last little bit of important information

important information that we're going

that we're going to learn

to learn before we actually get a chance

before we actually get a chance

to practice everything we've learned so

to practice everything we've learned so far is the Current Region property.

far is the current region property now

Now the Current Region property basically does

the current region property basically

what Control Aid does for you

does what control Aid does for you if

if you are actually operating within Excel itself.

you were actually operating within Excel

So if I selected one of the cells within this dataset here,

itself so if I selected one of the cells within this data set here and I'm in the

and I'm in the file called Insert and Format Text,

file called in certain format text you

you can join me if you would like.

can join me if you would like if I

If I select any one of the cells

select any one of the cells in inside of

inside of any one of these lists,

any one of these lists I happen to be in

I happen to be in the list on the sheet called Hybrid.

the list on the sheet called hybrid if I

If I were to click on one of these cells

were to click on one of these cells and

and use Control + A,

use ctrl a it selects all of the data

it selects all of the data and it knows exactly where to stop

and it knows exactly where to stop

because I have a well-formed list.

because I have a well-formed list

Now, if I were to do this within VBA,

now if I were to do this within VBA I

I would use the Current Region property.

would use the current region property so

So I'm going to start by typing in the word Selection,

I'm going to start by typing in the word

the object Selection,

selection the object selection and I'm

and I'm going to use the current region

going to use the current region

using the Current Region property.

using the current region property and

And then after I've referred to the current region,

then after I've referred to the current

I'm actually going to say,

region I'm actually going to say I want

I want to select it.

to select it

And once I actually execute this, you'll see this in action.

and once I actually execute this you'll

And indeed VBA is able to select this whole table.

see this in action and indeed VBA is able to select this whole table we're

We're going to be using this

going to be using this throughout the

throughout the course of our class.

course of our class so make sure you are

So make sure you are able to explore this a little bit

able to explore this a little bit and

and practice it.

practice it and once you have done so

And once you have done so,

come on back and then we're actually

come on back and then we're actually going to put

going to put everything we've learned so

everything we've learned so far into a practice exercise.

far into a practice exercise

This is a practice video.

this is a practice video what we're

What we're going to be doing is taking everything

gonna be doing is taking everything that

that we've learned so far throughout this course

we have learned so far throughout this

and being able to implement it all at the same time

course and being able to implement it

into one macro.

all at the same time into one macro I do

I do have the file called Insert and Format Text open,

have the file called insert and format text open and I've opened my Visual

and I've opened my Visual Basic for Applications editor,

Basic for applications editor over on

over on the right-hand side.

the right-hand side I am going to select

I am going to select the sheet called Conservative.

the sheet called conservative that's

That's what I'm going to get started on.

what I'm gonna get started on so make

So make sure that you're on the sheet called Conservative.

sure that you're on the sheet called conservative the tasks that we're going

The tasks that we are going to be accomplishing

to be accomplishing in this particular

in this particular rehearsal is

rehearsal is we're gonna be inserting

we're going to be inserting some headers.

some headers we're gonna be formatting

We're going to be formatting those headers and columns of data all the same time.

those headers and columns of data all the same time and we're gonna be able to

And we're going to be able to execute these

execute these on all the other sheets

on all the other sheets that we have inside of this workbook.

that we have inside of this workbook

Furthermore,

furthermore the last thing that we're

the last thing that we're going to do

gonna do is actually select all the

is actually select all the content

content that we have organized in this

that we have organized in this list here. And we're going to color the background of that

list here and we're gonna color the

so that it looks like a nice visually-pleasing list there.

background of that so that it looks like a nice visually pleasing list there

So that's the exercise that we're going to go through.

so that's the exercise that we're going

We're going to create three different bits of macro script

to go through we're gonna create three different bits of macro script in order

in order to be able to do this.

to be able to do this the first two

The first two we're actually going to be doing

we're actually going to be doing with

with the macro recorder.

the macro recorder because if the macro

Because if the macro recorder can do anything fast for us,

recorder can do anything fast for us we

we prefer to use the macro recorder.

prefer to use the macro recorder so go

So go ahead and get started with the macro recorder.

ahead and get started with the macro

And this is going to be the macro

recorder and this is going to be the

that actually inserts our text.

macro that actually inserts our text so

So I'm going to call it InsertText.

I'm going to call it insert

I'm going to call it InsertTxt.

text I'm going to call it insert txt

Then I'm going to click Okay.

I'm going to click OK

And I'm going to get started

and I'm gonna get started by just

by just putting some titles on here.

putting some titles on here now I'm

Now I'm going to call this "Our Global Company,"

gonna call this our global company

and I'm putting that in cell A1.

and I'm putting that in cell a1

In cell A2, I'm going to say

in cell a2 I'm gonna say this is always

this always talks about our stock prices.

talks about our stock prices then in b4

Then in B4,

I'm gonna put in the name of my

I'm going to put in the name of my Portfolio here, "Conservative Portfolio".

portfolio here conservative portfolio

Now you may already be wondering,

now you may already be wondering well

well, is that going to cause a problem?

it's that gonna cause a problem if I'm

If I'm writing Conservative Portfolio here,

writing conservative portfolio here what

what if I move on to the Balanced Portfolio and the

if I move on to the balanced portfolio

aggressive Portfolio and the defensive Portfolio,

and the aggressive portfolio and the defensive portfolio then this little bit

then this little bit of script that I've created here,

of script that I've created here isn't

isn't actually going to work and you would be right.

actually gonna work and you would be

We are going to be putting in some custom script

right we're gonna be putting in some

in a few minutes,

custom script in a few minutes that's

that's going to check the name of our sheet for us,

gonna check the name of our sheet for us

but for now, let's just keep going.

but for now let's just keep going I'm

I'm going to put in some titles of my list here.

gonna put in some titles of my list here

So this is my Symbol

so this is my symbol

and I have the Open, Close

and I have the open/close and then the

and then the Net Change with the time period.

net change for the time period

And that's all the texts that I need to be able to insert

and that's all the text that I need to

with this particular macro.

be able to insert with this particular

So I'm going to go ahead and click Stop,

macro so I'm going to go ahead and click stop

and it's stopped recording.

stop recording

And I'm going to go look and see

and I'm gonna go look and see what the

what the macro recorder made for me.

macro recorder made for me it's gonna be

It's going to be located in my Modules folder

located in my modules folder and my

in my Visual Basic for Applications window.

Visual Basic for applications window

Here we go. Let's take a look at it.

there we go let's take a look at it

Now the macro recorder actually put in a bunch of stuff

now the macro recorder actually put in a

I don't need and some stuff that looks a little bit weird.

bunch of stuff I don't need and some stuff that looks a little bit weird this

This thing that says Formula R1C1

thing that says formula R ones see one

that's a really antiquated coding language.

that's a really antiquated coding

That just means value.

language that just means value so most

So most people, these days

people these days don't actually type

don't actually type that in, what they type in is Value, like I just did.

that in what they type in is value like I just did

And in fact, the macro recorder also said,

and in fact the macro recorder also said

Range A1 select and then ActiveCell input the value.

ring j1 select and then active cell

But I don't need that.

input the value but I don't need that

That's a little bit redundant.

that's a little bit redundant so I could

So I could shorten this down to simply

shorten this down to simply range a for

Range A4.Value = "Our Global Company".

value equals our global company

And I'm going to do that for each one of these lines.

I'm gonna do that for each one of these

That's just going to clean it all up for me.

lines that's just gonna clean it all up

The only reason that you would want to clean this up,

for me the only reason that you would want to clean this up it's a little bit

it's a little bit less byte expensive.

less bite expensive and the other

And the other benefit is that

benefit is that when I come back and

when I come back and look at this macro code

look at this macro code a few months

a few months from now, it's nicer for me to have it cleaner

from now it's nicer for me to have it

so it's easier for me to tell what's going on.

cleaner so it's easier for me to tell what's going on

Most of we macro programmers have the real world scenario

most of we macro programmers have the

in which we come back to a macro later on

real-world scenario in which we come

and we can't remember exactly what it did.

back to a macro later on and we can't

And so we have to read it all over again.

remember exactly what it did and so we

So the cleaner it is, the nicer it is.

have to read it all over again so the cleaner it is the nicer it is now we

Now we were talking earlier about the difficulty

were talking earlier about the

with having this word "Conservative" in here,

difficulty with having this word

because I'm going to have multiple different Portfolios

conservative in here because I'm gonna have multiple different portfolios and

and they're going to need their own names.

they're gonna need their own names so

So instead of having the macro code

instead of having the macro code

actually put in the word "Conservative,"

actually put in the word conservative I

I'm going to take that out.

want to take that out

And I'm going to tell VB,

and I'm gonna tell VB hey check the name

hey, check the name of my sheet right here,

of my sheet right here check the name of

check the name of my sheet and put in that word.

my sheet and put in that word

So as we learned earlier,

so as we learned earlier we can refer to

we can refer to the active sheet

the active sheet

and the name of that active sheet

and the name of that active sheet using

using ActiveSheet.Name

active sheet name and then I can simply

and then I can simply concatenate that with the word "Portfolio".

concatenate that with the word portfolio

I did intentionally leave a space here

because I want the word "Conservative"

because I want the word conservative to

to be separated from the word "Portfolio"

be separated from the word portfolio

because that's going to be nicer to look at.

because that's going to be nicer look at

And for the most part I'm done inserting my text.

and for the most part I'm done inserting my text all right if I wanted to test

All right, if I wanted to test this out,

this out I could go to the next sheet

I could go to the next sheet called Balanced

called balanced and I could click on my

and I could click on my play button.

play button and indeed that all gets

And indeed that all gets inserted for me,

inserted for me including the word

including the word "Balanced" in this case,

balanced in this case instead of the

instead of the word "Conservative,"

word conservative which is exactly what

which is exactly what I wanted.

I wanted if you haven't done so already

If you haven't done so already, go ahead and record your macro,

go ahead and record your macro clean it

clean it up and then make sure

up and then make sure to make this

to make this slight modification to the code

slight modification to the code so that

so that your code will actually insert

your code will actually insert the name

the name of the sheet and name your Portfolios

of the sheet and name your portfolio's

in a custom manner.

in a custom manner and then come on back

And then come on back and we'll continue.

and we'll continue the next little bit

The next little bit

that we're going to be adding to this is

that we're going to be adding to this is a formatting macro.

a formatting macro so I'm gonna go back

So I'm going to go back to my Conservative sheet,

to my conservative sheet because this is

because this is where I'm creating everything,

where I'm creating everything although I

although I wouldn't have to,

wouldn't have to

and I'm going to go and record another macro here.

and I'm gonna go and record another macro here I'm going to click on record

I'm going to click on record and this one is going to be called FormatTxt

and this one is going to be call format

and I'm going to click Okay.

txt I'm going to click OK

And I'm just going to do some basic formatting here, cell A1,

and I'm just gonna do some basic formatting here cell a1 I'm gonna make

I'm going to make this text size 20,

this text size 20 I'm gonna make it bold

I'm going to make it bold and the next cell down that says "Stock Prices,"

and the next cell down that says stock

maybe I'll make this 18 and bold

prices may well make this 18 and bold

and where it says Conservative Portfolio,

and where it says conservative portfolio

maybe I want that to be 14 and bold.

maybe I want that to be 14 and bold

And my headers here I'll make those bold as well

and my headers here I'll make those bold

and size 12.

as well and size 12

Now, the other thing I need to do is

now the other thing I need to do is

change all my numerical values here

change all my numerical values here that

that are looking a little bit confusing.

are looking a little bit confusing I

I want them to all look like numerical values.

want them to all look like numerical

So I'm going to click and drag on my column headers

values so I'm going to click and drag on

to select multiple.

my column headers to select multiple and

And then I'm just going to click on my dollar sign

then I'm just gonna click on my dollar

on my Home tab of the ribbon in the Number command group.

sign on my Home tab of the ribbon in the number command group and that's gonna

And that's going to convert those all to a dollar sign.

convert those all to a dollar sign now a

Now a couple of these columns are a little bit too small,

couple of these columns are a little bit

especially the one called Net Change.

too small especially the one called net

So I can't see everything that's there.

change so I can't see everything that's

And so I'm going to select all the columns in my list here,

there and so I'm gonna select all the columns in my list here all of them

all of them, including B, so B through E,

including B so B through E

and I'm going to auto fit them by hovering over the divider

now I'm gonna autofit them by hovering

in between any of them and double clicking.

over the divider in between any of them and double-clicking that's gonna make it

That's going to make it big enough to fit all of the content.

big enough to fit all of the content and

And then the last thing I'm going to do

then the last thing I'm going to do is

is de-select here.

deselect here I like to deselect so I

I like to de-select so I don't leave a bunch of things selected.

don't leave a bunch of things selected

So I'll de-select on E1 and that's fine.

so I'll do you select Ani one and that's

And I'm done.

fine and I'm done I'm gonna go to the

I'm going to go to the Developer tab and stop recording.

Developer tab and stop recording

Now, of course,

now of course we're gonna go back and

we're going to go back and look at the code

look at the code and see what's here

and see what's here that we need

that we need and what's here that we

and what's here that we don't need, just like in the last example.

don't need just like in the last example

With this particular example,

with this particular example when it

when it comes to formatting text,

comes to formatting text the macro

the macro recorder brings in a lot of things

recorder brings in a lot of things that

that we don't need.

we don't need so you'll see in this very

So you'll see in this very first line here, it says, okay,

first line here it says okay select cell

select cell A1.

And then you'll see this thing called a With

and then you'll see this thing called a

End With statement.

with and with statement the reason this

The reason this is here is because without it,

is here is because without it each one

each one of these slight modifications,

of these slight modifications like the

like the name of the font, which is Ariel,

name of the font which is Arial the size

the size of the font, which is 20,

of the font which is 20 the

the strike through whether I want it or not,

strikethrough whether I want it or not

the superscript whether I want it or not, et cetera,

this superscript whether I wanted it or

all of these would have to have the words,

not etc all of these would have to have the

Selection.Font in front of them normally.

words selection dot font in front of

So it would be Selection.Font.Name,

them normally so would be selection dot

Selection.Font.Size,

font name selection dot font size

Selection.Font.Strikethrough.

selection font dot strikethrough and

And that would start to get a little bit cumbersome

that would start to get a little bit

for me as a programmer.

cumbersome for me as a programmer and so

And so I have this nice thing called

I have this nice thing called a width

a With End With statement

and with statement that allows me to

that allows me to make a object statement once

make a object statement once selection

Selection.Font,

dot font and then refer to all of the

and then refer to all of the properties of that particular object all at the same time.

properties of that particular object all

So I don't have to type my object name over and over

at the same time so I don't have to type

and over again.

my object name over and over and over

In this case, there's a lot of things in here

again in this case there's a lot of

I don't actually need

things in here I don't actually need

like theme font, tint, and shade,

like theme font tint and shade color

color index, underlying shadow, blah, blah, blah, blah.

index underline shadow blah blah blah

I don't need anything except for the name and the size.

blah I don't need anything except for the name and the size and so I'm going

And so I'm going to be deleting those.

to be deleting those and so this is how

And so this is how I'm going to clean up this.

I'm gonna clean up this now you'll see

Now you'll see this statement down here that says

this statement down here that says

Selection.Font.Bold = True.

selection font bold equals true

Well, technically I probably want that up here

well technically I probably want that up here

and again, I don't have to have it in here,

and again I don't have to have it in here

but the nice thing about moving that around

but the nice thing about moving that

is that later on,

around is that later on when I look at

when I look at this macro code four months from now,

this macro code four months from now or

or whatever,

whatever that I'll be able to see all of

that I'll be able to see all of my font formatting

my font formatting directly in the same

directly in the same spot.

spot

If you haven't done so already,

if you haven't done so already take a

take a second to pause the video,

second to pause the video record your

record your macro, that actually formats your content.

macro that actually formats your content

Then come back in here

then come back in here and clean up

and clean up whatever you can.

whatever you can ideally if you are not

Ideally, if you are not sure that you can delete certain texts,

sure that you can delete certain text

make sure to comment it out and then run it,

make sure to comment it out and then run

see if it works.

it see if it works and if it works then

And if it works, then you can actually

you can actually delete all that content

delete all that content.

so go ahead and do that now come on back

So go ahead and do that now, come on back and we'll do the third step.

and we'll do the third step and the last

And the last little part of this particular practice

little part of this particular practice

is for me to select all the content

is for me to select all the content that

that happens to be in this list

happens to be in this list and make it a

and make it a slightly different interior color.

slightly different interior color and

And that way it'll kind of look nice.

that way it'll kind of look nice

However, all of my lists happen to be different sizes.

however my all of my lists happen to be

And so I am going to want to use the

different sizes and so I am gonna want

Current Region property

to use the current region property

that's going to figure out exactly what the size is.

that's going to figure out exactly what

Then I need a Format there

the size is that I need to format there

and it's going to do it for me.

and it's gonna do it for me in order to

In order to do this,

do this I'm actually gonna do my own

I'm actually going to do my own manual scripting.

manual scripting you could also do it

You could also do it with the macro recorder

with the macro recorder if you wanted to

if you wanted to,

but I'm gonna take this opportunity to

but I'm going to take this opportunity to insert a new procedure here.

insert a new procedure here a procedure

A procedure is basically a mini macro or a macro,

is basically a mini macro or a macro

and I'm going to call this one FormatList,

I'm gonna call this one or Matt list

click Okay.

okay

And all I'm going to do is start by selecting range B6,

and all I'm gonna do is start by selecting Range b6 because that always

because that always has the list content in it.

has the list content in it

As you can see here,

as you can see here my list starts with

my list starts with B6 and it just so happens that it also

b6 and it just so happens that it also

starts in B6 with all the rest of the sheets there.

starts on b6 with all the rest of the

So I'll say Range,

sheets there so I'll say range b6 dot

B6.CurrentRegion.Select

current region dot select

and then I'll say based on that selection,

and then I'll say based on that selection

I want the interior

of all those cells

of all those cells to be a particular

to be a particular theme color,

theme color

and maybe I'll choose xlThemeColor,

and maybe I'll choose Excel theme color

Dark2.

And if I run this on the given sheet,

I know if I run this on the given sheet

I'll be able to see that it quickly formats my list there.

I'll be able to see that it quickly formats my list there

Okay. Now that you have created all of these macros,

okay now that you have created all of these macros go to all the rest of the

go to all the rest of the sheets in the document

sheets in the document and run them all

and run them all here

here

and just sort of appreciate at this moment,

and just sort of appreciated this moment

exactly how much faster it would be to create these macros

exactly how much faster it would be to

and then have them permanently from now on,

create these macros and then have them permanently from now on in every single

in every single sheet here and use them

sheet here and use them

instead of doing it manually.

instead of doing it manually

When you're done,

when you're done come on back and we'll

come on back and we'll keep going.

keep going

Thanks for watching.

thanks for watching don't forget we also

Don't forget, we also offer live classes and office applications,

offer live classes and office

professional development and private training.

applications professional development

visit learnit.com for more details,

and private training visit learning comm for more details please remember to like