IntroductionWelcome| 00:04 | Hi! I am Dennis Taylor.
| | 00:06 | Welcome to Microsoft
Excel 2010: Macros in Depth.
| | 00:10 | If you want to eliminate some of the
drudgery associated with repetitious
| | 00:14 | command sequences or data manipulation
tasks, then you need to know more about
| | 00:19 | macros, Excel's automation capability.
| | 00:23 | I'll show you when it's appropriate
to use macros, and in the process
| | 00:27 | familiarize you with the following features:
| | 00:30 | creating macros by recording keystrokes,
expanding a macro for more than one
| | 00:35 | task, making macros available to all
Excel workbooks, launching macros with
| | 00:41 | keystroke shortcuts and toolbar buttons,
editing VBA code and making sense of
| | 00:47 | that VBA environment, debugging and
testing macros via step mode and split
| | 00:52 | screens, and expanding the power of
macros with Do loops and If commands.
| | 00:59 | For many Excel users, macros
become a power tool with seemingly
| | 01:03 | unlimited capabilities.
| | 01:05 | I'll show you this power as
we get started with Excel 2010:
| | 01:10 | Macros in Depth.
| | Collapse this transcript |
| Using the exercise files| 00:01 | If you have access to the exercise
files for this course, you can put them on
| | 00:05 | the Desktop as I have--
| | 00:06 | I am about to open one here--
or anywhere else you want.
| | 00:09 | If you do not have access to these
files, you can follow along with your own
| | 00:13 | files as we proceed through the course.
| | Collapse this transcript |
|
|
1. Getting Started Understanding what a macro does| 00:00 | When using Excel, do you sometimes say
to yourself--or maybe even out loud--"here
| | 00:05 | I go again," as you repeat a frequently
used command sequence or maybe a series of
| | 00:09 | steps--something you do frequently?
| | 00:11 | If so, you need to learn how
macros can automate these steps.
| | 00:16 | Let's take a look at a few examples, and I
also want to suggest that, in a certain sense,
| | 00:21 | you've been using macros all along;
| | 00:23 | you just haven't thought about it in that
way, or you maybe haven't used that word.
| | 00:27 | Let's take a simple process here.
| | 00:29 | Maybe in this list here, when someone
leaves the organization, at least for a
| | 00:34 | while, what you don't do is remove the record.
| | 00:37 | You simply want to use strikethrough.
| | 00:40 | In other words, Madelyn O'Brien here has
left; I want to use strikethrough on these cells.
| | 00:45 | Most of you probably know
where to find that feature.
| | 00:47 | You'll go to the Home tab, and possibly
click the Dialog Box launcher right here.
| | 00:54 | You might know a shortcut by pressing
Ctrl+1, or you could right-click and
| | 00:59 | go to Format Cells.
| | 01:01 | The point is, it's a few clicks here and there.
| | 01:04 | You will track down this capability.
| | 01:06 | If you're going to Format Cells, you
might have to activate the Font tab if it's
| | 01:10 | not already active, and there is Strikethrough.
| | 01:14 | Now, I wouldn't suggest that a
lot of people will use that feature
| | 01:17 | frequently, but you could imagine if it
becomes pretty important to you, and you
| | 01:21 | use it a lot, you'd like to make it
faster. And you certainly can use a couple
| | 01:26 | of shortcuts like I suggested here.
| | 01:28 | Maybe click this next time, or press
Ctrl+1 to get into that dialog box a bit faster.
| | 01:34 | But it still is going to to take a few
clicks. And then one day somebody tells
| | 01:38 | you out of the blue "Oh, by the way, there is a keystroke
shortcut already built into Excel, Ctrl+5
| | 01:44 | and furthermore, it's a toggle; it's an on/off."
| | 01:46 | We don't call that a macro,
but in a certain sense it is.
| | 01:51 | It embodies the idea that a short
sequence, in this case a keystroke shortcut,
| | 01:56 | takes the place of those four or five
clicks that we just went through before.
| | 02:00 | It would be incorrect to suggest that
macros are simply about short, little
| | 02:05 | processes that are going to save you
five steps here or five steps there.
| | 02:10 | But we start with this idea that
anytime you want to take a series of steps or
| | 02:15 | actions and essentially turn them
into a single action, that's the starting
| | 02:20 | point for talking about macros.
| | 02:23 | At other times we do this as well too.
With so many different features, we
| | 02:27 | have shortcuts already.
| | 02:29 | If you didn't know Excel very well,
and you said, "I want to make this bold,"
| | 02:33 | I certainly couldn't imagine anybody
using Excel and not knowing about the
| | 02:36 | letter B, but if you were using the
Data tab for whatever reason, and you
| | 02:40 | couldn't find it, you'd be complaining a
little bit saying, "I got to go back here."
| | 02:44 | and "What's the sequence?"
| | 02:46 | You'd think out how many steps it
takes you to get to a certain feature.
| | 02:50 | Let's go into things that maybe are
not quite so well known and suggest more
| | 02:55 | ideas regarding macros.
| | 02:57 | Here is a worksheet called
Formulas, and it's a real mess.
| | 03:02 | It needs some cleanup, and maybe you
encounter worksheets like this from time to
| | 03:06 | time, and you need to get
your bearings kind of quickly.
| | 03:09 | In prior versions of Excel going way
back, it used to take quite a while to
| | 03:14 | figure out where the formulas are.
| | 03:16 | What I'm about to do here
is going to be fast and easy.
| | 03:19 | It probably doesn't require a macro,
but sometimes you get annoyed at processes
| | 03:24 | that even take only three or four steps.
| | 03:26 | How to we find all the
formulas in this particular worksheet?
| | 03:30 | On the Home tab, the extreme right
button called Find & Select, there is a
| | 03:35 | choice called Formulas.
| | 03:36 | It highlights all the formulas.
| | 03:38 | Now you might want to add a color to them.
| | 03:41 | That would take another step.
| | 03:42 | Make them yellow or something like that if
you wish. But that's certainly a handy feature.
| | 03:46 | If you use it often, does it bother you
that you always have to go to the Home
| | 03:50 | tab, then click that button, then
make the choice, then add the colors?
| | 03:55 | Well, certainly the steps that we just
went through could be turned into a macro.
| | 03:59 | So what I'm suggesting here, at least
initially, is the idea that any sequence of
| | 04:05 | actions could be turned into a
macro by a process known as Recording.
| | 04:11 | We can turn on a recorder and go
through those various steps manually while
| | 04:15 | they're being stored for us.
| | 04:17 | Then in the future, we can either use
a keystroke shortcut or a new button in
| | 04:23 | the Quick Access Toolbar to actually
execute that sequence with a single action.
| | 04:29 | I'm going to click Ctrl+Z to undo this.
| | 04:32 | A companion feature to this
would take considerably longer.
| | 04:36 | I'm not necessarily suggesting this is
a feature that everybody needs, but it
| | 04:40 | certainly could be valuable in some
worksheets if you say, I would like to
| | 04:44 | highlight all of the cells that just
have pure values in them. That cell's got
| | 04:49 | a pure number in it.
| | 04:50 | So does this cell.
Not that cell, that's got a formula.
| | 04:54 | We already know how to find those.
| | 04:56 | So how do we find the
cells that have pure values?
| | 04:59 | It's important in both of these
examples that you click on just one cell.
| | 05:04 | If you highlight two or three or
five, the search will only take place
| | 05:08 | within those cells.
| | 05:10 | So we click on the single cell here,
and again on the Home tab, Find & Select.
| | 05:15 | This time the not so
obvious choice, Go To Special.
| | 05:20 | We see a choice here called Constants,
but that will also select text and
| | 05:25 | logicals and errors, so then we
would uncheck Text and Logicals and
| | 05:30 | Errors, and click OK.
| | 05:32 | What do we have highlighted?
Just values. And here, too,
| | 05:37 | if you would like to apply a color, go do that.
| | 05:40 | So the issue is, is this the kind of
sequence that maybe you've already been
| | 05:44 | performing from time to time, and you
start to use it more frequently, and it's
| | 05:49 | six or eight or 10 steps--
| | 05:50 | we're not really counting here--
| | 05:52 | you say, I wish I had a faster way to do that.
| | 05:55 | That's the starting point
for certain kinds of macros.
| | 05:58 | For want of a better term, perhaps we
would call these "utility macros," but that's
| | 06:03 | certainly one thought process that we have here.
| | 06:06 | But let's suggest another idea as well.
| | 06:09 | Suppose a couple of times a week it's
important for different people in the
| | 06:13 | organization to get a list of the
employees and who they are and where
| | 06:18 | they work and so on.
| | 06:19 | Maybe every time you do this you sort
the list first by Department, and then you
| | 06:24 | sort it again by Employee Name.
| | 06:26 | There are a few people that need that list.
| | 06:29 | Maybe another thing you need to do is
to use a filter on this list and show
| | 06:34 | only the hourly people.
| | 06:36 | Now I'm not just suggesting make-work projects.
| | 06:38 | I'm suggesting these are things
that you do on a regular basis.
| | 06:42 | Maybe you do that twice a week.
And it doesn't take that long really, but it's
| | 06:46 | five, ten minutes, whatever.
| | 06:47 | What if you could click a button,
and have all those steps essentially run for you?
| | 06:53 | It's going to save you some time.
| | 06:55 | That's the idea behind macros.
| | 06:58 | Initially, we want to focus on the
idea that any actions that you take in
| | 07:02 | Excel could be recorded so that the
next time you need to use them, those
| | 07:08 | actions happen all at once.
| | 07:10 | It even can be set up in such a way that
no matter how big this list becomes, or
| | 07:15 | whether it shrinks or grows,
the macros will still work.
| | 07:19 | So we're giving you some
ideas for the rationale of macros.
| | 07:23 | Why do I need macros?
| | 07:25 | To automate your work and save
some time--and sometimes lots of time!
| | Collapse this transcript |
| Reviewing macro examples| 00:00 | Before actually creating some macros,
it's worthwhile to see some more examples
| | 00:06 | to deepen the rationale as to why
we want to use this great feature.
| | 00:10 | In this particular worksheet, you
see these numbers in millions--nothing
| | 00:13 | wrong with this at all.
| | 00:15 | Many people who deal with large numbers,
however, might want to use a different format.
| | 00:19 | I'm just going to highlight these
cells here For example, in cell E5 here,
| | 00:24 | how about that being display as 415.1?
| | 00:27 | That sounds like something easy to do.
| | 00:31 | I'm going to do it manually here by
simply right-clicking, going to Format
| | 00:36 | Cells. And the solution is not one of
the built-in number formats, but you need
| | 00:40 | to go to Custom--and actually you can
start with almost any one of these--and
| | 00:45 | alter it to include a .0 and,
believe it or not, comma, comma.
| | 00:51 | And that will display these values in
millions with a single decimal place,
| | 00:55 | and you could easily imagine someone
wanting to have that at his or her figure tips.
| | 01:00 | And we'll be showing you how you can
have macros be executable, or you can
| | 01:04 | make them happen, with a single keystroke
shortcut or button in the Quick Access Toolbar.
| | 01:10 | Another feature, one that I don't use a
whole a lot--but take a look at this idea
| | 01:14 | here with this data here.
| | 01:17 | Double underlining is a feature that
not everybody needs, but nevertheless on
| | 01:22 | the Home tab you will see an
underline button--and some of you use double-
| | 01:26 | underlining--that's one
kind of double underlining.
| | 01:29 | There is another kind called double
accounting. In this example here, I'm not
| | 01:34 | making the case for saying everybody
needs this, but if you need this, you'll
| | 01:38 | right-click, go to Format Cells, go to
Font tab, and choose Double Underlining.
| | 01:45 | The only difference between two of
these, by the way, is how close it puts
| | 01:48 | the lines to the texts.
| | 01:49 | But the idea is it's a feature that you
need, and if you do, you get annoyed at
| | 01:55 | the fact that takes to long to get here.
| | 01:58 | Now we have already suggested some
other macros, for example, a list here, the
| | 02:03 | idea that we might frequently sort
this and might hide column along the way,
| | 02:07 | we might filter it. If it's a process that
we use often, we will definitely want to
| | 02:12 | use macros to record those processes,
and then the next time we need them, just
| | 02:17 | execute them quickly.
| | 02:19 | But here is another list.
| | 02:20 | This is about 2,400 rows.
| | 02:23 | It's a list of realtors from one state.
| | 02:26 | We've got six rows of information about each
person here. And the data is great. We need it.
| | 02:31 | It's going to help us enormously.
| | 02:33 | It's not in the form that we want,
however, and try as we might, they won't send
| | 02:37 | it to us the way we want.
| | 02:38 | What are we trying to do here?
| | 02:40 | You could easily imagine the typical
Excel users saying "I would like to get all
| | 02:44 | the names, for example, in column D,
get those name over there, put the company
| | 02:49 | names right here maybe, put the
business addresses right here." And as you look
| | 02:54 | at the data, a good Excel user will
probably think out three or four different
| | 02:58 | ways, maybe, to do this.
| | 03:00 | You might use a filter.
| | 03:01 | You might just start copying the data.
What you wouldn't recognize at first
| | 03:06 | though is the following idea,
and that's a new idea here.
| | 03:10 | If you were to highlight these cells,
either one by one or in clusters, and copy
| | 03:16 | the data, for example, over here, so it
will look like that and then do the same
| | 03:22 | thing with the next set of
names and the next set of names,
| | 03:27 | you could do that in the certain way
while recording your actions. And then, and
| | 03:32 | this would be a huge leap
and you wouldn't make this intuitively.
| | 03:36 | you could essentially say, let's take
these actions and perform them over and
| | 03:42 | over and over throughout
this entire list of data here.
| | 03:46 | And again, that's the kind of thing
that macros can do that you don't quite
| | 03:51 | pick up on at first.
| | 03:52 | If we simply confine the idea of a
macro, it only picks up recorded actions.
| | 03:58 | You wouldn't make this leap.
But this macro that we're talking about here
| | 04:02 | has been written. We'll show you how a bit
later, and here is a button to make it happen.
| | 04:07 | 2,400 rows. Watch what happens. And it's
not going to take very long, and there is
| | 04:15 | way also to not show this interim
activity, and it will happen even faster.
| | 04:21 | When you start to see some examples like
this, I thing there is a little doubt as
| | 04:25 | to why you need to know more about macros.
| | Collapse this transcript |
| Understanding security| 00:00 | Even before grasping the full meaning
of what a macro is, you need to know about
| | 00:04 | the security issues associated with macro usage.
| | 00:08 | Currently on the screen I'm using a
file that doesn't have any micros in it.
| | 00:11 | It's called Profits.
| | 00:12 | You might notice its file
extension, and depending upon your display
| | 00:16 | settings, you might not be seeing a
file extension yourself. But the standard
| | 00:20 | file extension in Excel files in 2010 is .xlsx.
| | 00:26 | I'm about open another file.
| | 00:28 | This file is called Large Macro Example.
| | 00:32 | You will notice its file extension,
xlsm. That means it has macro in it.
| | 00:37 | I'm going to open this simply by
double-clicking, and here is a prompt:
| | 00:42 | "Microsoft office has identified a
potential security concern," and the warning sounds
| | 00:48 | rather dire. But I think lot of you
know, and you been warned by your IT
| | 00:52 | departments in many cases, that
macros can be a repository for viruses.
| | 00:57 | So the default notice in Excel will
appear like this when you open a file
| | 01:02 | that has macros in it.
| | 01:04 | We certainly like to run
macros, so we'll enable the macros.
| | 01:08 | Of course you do have the other choice.
And if you know this file, of course
| | 01:12 | you're going to be choosing enable macros.
| | 01:14 | Now if you know this file well, if you
store it in a particular location, you're
| | 01:19 | very familiar with it and no
body else has accesses to it,
| | 01:22 | you are going to get a little tired of
that notification all the time. And using
| | 01:26 | the File tab in the menu, we can go to
Options, and then on the left-hand side,
| | 01:34 | Trust Center. We've got some control
here by then clicking on Trust Center
| | 01:40 | settings and then, on the left side,
Trusted Locations. If a file comes from one
| | 01:47 | of these locations, and I believe these
are the defaults you get at start-up, and you
| | 01:51 | can change them or get rid of some
of them if you think that's necessary.
| | 01:55 | But you can add trusted
locations, meaning paths here, folders.
| | 02:01 | We can add new locations, so when
we open files from those location,
| | 02:05 | we're not going to be getting the prompt.
| | 02:07 | Add a new location, and I was strongly
recommended here that you use browse.
| | 02:13 | So I'm going to find the location
where some of the files are that I need.
| | 02:17 | It's in a folder called Exercises.
| | 02:20 | Now that does have other folders within
it. And possibly in some situations you
| | 02:25 | might want to treat some of these
folders as Trusted Locations but not others.
| | 02:30 | I hardly doubt that, so what might we
do here? I'm pressing backspace here.
| | 02:35 | We can choose the exercises file
folder. Now, that by itself doesn't mean that
| | 02:41 | all the subfolders are trusted as well, so
you do want to click the box here to the left:
| | 02:46 | Subfolders of this location are also
trusted. So there is the path right there.
| | 02:52 | That's the trusted location, and
all the subfolders of that are also
| | 02:56 | trusted. Click OK, OK, OK.
| | 03:00 | I will close this file and open it
again. I could just do it either through
| | 03:07 | File > Open, or the Recent File list.
| | 03:10 | There it is right there.
Open this file, no questions I asked.
| | 03:13 | We're ready to run the macro or do
with this file what we wish without
| | 03:18 | getting that prompt.
| | 03:19 | So there are some security concerns.
You want be aware of the idea, and perhaps
| | 03:24 | many of you working with your IT
departments have been told, about the security
| | 03:28 | concerns associated with opening
files that have macros in them.
| | 03:33 | Unlike many Excel features, security
concerns here, as we have seen, are much more
| | 03:37 | important when we talk about macros.
| | Collapse this transcript |
| Reviewing the recording method| 00:00 | The best way to get started creating a
macro is by recording your actions.
| | 00:05 | Even power-macro users will use this is a
fallback method for creating a macro.
| | 00:11 | In this particular worksheet, we've
decided it's important to us. We are also
| | 00:15 | thinking how important it might be
in other worksheets here as well.
| | 00:19 | We'd like to highlight just the
cells that have pure values in them.
| | 00:23 | A quick reminder of how we do this: on
the Home tab, the rightmost button, Find &
| | 00:29 | Select, has an option called Go To
Special and by clicking Constants and then
| | 00:34 | unchecking the boxes for Text,
Logicals, and Errors and then clicking OK,
| | 00:40 | we have highlighted only the
cells with pure values in them.
| | 00:44 | We could then apply a color to these.
But we'd like to have that happen
| | 00:48 | instantaneously because we've identified
it is something we'd like to use often,
| | 00:54 | and we want it to be fast.
| | 00:56 | If we highlight a few cells and go
through those steps, by the way, we will only
| | 01:00 | be selecting the cells within this
range. By clicking on a single cell, we are
| | 01:04 | about to check the entire worksheet.
| | 01:08 | So how do we create a macro by recording?
| | 01:11 | In the lower-left corner of your screen,
you'll see the word "Ready," and to the
| | 01:15 | right of it you might or
might not be seeing a button.
| | 01:18 | I'm not seeing a button here,
| | 01:20 | so I'm going to right-click in that
status bar. And you'll notice a lot of
| | 01:24 | selections here, including
one called Macro Recording.
| | 01:28 | By checking this option, you see the
check in front of it, and also at the bottom
| | 01:33 | of the screen, we now have a button.
| | 01:36 | This is one of two major
ways to start recording a macro.
| | 01:40 | The current message says, "No
macros are currently recording.
| | 01:44 | Click to begin recording a new macro."
| | 01:48 | Another way to start macro recording is
to click the View tab in the Ribbon and
| | 01:54 | the rightmost button called Macros.
| | 01:56 | There is a drop arrow there, Record Macro.
| | 02:00 | A third way is adding a new tab
in the Ribbon called Developer.
| | 02:05 | We don't really need to do
that, but that's another way.
| | 02:07 | It will give us some buttons up here
for starting a macro recording as well.
| | 02:12 | We can use either of the two
techniques I first alluded to.
| | 02:15 | We've identified the need,
possibly we've written down the steps;
| | 02:18 | we are ready to do recording.
| | 02:21 | In the lower-left corner, I am
going to click that button, and here's a
| | 02:24 | dialog box, Record Macro.
| | 02:27 | We need to give the macro a name.
| | 02:29 | We always represent it with the default
name, but we want it to be meaningful.
| | 02:32 | That name can be pretty
free-form with a few obvious exceptions.
| | 02:37 | It cannot begin with a number.
| | 02:39 | It cannot contain spaces.
| | 02:42 | If you'd like to simulate different
words and make it looks somewhat like a
| | 02:45 | sentence, that's fine, but use the
underscore character or simply shift between
| | 02:49 | upper- and lowercase.
| | 02:51 | Ideally we want this to
describe what the macro does.
| | 02:55 | As you see more and more macro, many,
many times, a good name helps define and
| | 03:01 | describe what the macro dose.
| | 03:03 | Why not "HighlightValueCells?"
| | 03:04 | I am not using underscore.
| | 03:09 | I am just shifting case here.
| | 03:10 | A keystroke shortcut is extremely
valuable, and it's a fast way to run a macro
| | 03:15 | later--make it happen. But we have only
52 choices here: any of the lowercase
| | 03:20 | letters, any of the uppercase letters.
And if I were to use the letter C here,
| | 03:25 | thinking of the word "cells," I would not
be able to use Ctrl+C for copy anymore.
| | 03:30 | So there's a tendency among some users
to simply use the Shift key because these
| | 03:34 | combinations are not widely used.
| | 03:36 | So I am holding down the Shift key
here, and maybe I'll use the letter H. I am
| | 03:39 | thinking of the word "highlight."
| | 03:41 | You can't always come up with the
letter that you always want here.
| | 03:44 | Ctrl+Shift+H will be the way that
we make this macro happen later.
| | 03:49 | We want to store it in this workbook.
| | 03:51 | There are some other options here, which
we'll see a bit later, and describe the macro.
| | 03:56 | Ideally, you want to provide this
because the more macros you see--as you're
| | 04:01 | looking at code, rather than trying to figure
out what's going on--you want a description.
| | 04:06 | We want to say also here we are on the
verge of creating a macro, and what that
| | 04:11 | really means is whatever actions we take
are going to be recorded so we can play
| | 04:16 | them back, but they actually get
converted into a language called Visual Basic
| | 04:21 | for Applications, VBA. More about that later.
| | 04:24 | The description here what I often
do is simply copy the name, make some
| | 04:28 | adjustments, maybe expand it a little bit.
| | 04:30 | I just press Ctrl+C. Down here I'll
press Ctrl+V to paste. Maybe do this:
| | 04:35 | "HighlightValueCells," and maybe we'll
just add the phrase "in this worksheet"
| | 04:40 | because that's what it mean,
of course, only this worksheet.
| | 04:46 | As we click OK, a quick reminder here:
we are on the verge of saying let's
| | 04:51 | record everything until we stop recording.
| | 04:54 | So as I click OK, and you will notice
in the lower-left corner of the screen,
| | 04:58 | the previous icon has been replaced with
a box: "A macro was currently recording.
| | 05:04 | Click to stop recording."
| | 05:06 | Nearly everything we do will be recorded.
| | 05:09 | Now if I do scrolling like this and--
maybe that was a mistake--that's actually
| | 05:13 | going to be in the macro. It's so innocuous
| | 05:16 | we'll never know it anyway, but you
want to stay focused and do only the things
| | 05:20 | that you want to have recorded.
| | 05:22 | If you happen to enter a dialog box, if,
for example, if you were to right-click
| | 05:26 | on a cell and press Format
Cells--anything you do here--
| | 05:30 | if you press Cancel, it is meaningless.
| | 05:33 | So those kinds of actions don't appear
in the Macro; nearly everything else does.
| | 05:38 | So what do we want to do?
| | 05:39 | Exactly what we did before,
Home tab > Find & Select > Go To Special >
| | 05:46 | Constants, uncheck Text,
Logicals, Errors, click OK.
| | 05:51 | Let's apply color; maybe we'll make
it green. And although its not critical,
| | 05:56 | right now these cells are highlighted,
and they are in color. It's a convention
| | 05:59 | to click outside, or maybe we'll just
click on cell A1. And they are no longer
| | 06:03 | highlighted, but the color is there.
| | 06:05 | We are done recording. Lower-left corner,
| | 06:08 | there is a box. Click it.
| | 06:10 | We have stopped recording.
| | 06:12 | We have created a macro.
| | 06:13 | It's fast, it's easy. And let's test it.
| | 06:17 | Now something you'll learn early on when
you run macros: you cannot use the Undo
| | 06:23 | feature to undo what you just did.
And that makes us think about macros might be
| | 06:28 | deleting data--and we want to stay
away from those for a while. But since we
| | 06:32 | went through the action of recording
these, maybe the best thing to do if we
| | 06:36 | want to test this is go to a
different worksheet, over to Profits here.
| | 06:39 | It looks like there's some color already there.
| | 06:41 | Let me take it of manually. Click here, No Fill.
| | 06:47 | Are there cells in this
worksheet that contain values?
| | 06:49 | Well, we know there are, but where are they?
| | 06:51 | We'll use that keystroke shortcut.
| | 06:54 | I use Ctrl+Shift+H.
It highlights these cells in color.
| | 06:58 | We could go to different
worksheets and try this.
| | 07:01 | How about over here? Any cells with pure
values? Ctrl+Shift+H. We see that it's working.
| | 07:08 | The macro was recorded when using the
formula sheet. But it's not stored here;
| | 07:13 | it's stored in this workbook, so it
can be used anywhere. And we've just
| | 07:17 | demonstrated the relatively
easy process of recording a macro.
| | 07:21 | It's going to be the dominant,
but certainly not the only, method of
| | 07:25 | creating macros.
| | Collapse this transcript |
| Understanding other techniques for macros| 00:00 | In this workbook a macro has been run,
and its purpose was to highlight
| | 00:04 | just the cells that have pure values
and make them green, and that helps us
| | 00:09 | understand the workbook.
| | 00:10 | The method used for creating the
macro was recording, the dominant way to create
| | 00:15 | macros. But there are other techniques,
and we need to see, from time to time,
| | 00:20 | the actual macro code.
| | 00:22 | When you record a macro, you
actually create a small program.
| | 00:27 | Well, let's take a look at it.
Where is it? How do we find it?
| | 00:31 | On the View tab in the Ribbon, the
right-most button called Macros, the drop
| | 00:36 | arrow, the choice View Macros.
| | 00:39 | This will show us the macros in
this workbook, and potentially others.
| | 00:42 | The macro we're talking about here you
can guess by the name, Highlight Value Cels.
| | 00:47 | There is another macro that
displays values in millions.
| | 00:50 | Let's look at the code.
| | 00:52 | When we create a macro by recording, we
create a small program in the language
| | 00:57 | called VBA, Visual Basics for Applications.
| | 01:00 | Let's edit this. Here is the code.
We're in a different window with the Visual Basic
| | 01:06 | editor. This is the macro that
performs the action of selecting cells that
| | 01:12 | have values in them.
| | 01:13 | During the recording of this, I did
scrolling, and that appears in the macro.
| | 01:18 | Now it doesn't hurt anything.
| | 01:19 | You don't see when a macro runs.
But these are the kind of things that
| | 01:22 | happen sometimes, and just to make the
point here, we're going to get rid of this.
| | 01:26 | We'll run the macro again perhaps
later. It won't make any difference at all.
| | 01:30 | It's still going to work just fine.
| | 01:32 | What do we need to know about this?
| | 01:34 | Well we worry about that later. But we
might just want to make a small change
| | 01:37 | for the sake of making a change.
| | 01:39 | To emphasize the idea increasingly over
the time, as you work with Visual Basic,
| | 01:45 | as you create more and more macros,
you'll get more familiar with the code and
| | 01:49 | actually make some changes.
| | 01:51 | Now it's a small matter to say, when this
macro ends, the active cell goes to A1.
| | 01:56 | We want it to go somewhere else.
| | 01:58 | All I'm suggesting here is we're going
to make a change, and simply by putting in
| | 02:04 | the zero here, the next time we run
this macro the active cell will go to cell
| | 02:08 | A10. That's just to prove the point.
We are not going to change anything else.
| | 02:12 | It's a reminder that you have control
over this environment, and the more that
| | 02:16 | you eventually learn about
VBA will have some impact.
| | 02:20 | People who know VBA well are more
likely to be typing some of these commands--
| | 02:25 | not necessarily these, but
certain commands and the actions.
| | 02:28 | And a bit later we will see that there are
certain structures within VBA that you'll type.
| | 02:34 | In this particular workbook is another macro.
| | 02:37 | It's located in what we call a
module. Then over on the left side I am
| | 02:40 | double-clicking here. And here is
another small macro. It simply displays
| | 02:44 | the values in millions.
| | 02:46 | If, for example, though you want to
alter this macro to also apply color to
| | 02:52 | the cells when you apply this format,
then you could easily re-record this.
| | 02:57 | But another idea here, I'm going to go
back to the other module in the left-hand
| | 03:01 | side and highlight this.
| | 03:04 | Now you don't know the details of
what each line is doing here, but this is
| | 03:08 | the portion of the other macro--
and it wouldn't take long to figure it out--
| | 03:11 | that applies the color.
| | 03:12 | So what we're going to do with this?
We're going to copy this code, right-click >
| | 03:17 | Copy, go back to the other module, and
right here after applying the number
| | 03:22 | format, right-click > Paste.
The next time we run this macro,
| | 03:26 | it'll not only apply numeric format,
but it'll also apply a color to the cell.
| | 03:32 | We're opening the door to the idea that
although recording will be the dominant
| | 03:37 | method of creating macros,
| | 03:39 | there will be times when you step into
the Visual Basic code to make changes.
| | 03:43 | There will be other times when you
copy code from one macro to another.
| | 03:47 | As we jump back into Excel here--
and we can do this from the menu at the top,
| | 03:52 | File and close and return to Microsoft Excel--
| | 03:55 | we could go right back in here.
| | 03:57 | We could run those macros again.
| | 03:59 | But we're introducing the idea that
recording, although the dominant method, is
| | 04:03 | not the only way that we
create and alter macro code.
| | Collapse this transcript |
|
|
2. Running a Macro (Execute, Play Back, Etc.) Running macros from the View tab| 00:00 | Once you have created a macro, no
matter how, you need to run it at
| | 00:04 | different times. And it just so
happens sometimes you forget that there is a
| | 00:08 | keystroke shortcut.
| | 00:09 | You don't know if there
is, or maybe you forgot it.
| | 00:12 | It doesn't have a button associate with it.
| | 00:13 | How do we track down our macros?
| | 00:16 | You might later assign the keystroke
shortcut. But let's imagine we want to
| | 00:19 | run a macro that displays these values here.
Maybe you're only thinking about the first quarter.
| | 00:25 | We want these values displayed in millions.
| | 00:28 | We've already written a macro to do that.
| | 00:29 | The macro will display these
values in millions with a single decimal,
| | 00:33 | a lot easier to read.
| | 00:35 | But we have forgotten the keystroke
shortcut. What are we going to do?
| | 00:38 | On the View tab, go to the
Macros button. Choose View Macros.
| | 00:44 | We don't care about seeing the code or
making any changes. Don't worry about the
| | 00:47 | keystroke shortcut now.
We simply want to run this macro.
| | 00:50 | Well, here it is values in millions.
| | 00:53 | We could either double-click
that or highlight it and then click Run.
| | 00:58 | We've run the macro. That's our
fallback method. And the other reason it's
| | 01:01 | good to go to that location is it
does remind you which macros are in this
| | 01:07 | workbook, or possibly others. So viewing
the macros, and even though I have only
| | 01:12 | two here right now,
| | 01:13 | nevertheless it will help you to
figure out where the macros are, and we can
| | 01:16 | also come here for other reasons, such
as assigning keystroke shortcuts and
| | 01:20 | actually jumping into the code as well.
| | 01:22 | So it's a valuable tool for keeping
track of macros, and when necessary actually
| | 01:28 | running macros--although
it's not the fastest way.
| | Collapse this transcript |
| Running macros from a keyboard shortcut| 00:00 | The fastest way to activate or run a
macro is by way of a keystroke shortcut.
| | 00:05 | There are some problems
though, associated with this idea.
| | 00:08 | Many times you want to use a meaningful
letter for your keystroke shortcut.
| | 00:12 | So maybe you've got a macro like one you
might have seen here that activates a
| | 00:17 | macro that displays numbers in
millions with a single decimal.
| | 00:21 | Ctrl+Shift+M. There it is, not bad.
But you surely can't count on every feature
| | 00:28 | that you want to have
that letter available to you.
| | 00:31 | And so there is a tendency over the
time to maybe drift away from using
| | 00:35 | keystroke shortcuts.
| | 00:36 | I often use them when I'm testing
macros, and then later I don't bother with
| | 00:40 | them. But it is a time-honored method.
| | 00:42 | But you want to know how to change
them or possibly get rid of them or
| | 00:46 | maybe even add them if you forgot to assign a
keystroke shortcut when the macro is recorded.
| | 00:52 | On the Macros button, in the View tab,
the choice View Macros allows us to view
| | 01:00 | the code of the macros and also by
clicking the Options tab, to either assign the
| | 01:06 | keystroke shortcut, remove it, or
change it to be something else.
| | 01:11 | We don't see the list of which
other shortcuts are currently in use, so
| | 01:15 | sometimes we have to go back and forth
looking at other macros to see what their
| | 01:19 | key stroke shortcuts are.
| | 01:21 | And there is also this concern:
Am I using a keystroke shortcut
| | 01:25 | that's already in use?
| | 01:27 | If you use Ctrl and Shift when you
create this, that's usually not a problem.
| | 01:32 | But on sheet 1 is a list of the
commonly used Ctrl key combinations.
| | 01:37 | In Excel 2010, for example, Ctrl+C is
copying, Ctrl+X is cut--those are widely
| | 01:43 | known--Ctrl+V is paste, and so on.
| | 01:46 | Now if you never Fill Right--I don't--
then you can use Ctrl+R for a macro if
| | 01:52 | that makes sense to you.
| | 01:54 | Ctrl+Q is wide open;
it doesn't have any assignment.
| | 01:56 | Use Ctrl+Q or use Ctrl+E. It's your
call on what is you want to use, but do
| | 02:01 | remember if you use one of these
combinations here, you're overriding its other
| | 02:07 | use, and that may not be what you want.
| | 02:09 | That's why a lot of people
automatically use the Shift key when they're
| | 02:12 | making assignments.
| | 02:14 | So once again, from the Macro button
on the View tab, this is where you, by
| | 02:20 | clicking the appropriate macro and
then choosing Options, make those changes.
| | 02:24 | But keystroke shortcuts are a
time-honored way. It's fast, and it's easy.
| | 02:27 | It's just a question,
| | 02:29 | sometimes you forget this
keystroke shortcut or that one.
| | Collapse this transcript |
| Running macros from the Quick Access toolbar button| 00:00 | For macros that you run frequently,
you might want to decide to have a new
| | 00:05 | button in the Quick Access
Toolbar at the top of your screen
| | 00:09 | in addition to a keystroke shortcut, or
instead of a keystroke shortcut. We also
| | 00:13 | want to introduce the idea that if you
plan to use a macro in many different
| | 00:18 | workbooks, we store the
macro in a different location.
| | 00:21 | This not a course in using functions,
but if we look in column B here,
| | 00:25 | something interesting is going on.
| | 00:27 | The code in column B uses the column A
data, and it gets rid of this unnecessary
| | 00:33 | spaces like we see in row 7
here, for example, and here, too.
| | 00:37 | It also uses proper case to clean up the data.
| | 00:40 | But when you work with formulas that
clean up data--typically in an extra column
| | 00:45 | like this--eventually what you want to
do is to turn these into the pure values,
| | 00:51 | in other words, the results.
| | 00:52 | We would like to have this cell, the
first one, simply consist of Baxter, Donna.
| | 00:57 | No formulas, throw all
that away. Keep the results.
| | 01:00 | And many of you know how to
do this multi-step process.
| | 01:04 | We are doing something similar in
column E, but over here a different function
| | 01:08 | is being used. And what's happening here?
| | 01:09 | It's simply turning all the
letters into uppercase letters.
| | 01:12 | Column I is doing something little
more sophisticated, or actually
| | 01:15 | manipulating the code, and column I
is doing something even more involved
| | 01:19 | manipulating the code.
| | 01:21 | But in all three cases, what we would
like to be able to do is to take these
| | 01:25 | results and essentially
convert them into their values.
| | 01:29 | Let's record a macro that does this,
and we want to store this in such a way
| | 01:32 | that it's always going to be available,
and then we will make it accessible by
| | 01:36 | way of a new button in the Quick Access Toolbar.
| | 01:39 | A lot of macros are based on the concept
that you have already selected the data
| | 01:45 | before you want the macro to do its work.
| | 01:48 | So we are going to highlight these
cells here to record the macro, and we are
| | 01:52 | thinking, we could use this macro not
only in this worksheet but in other
| | 01:58 | worksheets in this workbook
and in other workbooks as well.
| | 02:02 | So let's say we've got this data highlighted.
| | 02:04 | The steps we want to take, we've
written them down, or we know what
| | 02:08 | they're going to be.
| | 02:09 | They are going to be off the Home tab.
| | 02:11 | It's going to be a Copy and
then a Paste Special values.
| | 02:14 | So if we have either written that down, or
we know that. We are ready to record a macro.
| | 02:19 | Bottom of the screen, we click the
button. No macros are currently recording.
| | 02:23 | Let's record, and we will call this PasteValues.
| | 02:32 | And this time instead of storing them in
this workbook, we want to store them in
| | 02:35 | personal macro workbook.
| | 02:38 | Now this is a special workbook that
at the moment might not even exist.
| | 02:43 | The very first time that you record
a macro and indicate that you want to
| | 02:47 | store it in a personal macro workbook,
you're setting up the steps to actually
| | 02:51 | create this workbook.
| | 02:53 | It's a workbook that from now on will
always open every time you open Excel, and
| | 02:58 | it will be the repository for macros
that you want to have available no matter
| | 03:03 | which workbooks are open.
| | 03:05 | We could give it a keystroke shortcut,
but we are more interested this time in
| | 03:08 | showing that we want to use it by
way of a button. So we could give it a
| | 03:12 | keystroke shortcut, but I won't.
And I could get to the description later.
| | 03:17 | So we are about to click OK to
record this. So what do we do?
| | 03:21 | We are trying to turn these
results into their values.
| | 03:24 | We will use the Copy button and then
Paste > Values. And then you can see what's
| | 03:31 | happened in cell B1. That's now Baxter, Donna.
| | 03:35 | A minor point here, we will press Escape to
remove the marquee lights. The macro is done.
| | 03:40 | We will click the button right here.
| | 03:44 | We don't have a keystroke shortcut;
| | 03:46 | we have to fall back on our other method of
going into the View tab to run this. Let's try this.
| | 03:51 | Let's highlight the data here.
| | 03:53 | These are different formulas here, but we
want to turn them into their values as well.
| | 03:57 | We will use the View tab, the Macros
button, View Macros. And where are we are looking?
| | 04:04 | We are looking for macros in
the PERSONAL macro workbook;
| | 04:09 | there it is PasteValues. Let's run this.
| | 04:12 | Obviously that's not the fastest way,
but it proves that our macro was set up,
| | 04:17 | and we are ready to use it.
| | 04:18 | Now, let's add a button to
the Quick Access Toolbar.
| | 04:22 | The Quick Access Toolbar
might appear above the Ribbon.
| | 04:26 | You have the option of putting it
below it. Perhaps you have done that. But in
| | 04:29 | either case, we want to right-click on
the Quick Access Toolbar and customize it.
| | 04:35 | And in this dialog box called Excel
Options, you will see Choose commands from >
| | 04:42 | Popular commands.
| | 04:43 | Click the drop arrow. We want to
choose Macros, and here's the macro
| | 04:50 | in question, the one called PasteValues.
| | 04:53 | Let's add this to the Quick Access Toolbar.
| | 04:56 | Now you might or might not like that icon there.
| | 04:59 | If you don't, click Modify, and you've
got 181 choices out here. And I doubt if
| | 05:06 | any particular one here suggests for
what we are doing, so it's just a question
| | 05:09 | of what you like and something looks
a little different from other things.
| | 05:13 | So I will just pick one at
random here, maybe this one.
| | 05:17 | Some people might be looking for a number
if they're thinking of values meaning numbers.
| | 05:22 | But in this case, the values don't
necessarily mean numbers; they could mean
| | 05:25 | text. So pick whatever you want.
| | 05:26 | I will pick this one. Click OK.
| | 05:30 | More important than the icon is the
fact that if you gave it a sensible name
| | 05:34 | like PasteValues, this is the name
you'll see in the pop-up when you actually
| | 05:38 | see the button in the Quick Access Toolbar.
| | 05:41 | So I will click OK and this column I
data out here, this rather long set of
| | 05:47 | formulas here, we want to turn those
formulas into their results, we want to do
| | 05:52 | that PasteValues, we want to run that macro.
| | 05:55 | We now have this button in the Quick
Access Toolbar up there, PasteValues.
| | 06:00 | We will click it, and what do we do?
| | 06:02 | We turn these values into the results.
| | 06:06 | Every time we need to do this--and maybe
we need to do it a lot--we've got this
| | 06:10 | button up here in the Quick Access Toolbar.
| | 06:13 | Now maybe it's not quite as fast as
the keystroke shortcut, but it's here in
| | 06:17 | definitely. And every time we slide
across, it will remind us to what it does.
| | 06:23 | So it's a valuable way to run a
macro. And for those kinds of macros that
| | 06:27 | you're likely to run frequently, assign--
as we did here--a button to it in that
| | 06:33 | Quick Access Toolbar.
| | Collapse this transcript |
| Running macros from an object or clip art| 00:00 | If a macro is designed to be used by
others, or is designed to work only in
| | 00:05 | specific worksheets, you might want to
consider activating them by clicking a button.
| | 00:10 | And that's the case in this
workbook right here called Phone List.
| | 00:14 | When we put the mouse right on Sort by
Name, you see the pointer there. It's going
| | 00:18 | to actually sort these by last name, too.
| | 00:20 | Notice it does that. At another time you
might want to sort this list by department.
| | 00:25 | The macros associated with the Sorting
buttons are actually in this workbook,
| | 00:31 | and we can create buttons.
| | 00:35 | We can use clip art and make them
be the starting point for macros.
| | 00:39 | Let's assume that people using this
don't know how to sort data in Excel.
| | 00:44 | But this makes it simple and easy for them.
| | 00:46 | So how do we set this up?
| | 00:47 | What I'm going to do is hold down the
Ctrl key and simply click one of these,
| | 00:51 | for example Sort by Name, and then delete it,
and show how we can create a button like this.
| | 00:56 | So I want a button right
here, just select the one we saw.
| | 01:00 | Quick look here on the View tab first
though to remind us--because I did say it
| | 01:04 | and didn't show it to you--
| | 01:05 | the View tab, Macros button by Viewing
Macros, we can see the macros in this
| | 01:12 | particular workbook.
| | 01:14 | There is a macro called SortByDepartment.
There is macro called SortByName. They are in this workbook.
| | 01:21 | We would like a button here
in column A. Where do we go?
| | 01:25 | Insert tab > Shapes. Use any shape you want.
Sometimes you'll see these maybe done
| | 01:31 | whimsically with a smiley
face. There is a heart shape.
| | 01:35 | There all called kinds of buttons,
explosion-type buttons, 32- 16-point
| | 01:40 | stars, all kinds of stuff.
| | 01:41 | I'll be a little conservative here
and just use rounded rectangle.
| | 01:46 | Draw it. If you want to look so-so,
hold down the Alt key, drag these edges.
| | 01:52 | They line up perfectly with the cell
boundaries. Not critical, but some
| | 01:55 | people love that idea.
| | 01:56 | Hold down Alt to do that.
| | 01:58 | We can typewrite on it from
the beginning "Sort by Name."
| | 02:04 | And let's get the cosmetics out of the way,
just click the corner, maybe go back to
| | 02:07 | the Home tab, make it Bold, change the
font if you wish, use centering, top-down,
| | 02:12 | left-right, whatever.
| | 02:14 | Redesign that, change the color
of it, and so on, and so on, and so on.
| | 02:17 | The key step of course is to right-
click on this and do this near the edge,
| | 02:23 | and you'll see a choice called
Assign Macro. You're assigning a macro to this
| | 02:28 | button. And which one?
| | 02:31 | Here are the available macros, the ones in
this workbook only; we could narrow it that way.
| | 02:37 | We want SortByName; that's the macro
that will sort these by last name.
| | 02:42 | Click OK, and we've made the association.
| | 02:44 | If we're done with all the cosmetics
and everything, click outside of it.
| | 02:48 | We'll click this button. It will run our
macro to sort this by last name.
| | 02:53 | Now you can do this with clip art, too.
Just a quick example here. I'm going to go
| | 02:57 | to the Insert tab > Clip Art.
Recently I used something like this. I simply
| | 03:02 | typed in "car". Click Go.
| | 03:05 | I'd be hard-pressed to give you good
strong reason for using this here, but
| | 03:10 | nevertheless any object--whether it's a
shape like what we saw or one of these--
| | 03:14 | could be a vehicle for running a macro.
| | 03:17 | And here too, right-click > Assign
Macro, and there it is. Maybe it's the
| | 03:24 | SortByDepartment macro. Click OK.
| | 03:27 | Now until we click away from it--
maybe I want to make that smaller. Okay.
| | 03:32 | Now again, a little bit frivolous here,
I can't give you good reason for using
| | 03:34 | that. But point here--and there is no
pop-up that tells us what it's going to do--
| | 03:38 | but this will sort by department.
And you saw how we did this with the objects.
| | 03:43 | So here and there, particularly in
workbooks where you have got macros designed
| | 03:47 | only to manipulate data within that
workbook, not a bad idea sometimes to use
| | 03:51 | either a button or clip art to be the
starting point for running the macros.
| | Collapse this transcript |
|
|
3. Using Visual Basic for Applications (VBA)Introducing the VBA environment| 00:00 | Every time you record a macro, you
create a small program using VBA, Visual Basic
| | 00:07 | for Applications, the
programming language of macros.
| | 00:11 | And the big question in many of
your minds would be, what is VBA,
| | 00:15 | and what do you really need to know
about this programming language?
| | 00:20 | In this particular workbook called
Code--notice it's file extension, xlsm--
| | 00:26 | there are macros here. On the View tab
in the Ribbon, rightmost button, Macros >
| | 00:33 | View Macros, there are a
few macros in this workbook.
| | 00:38 | We might want to see what
the macro code is about.
| | 00:42 | I recommend that anytime you record a
macro that you quickly look at the code.
| | 00:46 | It's not that you need to know a whole
lot about this environment, but you'll
| | 00:51 | learn more and more about
macros as you view the VBA code.
| | 00:56 | And certainly one method is to select a
macro in question--like this particular
| | 01:00 | one here, Highlight_Value_Cells--and in
this dialog box, click Edit. There we are.
| | 01:08 | This is a macro created in a prior
movie that highlights just the value cells
| | 01:14 | within a given worksheet.
| | 01:15 | And the big concern of course is what do we
need to know about what we were saying here?
| | 01:20 | And what is this environment all about?
| | 01:22 | We're on a different screen completely.
| | 01:24 | When this macro was recorded, the
initial stepped involved clicking the menu and
| | 01:30 | choosing--by clicking in four different
places--a command process, and all that
| | 01:36 | translates into this.
| | 01:38 | When you view macro code, you're viewing
an environment that begins with the word "Sub."
| | 01:44 | It's sort of out of the past; it means
subroutine. It ends with the phrase "End Sub."
| | 01:49 | These are automatically generated.
| | 01:51 | When you provide the name of the macro,
that's what provided in the very first line.
| | 01:55 | If you provide a keystroke shortcut
at the time of creation, you'll see
| | 01:59 | that here. Don't expect to be
changing this and have any real effect;
| | 02:04 | this doesn't change anything.
| | 02:06 | Don't even think of changing that
there; that's not how we change the
| | 02:10 | keystroke shortcut.
| | 02:11 | These lines here all begin with a
single quote. Those are considered comments.
| | 02:17 | If we remove them, the
macros runs exactly the same.
| | 02:20 | This has nothing to do with the
operation of the macro, but it is
| | 02:24 | good documentation.
| | 02:26 | Many times I will remove this one.
It's simply repeated at the top line.
| | 02:30 | So it doesn't hurt to get rid of it,
and you can casually do that or keep it,
| | 02:33 | doesn't make any difference. Press delete.
| | 02:36 | You can introduce empty lines if you think
it makes the reading of this clearer to you.
| | 02:41 | I am going to click right here and press Enter.
| | 02:43 | I might click down here and press Enter.
| | 02:46 | Totally unnecessary, but on the other
hand, if it somehow clarifies what I think
| | 02:50 | this macro is doing, fine.
| | 02:52 | And at different times we'll see code
like this, and of course, aren't you glad
| | 02:56 | you never had the type something like that.
| | 02:58 | And you can more or less figure out
what's going on in here, although again, the
| | 03:02 | focus is not so much on mastering the
code but getting familiar with it and not
| | 03:07 | getting intimidated by it.
| | 03:09 | And anytime we want to leave this
environment, we can press Alt+F11, as I'm doing here.
| | 03:15 | In fact, you can use Alt+F11 as a
toggle. In any workbook that has macros, say
| | 03:22 | this one, we can press Alt+F11 to
take us into the world of Visual Basic,
| | 03:27 | do what we want to do here, or maybe
just look around, press Alt+F11 and we're
| | 03:31 | automatically back into Excel.
| | 03:34 | So we're getting you used to the idea
that every macro automatically generates a
| | 03:39 | small program in Visual Basic.
| | 03:41 | You want to check it out, get little familiar
with it, as we learn more about this environment.
| | 03:45 | This is not a course in VBA, and yet you
cannot ignore that feature of the software.
| | 03:50 | It's a programming environment
easily accessible from any workbook.
| | Collapse this transcript |
| Viewing the VBA components| 00:00 | There are additional aspects of
the VBA environment that you should
| | 00:04 | become familiar with.
| | 00:05 | Once again, a reminder: the quick way
to enter the Visual Basic window is to
| | 00:10 | press Alt+F11, and this is also the
keystroke shortcut for returning from
| | 00:15 | Visual Basic, Alt+F11.
| | 00:18 | When you come into the world of
Visual Basic here, the screen isn't always
| | 00:22 | looking the way you want it to.
And things can go wrong here and there; it can
| | 00:26 | be a little confusing.
| | 00:28 | I'm not suggesting that the screen
as we see it now is the way it will
| | 00:31 | always appear to you.
| | 00:33 | But many, many times--and you'll see
this depicted in books on macros as well--
| | 00:37 | what you're likely to see on the left
side of the screen--and that portion can
| | 00:41 | be made wider or narrower,
| | 00:43 | there's no strong reason
for doing this right now--
| | 00:46 | but the upper-left corner typically is
occupied by a section where you'll see the word "Project."
| | 00:52 | I'm going to purposely get rid of
this because there will be times when you
| | 00:55 | jump into this environment,
particularly if it's the first time you're using
| | 00:59 | macros on a particular PC,
| | 01:01 | you might not see the portion of the
screen that I just purposely got rid of.
| | 01:06 | On the View Tab, you'll see
the term Project Explorer.
| | 01:10 | It sounds like
National Geographic. Click this.
| | 01:14 | This is certainly one of the
major components and one of the most
| | 01:16 | important parts of the Visual
Basic environment because it gets us
| | 01:21 | oriented with our macros.
| | 01:23 | And what we'll see on the left-hand side--
and these images might be collapsed;
| | 01:27 | now I am doing this on purpose--
| | 01:29 | we will see the names of all open files,
and, if it has been created, the personal
| | 01:35 | macro workbook as well,
looking like this, PERSONAL.XLSB.
| | 01:40 | The pluses in front of them will expand this.
| | 01:43 | If this particular workbook, called Code,
has macros in it, when we click the plus
| | 01:49 | to the left of it, it will expand and
show us the sheet names, and we will also
| | 01:55 | see under here, Modules.
| | 01:57 | Now that minus in front
of it might be collapsed.
| | 01:59 | As soon as we see the word Modules, we
know that at least potentially there are
| | 02:03 | some macros here. Click the plus.
| | 02:06 | And within Modules you might see one or
two or three or more separate modules.
| | 02:12 | Clicking on these does not reveal the
actual macrocode, but if we double-click
| | 02:18 | on one, to the right we see
the macro that's in that module.
| | 02:22 | As you open and close workbooks and
create new macros, every time you create a
| | 02:27 | new macro after opening, it
will go into a new module.
| | 02:31 | What you'll do from time to time is
simply move these macros around like
| | 02:36 | copying the code from one module into another,
and perhaps even changing the names of these.
| | 02:42 | One of these I renamed Utility.
| | 02:44 | Think of these as file cabinets, and you can
have one macro per cabinet or three or four.
| | 02:50 | There's a tendency to store them
together if you have more than a few, by name;
| | 02:55 | otherwise you are trying
to organize them somehow.
| | 02:58 | If you have created a macro for the
personal macro workbook, you will see it
| | 03:02 | indicated out here with a
plus, and you will see Modules.
| | 03:05 | And there's Module 1.
| | 03:06 | I am going to double-click it, and there
is a macro that's in the personal macro
| | 03:10 | workbook. More about this
personal macro workbook later.
| | 03:14 | But the VBA Project, the Project
window as it's called here, is extremely
| | 03:19 | important because it helps us jump to
this particular macro or that macro by
| | 03:24 | going to different modules.
| | 03:25 | In the lower-left corner, less
important but occasionally valuable is
| | 03:29 | the Properties window.
| | 03:30 | We will use that just
briefly to change the name here.
| | 03:34 | It's not critical, but you'll
probably want to have it visible.
| | 03:36 | But by far the most important part for people
using macros is the Code window on the right.
| | 03:43 | If that's not there--and occasionally
it's not--you will want to go to the View
| | 03:47 | menu and choose View > Code,
and that will make it appear.
| | 03:51 | But a common problem, in particular
when you're getting started, is that if you
| | 03:55 | press Alt+F11--which I do recommend as
the fast way to get into the Visual Basic
| | 03:59 | window--you are not always viewing the
macro that you want to right away.
| | 04:03 | And that means, again, we go into the
Project Explorer and double-click the
| | 04:06 | appropriate module to track down
the macro that we're are looking for.
| | 04:10 | So Project Explorer here helps us
locate and manage different macros by
| | 04:15 | providing a staging area
for copying or moving code.
| | 04:18 | This is also where we sometimes change
our mind and say, "You know this macro
| | 04:23 | here, for example, that's
in our current workbook?
| | 04:26 | This is going to be so useful.
We should also put this in personal and maybe
| | 04:31 | even not have it here."
| | 04:33 | So we can move or copy this, and it's
real simple, using standard techniques
| | 04:38 | simply to highlight this.
| | 04:40 | If we think that this macro could be
used in different workbooks, why don't
| | 04:43 | we simply copy this? Copy button
there and go to Module 1 down here over on
| | 04:49 | the left-hand side under PERSONAL.
Module 1, and we will put right here
| | 04:53 | below the other one.
| | 04:55 | There is a Paste button or
Ctrl+V. We will paste it there.
| | 04:59 | Do I want to have it in
both places? Probably not.
| | 05:02 | We could've moved it. I can
go back and get rid of it;
| | 05:04 | it doesn't hurt to have it in both places.
| | 05:07 | Anytime you create a macro and you
store it in a location and maybe you say to
| | 05:12 | yourself, it's the wrong place, or later
change your mind, it's very easy to move
| | 05:17 | or make a copy of a macro.
| | 05:19 | You remember also this idea that when
you are creating macros here and there,
| | 05:24 | rather than recording an action, if
macro code already exists, it's real easy to
| | 05:29 | copy code from one macro
and paste it into another.
| | 05:32 | It's commonly done, and it
helps you build macros faster.
| | 05:37 | There's no question that getting
familiar with this environment is helpful.
| | 05:40 | It does certainly can be
intimidating at first if you obsess too much
| | 05:44 | about knowing the code.
| | 05:46 | Another aspect of this--and it's a minor one--
| | 05:49 | some people don't like the look of
this because of the font, Courier.
| | 05:52 | But the reason this is used is that
the letters are all the same width.
| | 05:56 | And although that's not
providing a reason for using it,
| | 05:59 | when you are comparing code on different
lines when the letters are all the same
| | 06:03 | size, it's easier to make comparisons.
| | 06:06 | So that's what the look is here.
| | 06:08 | You can display size changes here.
| | 06:11 | I've made these larger than I would usually.
| | 06:13 | Simply click the Tools menu up top, go
to Options, and if you want that font
| | 06:18 | size to be larger or smaller, jump into
Editor Format. I've got size 14 right
| | 06:23 | now. Choose the one you want.
| | 06:25 | But I would recommend keeping
Courier or Courier New out here as the
| | 06:28 | particular typeface.
| | 06:31 | So in wrapping up, Project Explorer
helps us locate and manage different macros.
| | 06:35 | It gives us the staging area
for copying and moving code.
| | 06:39 | The Code window contains the actual code and
comments that actually get recorded and
| | 06:44 | gives us insight into what the
macro is all about. Press Alt+F11.
| | 06:48 | We are right back into Excel.
| | Collapse this transcript |
|
|
4. Recording a Macro in Stages Recording a simple macro| 00:00 | If you're relatively new with recording
macros, it's quite likely that the first
| | 00:04 | few macros are going to be short, quick, easy.
| | 00:07 | It's better to understand
the small processes first.
| | 00:10 | One question that might occur to you at
some point is, do I ever need to change these?
| | 00:15 | We've already suggested the idea that
you will from time to time want to do that.
| | 00:19 | We want to expand this idea, and as we
work with macros and go into VBA back
| | 00:24 | and forth into the world of
the VBA and back into Excel,
| | 00:27 | we want to become familiar with the
techniques for why we want to alter a macro
| | 00:33 | and then how we do that.
| | 00:34 | I am going to take a small process
here, create a new macro for double
| | 00:39 | accounting underlining, a feature
that some people might find useful.
| | 00:43 | Part of the reason there is I am using
this is that it's more multi-step than we
| | 00:46 | might want it to be if we do it manually.
| | 00:48 | If I want double accounting here,
| | 00:50 | I am going to have to go into the--
either the Home tab, or possibly make it
| | 00:54 | as fast as possible,
| | 00:55 | you know, right-click > Format Cells,
click the Font button, drop arrow here
| | 01:00 | is Double Accounting.
| | 01:01 | That's the one we want.
| | 01:03 | There we go. Click OK, and there it is.
| | 01:05 | But why not make it faster?
| | 01:07 | Let's record a macro to do this. I am going
to press Ctrl+Z right now to undo that.
| | 01:11 | Let's record a macro.
| | 01:12 | This time we will also emphasize
the idea that we want this macro to be
| | 01:16 | available to any workbook,
or potentially any workbook.
| | 01:20 | So, as we highlight this we want to be
thinking where we are going to be storing the macro.
| | 01:24 | We will click the button in the
lower left-hand corner for activating
| | 01:28 | the recording process.
| | 01:30 | Give this macro a name like
DoubleAccountingUnderlining.
| | 01:34 | Give it a keystroke shortcut,
Ctrl+Shift+U. Store it in a personal macro
| | 01:41 | workbook, the workbook that's
always open every time we open Excel.
| | 01:44 | Once it's been created, it's always open.
| | 01:46 | We will do the Description later. Click OK.
| | 01:49 | We are in recording mode now.
| | 01:51 | We can also get into that Format Cells
dialog box with Ctrl+Shift+F. We'll do
| | 01:56 | it that way this time,
| | 01:57 | Ctrl+Shift+F. There's the Font tab.
| | 01:59 | Underline > Double Accounting.
Okay, we are done. Stop recording.
| | 02:06 | Let's look at the code.
| | 02:07 | I always recommend looking at the code
every time you record a macro, even if
| | 02:11 | you are not going to make changes. It's good to
start getting familiar with more Visual Basic.
| | 02:15 | Alt+F11, this doesn't
always take us where we want.
| | 02:19 | Where do we want to go here?
| | 02:20 | This is stored in the personal macro workbook.
| | 02:23 | The downside, if any, of Alt+F11 is that
it doesn't always take us to the exact
| | 02:29 | code window that we want.
| | 02:30 | On the left-hand side of the screen, we see
Personal, plus in front of the Modules.
| | 02:35 | Double-click Module 1.
| | 02:36 | It's the only one there, and there
is the code that just got recorded.
| | 02:41 | If you look around a little bit,
| | 02:42 | you get familiar with what's going on.
| | 02:44 | Many of these settings are defaults.
| | 02:47 | In a bit, we will show you how you can
actually get rid of them just to tighten
| | 02:50 | up the code. It's not necessary;
it doesn't hurt to leave them in there either.
| | 02:54 | Here's the key line, DoubleAccountingUnderline.
| | 02:57 | You wouldn't know that, but certainly
when you see it, it makes perfect sense.
| | 03:01 | So this macro does what we want.
| | 03:04 | Now, it's sounding a little contrived,
but nevertheless emphasizing the idea that
| | 03:08 | sometimes you want to make changes.
| | 03:10 | Maybe when you do this, to make it
stand out even more prominently because you
| | 03:14 | are not going to use it
maybe in all the locations,
| | 03:16 | you will also want to apply a background color.
| | 03:19 | You remember that there's another macro
out there that you recorded a while ago
| | 03:25 | that applies color to value cells.
| | 03:27 | Let's see if we can find that.
Alt+F11, we are back here.
| | 03:31 | We have only one other workbook open.
| | 03:33 | It's called WatchCode.
| | 03:35 | It has some modules in it.
| | 03:36 | Let's take a look at Module 1.
| | 03:39 | That doesn't contain the macro we are talking
about. How about Module 2? This is the one;
| | 03:43 | it highlights cells on all
worksheet that have values.
| | 03:47 | After selecting those cells--that's
done here. Once again, a line you would never
| | 03:52 | guess what it is, but having recorded
it, you begin that recognize that it has
| | 03:57 | some kind of a code.
It's making constants, it looks like.
| | 04:00 | But all of this code here is simply
about applying a color, a solid color, to
| | 04:06 | the selected cells.
| | 04:09 | Now we could, if we wanted to, record
another macro that just applies the color
| | 04:15 | and then take that code and
put it into the other macro.
| | 04:18 | But here the code is already, so
what are we going to do with this?
| | 04:22 | Let's just copy this code.
| | 04:23 | Now remember, this is from a
workbook called WatchCode.
| | 04:26 | It's a macro that was recorded
there, and this portion of the macro
| | 04:31 | simply applies color.
| | 04:32 | We are going to copy this with the
Copy button and then jump back to the
| | 04:37 | macro under PERSONAL.
| | 04:39 | It's in Module 1.
| | 04:40 | This is the one we just recorded.
| | 04:42 | We want to alter this macro by saying
that not only will it underline using
| | 04:48 | DoubleAccountingUnderlining the
selected data, but after doing that it's
| | 04:52 | going to apply a color.
| | 04:54 | So at the bottom here after End With, I
just pressed Enter, click here, and now
| | 04:58 | I'm going to press either Ctrl+V or
the button in the toolbar up top, Paste.
| | 05:05 | And there is the code.
| | 05:06 | Now, we could click Backspace here;
| | 05:08 | that's certainly not critical.
But here's the code that got copied.
| | 05:11 | That's the portion of the previous
macro that applies the color. Here's the
| | 05:15 | macro that we just recorded to
apply DoubleAccountingUnderlining.
| | 05:17 | Now, although not a heavy-duty use of
the concept, it does remind us that there
| | 05:24 | will be times when we need to change a
macro, and certainly one way will be by
| | 05:29 | copying code from another macro.
| | 05:30 | So I am going to press Alt+F11 and
try that revised macro right here.
| | 05:37 | Control+Shift+U, and there it
is, and you see what's happened.
| | 05:41 | A minor use of it, but it's opening the
door to the idea that we've got control
| | 05:46 | over the Visual Basic environment, and
when we record macros, there will be times
| | 05:50 | when we want to make some changes.
| | Collapse this transcript |
| Adding functionality to a macro| 00:00 | What happens when you realize that a
macro that you've already written doesn't
| | 00:04 | do everything you want it to do?
| | 00:06 | Now if you're relatively new with
macros, that's not a thought that
| | 00:09 | immediately occurs to you.
| | 00:10 | But the more you work with macros, the
more you realize that sometimes you want
| | 00:15 | to expand the functionality of a macro.
| | 00:18 | In this particular workbook called
AddCode, there is a macro available that
| | 00:24 | applies Double Accounting Underline.
| | 00:26 | It's been used over here.
| | 00:26 | It's Ctrl+Shift+U. Probably wouldn't
use it here, but just to demonstrate it,
| | 00:30 | highlight these cells, Ctrl+Shift+U.
| | 00:33 | Now say in that case, a few minutes later
I come back to this, and I say well, I
| | 00:36 | don't really need that there, and
furthermore, let's get rid of this.
| | 00:38 | Well, maybe it's too late.
| | 00:40 | Can we do an undo here?
| | 00:41 | We can't undo what a macro does.
| | 00:43 | The Undo button isn't
available for what we did in a macro.
| | 00:47 | So what do we do manually here?
| | 00:49 | We're going to have to
go back into Format Cells.
| | 00:51 | We could simply right-click and go to
Format Cells here, and remove the Double
| | 00:55 | Underlining that way. Not exactly fast.
| | 00:57 | Underlining, drop arrow, None.
| | 01:01 | What if we could record a macro
that does what we just did manually?
| | 01:04 | In other words, remove underlining.
| | 01:06 | Then there should be a way to take
that code and mix it with the code that
| | 01:11 | already does the underlining.
| | 01:13 | Now, let's first of all look at
the macro code by pressing Alt+F11.
| | 01:17 | We're in the world of Visual Basic.
| | 01:19 | Once again, don't always count
on seeing the macro you want.
| | 01:22 | But in this case, perhaps we are.
| | 01:24 | If you're not seeing this macro, you
want to be sure to go on the left-hand side
| | 01:29 | of the screen here into the AddCode workbook.
| | 01:32 | Make sure the modules are present.
| | 01:34 | This one happens to be in
Module 4. There is the code.
| | 01:39 | That's the key line that
applies Double Accounting Underlining.
| | 01:43 | So let's record a new
macro--put it right here, too.
| | 01:47 | Now when we record macros, we also
have the ability to keep this Visual Basic
| | 01:52 | Editor window on the screen.
| | 01:54 | What we need to do here, and you
might have to click the button in the
| | 01:58 | upper right-hand corner--
| | 01:59 | this is either restore or maximize--
| | 02:01 | we want to take its bottom edge or any
of its edges, drag it upward, make it
| | 02:05 | roughly half screen.
| | 02:07 | In the other half, on the Excel
screen essentially, do the reverse.
| | 02:12 | So in this case, restore down
possibly and make these be roughly 50-50.
| | 02:17 | Now if we record a macro into this
current workbook, it's going to go into a new
| | 02:23 | module, because we just opened the
workbook. And nothing wrong with that,
| | 02:28 | but what I want to do is essentially
create that new module, so that when we
| | 02:32 | record our macro we'll see the
code in the top of the screen.
| | 02:36 | So, in the Excel portion of the screen
here, I'm going to quickly record a macro,
| | 02:42 | make sure it's stored in this workbook
for now--eventually we might change it--
| | 02:46 | click OK, and essentially do
nothing here. Close the macro.
| | 02:51 | The new module has been created.
| | 02:52 | In the upper portion of the screen
here, left-hand side on the Project
| | 02:56 | window, under AddCode, we now have a Module 5
that we'll throw away. That's what we just recorded.
| | 03:04 | Just to orient the screen this way.
| | 03:06 | Seems like kind of a runaround,
but it's a good way to get oriented so
| | 03:09 | that from now on when we record, we will
see the code in the top portion of the screen.
| | 03:14 | It helps us understand VBA better.
| | 03:17 | All we're doing for the moment now is recording.
| | 03:20 | We're about to record a macro
that will remove these lines.
| | 03:24 | Active cell is in place right there.
| | 03:26 | We'll click the Record button
in the lower left-hand corner.
| | 03:29 | Since we are going to be throwing this
away, we don't care about the name of it,
| | 03:33 | nor the description, nor the shortcut key.
| | 03:36 | Just make sure it goes in this workbook.
| | 03:38 | Click OK, and on this cell,
right-click > Format Cells, None.
| | 03:46 | As soon as I click OK, watch the code
appear in the top part of the window.
| | 03:50 | A lot more code than you would have
expected, but similar to the other macro,
| | 03:55 | and there is the key line right there.
| | 03:57 | Now all of these other lines in here, and
nearly all of them, are default settings.
| | 04:02 | We're not going to be needing them,
| | 04:04 | so why don't we just get rid
of them right now, like this.
| | 04:10 | Now, the other macro that we need is
going to look similar, the macro that's
| | 04:14 | already there. It's in Module 4.
| | 04:16 | As it turns out, this is the
only line we really care about,
| | 04:19 | let's say this portion over right here.
| | 04:21 | We're going to copy this,
Ctrl+C. Go back into Module 4--
| | 04:26 | that's where the DoubleAccounting macro
is--and right below this at the bottom,
| | 04:30 | I'll put it right at the end for the moment.
| | 04:33 | Press Enter, and then Ctrl+V, or paste.
| | 04:37 | So, we're trying to pull
everything together here into one macro.
| | 04:41 | Just as this code was unnecessary in
the other macro, and you saw how I got rid
| | 04:45 | of it, I'll do the same thing here.
| | 04:47 | In other words, these are default
settings, and they're not the essence of
| | 04:50 | what we're trying to do. So we'll get
rid of those lines and these lines.
| | 04:55 | One more simplification that
isn't so obvious but relatively easy,
| | 04:59 | this body of code here can be
simplified into a single line by taking off the
| | 05:03 | With structure, and get rid of this.
| | 05:07 | Click up here, press Delete a few times.
| | 05:09 | That line will apply
Double Accounting Underlining.
| | 05:13 | From the beginning, after recording
this macro for the first time, we could've
| | 05:17 | simplified it to do just this.
| | 05:19 | Now we want to do this with the other
macro code that we've just created and copied.
| | 05:24 | Same idea here. And now we're
ready to reconstruct these two macros,
| | 05:29 | put them together, and insert
some logic that cannot be recorded.
| | 05:34 | Any number of occasions
we've mentioned the idea:
| | 05:37 | as much as possible, we want to
record our actions, but we can't do
| | 05:41 | everything using that process.
| | 05:43 | Now we want to insert the If statement.
| | 05:45 | Every programming language has
some kind of If construction.
| | 05:49 | It's central to the idea of programming.
| | 05:52 | We want to cover all bases here by
saying, in English, if the current selection
| | 05:58 | has Double Accounting underlining on it,
then let's apply no style, or turn off
| | 06:04 | underlining. So after that first line
up there, space and then the word Then.
| | 06:09 | It's a good idea when you're
typing code, use lowercase.
| | 06:13 | When I press Enter here,
you'll notice two things happen.
| | 06:17 | The word If will become
capitalized, and also the word Then.
| | 06:20 | You'll also see a color change.
| | 06:22 | You get used to that idea, as you
write code, you'll see that visual switch.
| | 06:26 | If you misspelled a word, it won't happen.
| | 06:29 | The next thing we want to do, and again
going back to English here for the moment,
| | 06:33 | if the current underlining status has
double underlining, then what do we want to do?
| | 06:38 | We want to turn off underlining. Enter.
| | 06:42 | But now we want to cover the other situation.
| | 06:46 | In other words, sometimes the current
selection will not be underlined, and so
| | 06:51 | what do we want to do there?
| | 06:52 | We do want to apply Accounting Underlining.
| | 06:56 | So take this code right here, Ctrl+C
to copy, paste it right here after Else,
| | 07:03 | and we're almost done.
| | 07:05 | One more statement, not so obvious, but we
need to end this with the statement End If.
| | 07:10 | You can type it this way, Enter.
| | 07:13 | A minor point: we can indent
with the Tab; it looks better.
| | 07:16 | Our logic is in place.
And in English, one more time:
| | 07:20 | If the current underlining status is
Double Accounting, then turn it off.
| | 07:25 | If it's not Double Accounting,
then make it be DoubleAccounting.
| | 07:28 | Keystroke shortcut is still in effect.
| | 07:30 | I'm going to press Alt+F11. And in the
Excel portion of our screen here, some
| | 07:36 | cells that have double
underlining. What are we going to do?
| | 07:38 | Ctrl+Shift+U. A few minutes later,
we come back, we change our minds.
| | 07:43 | We want to use Double Underlining here.
| | 07:45 | What are we going to do?
| | 07:46 | Ctrl+Shift+U. We want to use it over here,
| | 07:49 | Ctrl+Shift+U. It's applying Double Underlining.
| | 07:53 | We activate cells where it already exists,
| | 07:55 | Ctrl+Shift+U. We're introducing the
idea that you need to alter some macros.
| | 08:00 | It's not always using an If statement.
| | 08:02 | But the If construction here, common to
all programming languages, is one way we
| | 08:07 | alter our macros to make them do
the things that we want them to do.
| | Collapse this transcript |
|
|
5. Creating Non-Recordable VBA Code Understanding absolute and relative references| 00:00 | A key concept in macro recording, and
one you're more likely to encounter when
| | 00:05 | you're dealing with large amounts of
data, is the concept of absolute versus
| | 00:09 | relative references.
| | 00:11 | On the View tab in the Ribbon, the
rightmost Macro button has a drop arrow. Click it.
| | 00:17 | You'll see a choice Use Relative References.
| | 00:20 | The pop-up tip there, practically an
essay, reminds us and says, "Use relative
| | 00:25 | references so that macros are
recorded with actions relative to the
| | 00:30 | initial selected cell."
| | 00:32 | Without context, I don't
think that means a whole lot.
| | 00:35 | But let's try and put this in context.
| | 00:38 | What if we were to do
something relatively simple?
| | 00:40 | I'm looking at a sheet called Shortlist.
| | 00:43 | It's simply a copy or a pared-down
version of data gathered from the September or
| | 00:48 | October sheets here, which
were substantially longer.
| | 00:50 | They are about 2,400 rows.
| | 00:52 | What we'd like to be able to do
here is something fairly easy,
| | 00:56 | but we want to use this as an example,
| | 00:58 | a jumping off point for talking
about relative versus absolute.
| | 01:03 | What if we wanted to highlight, in each
of these examples here, the first three
| | 01:07 | rows and make them bold and
then make the next three italic,
| | 01:12 | then do that again for the next three and
the next three and over and over and over?
| | 01:16 | A leap that you might not have
made yet is the following idea:
| | 01:19 | if we turn on the Macro Recorder,
and make these bold and then make these
| | 01:24 | italics, as we move to different
portions of the data here, the code that will
| | 01:30 | be recorded, for example, right now,
will explicitly refer to cells B4 to B6.
| | 01:36 | As we highlight these, it will
explicitly refer to B7 to B9, and so on.
| | 01:41 | So any hope or idea that we could
somehow take our macro code and compact it is
| | 01:47 | not going to work so well.
| | 01:48 | Again, the pieces are not quite there yet.
| | 01:51 | What if somehow we could say, after
making these bold, move down three rows and
| | 01:58 | then make these italicized,
| | 02:00 | then move down three rows
select those and make those bold?
| | 02:04 | The idea of recording absolute addresses
versus relative movement is a key idea.
| | 02:09 | I want to start this off
with just a simple example.
| | 02:12 | The active cell here is in C7, and I want to
record a macro that puts the active cell in B1.
| | 02:19 | It's certainly not very heavy duty.
| | 02:21 | As I do this, we'll take a look at
the code and then contrast it with
| | 02:25 | relative references.
| | 02:27 | If you don't use the Macros button and
choose Relative References, it is not in
| | 02:31 | effect; Absolute References are.
| | 02:34 | So the active cell is at C7.
| | 02:36 | Let's record a macro using the
button in the lower left-hand corner, and
| | 02:41 | let's call it Absolute.
| | 02:43 | No keystroke shortcut needed.
| | 02:45 | We'll store it in this workbook. Click OK.
| | 02:47 | All I'm doing is clicking on cell
B1, and that's all we want to do.
| | 02:52 | We will stop the macro, and as I
press Alt+F11--and this workbook is
| | 02:56 | called MailingLists,
| | 02:59 | we have a Module here Module1--
| | 03:01 | There is the macro.
| | 03:02 | Not too exciting. It doesn't do very much.
| | 03:04 | I'm simply going to remove this too,
and emphasize the idea it's a single action.
| | 03:10 | Although we probably never really use
a macro like this, whenever we run this
| | 03:13 | macro, no matter where the active
cell is at the start, we run the macro,
| | 03:18 | it's going to go B1, no matter what.
| | 03:21 | Now, let's use split-screen techniques.
| | 03:24 | For the Excel window, we
can drag the top border.
| | 03:27 | You may have to click the
button here for restore or maximize.
| | 03:30 | Let's make our Excel
window be roughly half screen.
| | 03:35 | Top or bottom doesn't make any difference.
| | 03:37 | In the other half, you'll see Visual
Basic, and possibly it's full screen.
| | 03:41 | If it is, you may or may
not have to click this button.
| | 03:44 | Once you do that though,
you can resize it as well.
| | 03:47 | It's very helpful to see
the code as we record it.
| | 03:51 | Now in the Excel portion of the screen--
I'm going to give it slightly more dominance
| | 03:55 | here so we can see everything--
| | 03:57 | I'm going to put the active cell at C7
just the way it was before, and I'm going
| | 04:02 | to do the exact same thing in terms
of what we do during the recording.
| | 04:06 | But prior to the recording I'm going to
click the button associated with macros,
| | 04:12 | the drop arrow, and choose
Use Relative References.
| | 04:16 | It's a little tricky, by the way,
when you come back and look at this.
| | 04:19 | Is it on or is it off?
| | 04:20 | If it appears to have an
orange background, it is active.
| | 04:24 | So right now, the Use
Relative References setting is on.
| | 04:28 | We do this ahead of time.
| | 04:30 | You can do that within a macro also, and it
will make sense, as we'll see a bit later.
| | 04:35 | So that feature is on.
| | 04:37 | I'm going to create another macro, click
the Record button in the lower-left corner.
| | 04:41 | We'll call this one Relative.
No keystroke shortcut needed, no
| | 04:45 | Description needed.
| | 04:46 | It's going to go right into this workbook.
And as soon as we click OK, we'll see
| | 04:49 | it appear above its name, all set.
And I'm about to click on cell B1.
| | 04:55 | It's pretty obvious we're seeing
something quite a bit different up there.
| | 04:59 | Let's stop the recording here, shift
back to the top of the screen, and compare
| | 05:04 | these two. And here too, I'll get rid
of all the comments and the empty lines.
| | 05:09 | It's pretty obvious those
macros say something different.
| | 05:12 | Yet in both cases, if I put the active cell
at C7, exactly the same thing will happen.
| | 05:19 | Well, using your own logic and trying to put
things together, as we look at this, it
| | 05:23 | starts to make a little bit
of sense, maybe not everything.
| | 05:25 | Let me suggest, first of all, rarely
will you ever type this. You certainly might
| | 05:30 | copy it from time to time
and certainly record as well.
| | 05:33 | How do we read this line?
| | 05:35 | From wherever the
ActiveCell is, move up six rows--
| | 05:40 | if that were positive, of
course it would mean move down--
| | 05:43 | and move to the left one column.
| | 05:46 | Anytime in Excel when you see a rows
and columns used in the same function or
| | 05:50 | in the same Command, rows always
appear first: R,C. So, even though it's not so
| | 05:56 | obvious at first, this portion of the
code means from wherever the ActiveCell
| | 06:02 | is, move up six rows and to the
left one column. So that means
| | 06:08 | in this case move up six rows here and
then to the left one column into cell B1.
| | 06:14 | Now this portion here looks a little unusual,
and doesn't quite say--what does it mean?
| | 06:20 | First of all, it does not mean go to cell A1.
| | 06:22 | That's pretty obvious.
| | 06:24 | It means, as we see it
here, select a single cell.
| | 06:29 | Now, even though you might not be typing this,
at different times you will see this line.
| | 06:34 | What if, just for purposes of demo
here, I want the active cell to move up six
| | 06:40 | rows, over one, and when it gets there, I
want it to highlight these three cells?
| | 06:46 | Now what I do, if I want to alter this manually,
| | 06:50 | I'll put a period behind the A1 and put in
A3. Don't have to capitalize, but looks better.
| | 06:56 | Now this does not say go to cell A1 to A3.
| | 07:02 | It doesn't mean highlight that, either.
| | 07:03 | So in the worksheet, I'm going to
click right here in cell C7. Don't have a
| | 07:07 | keystroke shortcut for this,
so what am I going to do?
| | 07:09 | Go to the Macros button on the
View tab, and there is Relative.
| | 07:14 | I'm going to choose Run.
| | 07:16 | Watch what happens.
| | 07:18 | From the location C7--that was the
ActiveCell--we moved up six rows.
| | 07:25 | That would take us into C1, to the left
one column right into B1. And when we get
| | 07:30 | there, what are we doing?
| | 07:31 | We are not highlighting A1 to A3;
| | 07:34 | we are highlighting cells in the
same layout style as if we were
| | 07:38 | highlighting A1.A3.
| | 07:40 | That's a mouthful, and it's a concept
that's a little bit awkward to grasp at
| | 07:44 | first, but this does not mean
literally go to A1 through A3.
| | 07:50 | When you're looking at macro code
and you happen to see addresses here and
| | 07:53 | there, you can be thrown off track
by misinterpreting a line like this.
| | 07:58 | Now just for purposes of demo--and again
I couldn't give you a strong reason for
| | 08:02 | doing this right now--
| | 08:03 | what if we wanted to move the
ActiveCell up six over two and then
| | 08:08 | highlight these two cells?
| | 08:11 | How would the code be changed?
| | 08:12 | We need to go up six and then
to the left two. There is the 2.
| | 08:18 | Then if we want to highlight two cells
left or right, if we're during this manually,
| | 08:22 | we're going to put in A1.B1.
| | 08:26 | Now in this case because it matches
up exactly with the code, that's why
| | 08:29 | you could be misled.
| | 08:31 | So if I were to go here, for example,
and run this macro again manually Relative > Run,
| | 08:37 | what do we do?
| | 08:39 | Here is where it could be misleading,
because we'd literally did highlight A1.B1.
| | 08:43 | But that throws off track because
what we really have done here is we've
| | 08:46 | moved up six rows, to the left two columns,
and highlighted two cells in this layout style.
| | 08:53 | What if we move up to B1 instead?
| | 08:55 | So we'll go back to the previous
setting where we moved left one column, and
| | 09:00 | then we move to the right, and we put in A1.D1.
| | 09:05 | How do we interpret this?
| | 09:06 | This means from wherever the
ActiveCell is, move up six rows, to the left one
| | 09:11 | column, and when we get there, highlight
cells in the same layout style as A1 to D1.
| | 09:17 | In other words, highlight four cells.
| | 09:19 | So if this is going to work properly, it's
going to be highlighting these four cells.
| | 09:23 | Now again, this is a little bit
academic here, a little bit removed from what
| | 09:27 | we really want to do.
| | 09:28 | But it's getting us familiar with
this concept of absolute versus relative.
| | 09:32 | We still have the Relative recording on.
| | 09:34 | That's what this macro does.
| | 09:36 | So what do we do now?
| | 09:37 | Macros button > View Macros > Relative > Run.
| | 09:44 | We moved up six rows, one to the left,
| | 09:46 | and when we got there,
we highlighted four cells.
| | 09:48 | Now if we want to move through this
particular set of data here and highlight
| | 09:55 | to make these three bold and these three
italic and so on, and so on, and so on,
| | 10:00 | if we use absolute recording, we're going to
see the actual addresses every time we do this.
| | 10:07 | So by using relative recording, we'll
actually record the actually moving down three rows.
| | 10:13 | This is absolutely indispensable,
absolutely required in certain kinds of macros.
| | 10:18 | So getting familiar with the concepts
of absolute and relative recording are
| | 10:22 | extremely important in some macros.
| | Collapse this transcript |
| Using For and For...Next statements| 00:00 | The concept of repetition comes into
play all the time when we talk about macros.
| | 00:05 | It's the reason we actually have macros:
| | 00:07 | we are trying to eliminate repetition.
But there's another perspective on this
| | 00:11 | that uses the idea of repetition
to help us build certain macros.
| | 00:15 | In this particular worksheet is a
short list that's about 42 rows.
| | 00:19 | It's based on data from the longer
October and September lists, which go
| | 00:23 | to about 2,400 rows.
| | 00:25 | And it's often easier to work
with smaller amounts of data.
| | 00:27 | Now what we are going to do here is not
very heavy-duty, but it's going to cause
| | 00:31 | some concepts to emerge.
| | 00:33 | Let's say that in this list
| | 00:34 | we need to highlight the first three
rows of each name set, make them bold, then
| | 00:39 | the next three italic, and then
back here again bold and then italic.
| | 00:44 | If we turn on the macro recorder and
go through these actions, through five
| | 00:49 | or six sets of names perhaps, the code that
we see in Visual Basic will be repetitious.
| | 00:55 | But that's not going to happen automatically.
| | 00:57 | We have to be sensitive to the idea
of absolute versus relative references.
| | 01:03 | If we have got absolute references
turned on, and we highlight these three
| | 01:07 | cells, the code in Visual Basic will
explicitly say B1:B3. And if we then jump to
| | 01:14 | here, it will explicitly say B4:B6.
| | 01:20 | So we want to be sure, as we are
highlighting different ranges here, that we are
| | 01:24 | embodying the concept of moving down
the data, not picking specific cells, but
| | 01:29 | moving down the data. It's a relative reference.
| | 01:32 | However, as we start the process, we want to
make sure that we do explicitly go to cell B1.
| | 01:39 | So before starting the macro-recording
process, we want to make sure that our
| | 01:43 | setting--it's on the arrow on the
Macros button of the View tab--
| | 01:47 | we want to make sure that
relative references are turned off.
| | 01:51 | If there's an orange
border there, then they're on.
| | 01:55 | By clicking this, we have turned that
off, and just to check it again, click the
| | 01:58 | drop arrow. Absolute references are in effect.
| | 02:02 | So we are going to click anywhere here, and
imagine the active cell could be anywhere.
| | 02:06 | As we begin the process of clicking
on B1, we want that to be an absolute
| | 02:11 | reference, but from there on
in--and we'll make this wish--
| | 02:14 | we will make them be relative references.
| | 02:16 | And it's going to be helpful to see the
code being built as we create this macro.
| | 02:21 | So we want to make our Excel screen
be roughly half the screen, use that
| | 02:25 | middle button in the upper-right there.
| | 02:27 | If that maximizes it, you have it down manually.
| | 02:31 | And then we want to jump
into the Visual Basic editor.
| | 02:34 | If you don't see this, press Alt+F11.
| | 02:36 | If you do, you can just click there,
| | 02:38 | and do essentially the reverse. If you have
done it recently, I'll revert back to a previous look.
| | 02:43 | So we want these two windows to
roughly share half the screen each.
| | 02:48 | Notice that there are no macros in
this workbook called MailingList.
| | 02:53 | When we are looking in the Visual
Basic window, here is the name of the file.
| | 02:57 | There are no modules here. But we're about
to create a macro, and we'll see a module.
| | 03:02 | So in the Excel portion of the screen, we
are ready to record. Lower-left corner,
| | 03:07 | we will click the button for macro.
| | 03:09 | We don't need to name this or
give it a keystroke shortcut.
| | 03:12 | We want to store it in this workbook. Click OK.
| | 03:16 | At the moment, we don't see anything
happening in the top portion of our screen.
| | 03:20 | Let's click the plus next to
Modules, double-click Module1.
| | 03:24 | That's the beginning of our macro.
| | 03:27 | We want to jump to B1.
| | 03:29 | That's an absolute reference,
and that's exactly what we see in the macro.
| | 03:33 | Now we want to switch to relative references.
| | 03:35 | The arrow on the Micros button,
choose Use Relative References.
| | 03:41 | Now as we highlight B1 through B3, you
see the code as it appears in the window
| | 03:46 | up above. And of course
that does not mean A1, A3;
| | 03:50 | it means from the active cell, highlight
three cells in the same vertical layout
| | 03:56 | style as if you were highlighting A1, A3.
| | 03:59 | Next step, let's make these cells bold.
| | 04:02 | We can go back to the Home tab or
simply press Ctrl+B. We have made them bold,
| | 04:08 | and you see the code that has emerged there.
| | 04:10 | We'd then want to highlight the next
three. And to make this process a little more
| | 04:15 | visible in the bottom half of our
screen, let's simply collapse the Ribbon by
| | 04:19 | double-clicking one of the tabs.
How about the View tab? Just double-click.
| | 04:23 | It's going to make this easier to see.
| | 04:25 | We'll highlight the next three cells
here and make these be italics, Ctrl+I,
| | 04:32 | and then the next three, and now Ctrl+B.
And as we do this, note how the code
| | 04:37 | is at the top the screen.
| | 04:39 | It's starting to look like it's repeating.
| | 04:41 | They were not quite there yet, looks
like the line we just put in, we saw
| | 04:45 | earlier, but we need to do a few more of these.
| | 04:47 | So in the bottom of the screen, I am
going to highlight a few more, three more
| | 04:50 | rows, and we want to make these italic.
| | 04:53 | That's Ctrl+I. And then three more
rows, Ctrl+B. Three more rows, Ctrl+I.
| | 05:02 | That's probably enough.
| | 05:03 | Let's stop recording. Click that
button in the lower-left corner.
| | 05:07 | Let's look at this code in a little more detail.
| | 05:11 | In these kinds of situations usually the
first line or two, each of them in this
| | 05:15 | case happen to be unique.
| | 05:17 | You can press Enter
after that to separate these.
| | 05:19 | And now as we start to look at the code--
and I could've recorded some more, but
| | 05:22 | we see a repeating pattern.
| | 05:24 | I am going to press Enter in front of
this fifth line here, and that pattern
| | 05:29 | gets repeated four or more times.
| | 05:31 | We don't see three complete cycles.
| | 05:33 | We begin to see what's happening here.
| | 05:35 | If we kept recording, we would see the
same lines over and over and over again.
| | 05:40 | And the purpose here is not
to record through all the data;
| | 05:43 | we want to avoid all that.
| | 05:44 | But by recording a few sets of data
here, we've introduced the idea, at least
| | 05:50 | potentially, of repetition.
| | 05:51 | Let's get rid of this repetitious code
| | 05:55 | and now introduce the idea that
there are structures in the Visual Basic
| | 06:00 | language that allow us to perform
certain actions over and over and over again.
| | 06:06 | One of those kinds of constructions is
referred to as a for. That's F-O-R loop.
| | 06:12 | And in front of this code, right
before it, we want to type "for" followed by a
| | 06:18 | variable name that you could make up,
| | 06:20 | say counter. You can use
different word if you wish.
| | 06:24 | If you are learning VBA in a more
formal way, you will learn more about
| | 06:27 | variables and how they need to be
declared, and there are other aspects of that
| | 06:31 | that we don't need to get
into here. It's okay to do this.
| | 06:34 | For a counter =, now imagine in our list here
that we've got one name for each state in DC.
| | 06:44 | We know there are 51 of them, so we
simply want to say, in a way we use this
| | 06:49 | particular feature here, do this 51 times,
and the syntax is for counter = 1 to 51--
| | 06:58 | and of course in different
situations, whatever that number might be.
| | 07:01 | The concluding statement
after these actions are taken is--
| | 07:05 | and notice, by the way, when I
clicked down there that the word For became
| | 07:08 | capitalized, the word To became capitalized.
| | 07:11 | It's a good idea to always type
these in lowercase. Keywords will
| | 07:15 | automatically become capitalized.
| | 07:17 | Next, counter. So even though we are
not saying this reads exactly the way we
| | 07:22 | want it to in English, you
get used to it pretty quickly.
| | 07:25 | Do these actions here, or perform these
actions, 51 times, over and over and over again.
| | 07:31 | And that's the ideal command sequence
to be using when we know that we have a
| | 07:36 | specific set of names. This will work.
| | 07:40 | In fact, we can jump right back into
the worksheet here. And manually, let's say
| | 07:46 | we take off Ctrl+B here. That will take
off the bold. Ctrl+I takes off all of
| | 07:52 | the italicized entries.
| | 07:53 | Click it again. There we go.
| | 07:54 | Put the active cell here and run this macro.
| | 07:58 | How do we run this?
| | 07:59 | We don't have a keystroke shortcut.
| | 08:01 | We will go to View, click the drop
arrow for Macros > View our Macros;
| | 08:06 | it's the only one we have got:
| | 08:07 | Run the macro. And it went way beyond
where we need it because this macro ran 51
| | 08:14 | times. The main point here is it
certainly took care of this data.
| | 08:18 | But you can see how this has a
lot of use, a lot of applicability.
| | 08:22 | It doesn't cover all situations,
but it's one of those basic structures that
| | 08:26 | you will encounter and need from time
to time as you deal with large amounts of
| | 08:30 | data in an Excel macro.
| | Collapse this transcript |
| Using Do...While and Do...Until statements| 00:00 | In this workbook, there is a macro
that is designed to work on column B and
| | 00:05 | make the first three rows of an entry be bold,
the next 3 italic, the next 3 bold, and so on.
| | 00:11 | This macro is in place.
| | 00:13 | Press Alt+F11 to take a look at it.
| | 00:15 | We are in a workbook called
DoLoops, and here is that macro.
| | 00:19 | It's called BoldItalic, and it is familiar
to you if you have seen the previous movie.
| | 00:24 | This kind of macro code here using a
for loop makes perfect sense if there are
| | 00:30 | 51 entries or fewer. It doesn't make
difference if there are fewer; that's okay.
| | 00:34 | Eventually maybe the list will
have that many sets of names.
| | 00:38 | However, what if we have a series of
data, and the amount will vary, perhaps
| | 00:43 | from month to month?
| | 00:44 | May be we are envisioning a list that
we copy to this worksheet at different
| | 00:48 | times; it's not always going to be the same length.
| | 00:51 | We need to consider some other
structures to handle information even when we
| | 00:56 | don't know how many rows
of data are going to exist.
| | 01:00 | And we are talking about some
variations on a term called "do" loop.
| | 01:05 | It's similar to a "for" loop as we see
it here, but different in the sense that
| | 01:10 | it has more flexibility.
| | 01:12 | And as I jump back into the Excel
environment with Alt+F11, the current macro is
| | 01:17 | set up to run in this
particular worksheet called Shortlist.
| | 01:21 | For the moment, it has only 42 rows,
and that's only seven sets of names, but
| | 01:26 | eventually there will be 51 sets of names here.
| | 01:29 | The October List here is substantially larger.
| | 01:32 | It goes down 2,400 rows, and
conceivably it's going to grow to, or shrink;
| | 01:37 | we don't know necessarily.
| | 01:39 | We want to cover all of our bases to
take the existing macro and alter it so
| | 01:43 | as that it handles data no
matter how many entries there are.
| | 01:46 | Now in this example, what we are doing
is fairly innocuous. It's not that critical.
| | 01:52 | But the focus here is on the concept
and how we set up the syntax and alter the
| | 01:56 | code in Visual Basic to make
this process worked repeatedly.
| | 02:01 | I don't need to tell you that an
Excel worksheet has a million rows;
| | 02:05 | we might have a lot of data here eventually.
| | 02:08 | Now as is frequently convenient, it
will be good to see the Code window and
| | 02:13 | the worksheet together.
| | 02:14 | Let's change the display in the upper
right-hand corner, that middle button.
| | 02:17 | Either restore down or
maximize and drag the edges.
| | 02:21 | And if you're not in the Visual
Basic environment, press Alt+F11, do the
| | 02:25 | same sort of thing here so that our screens
are sharing roughly 50 percent of the space each.
| | 02:30 | Now here is what we would like to do,
| | 02:32 | recognizing that the "for" construction
here works fine if we know exactly how
| | 02:37 | many entries are going to be here.
| | 02:38 | So we are going to introduce the
concept of a "do" loop, and there are a number
| | 02:42 | of different variations on this.
| | 02:43 | We will show you two major ones.
| | 02:46 | This construction
actually ends with the word "loop."
| | 02:48 | We will put that in first.
| | 02:50 | Notice I didn't capitalize it.
When I click elsewhere, it will
| | 02:53 | become capitalized.
| | 02:55 | And this construction
starts off with the word "do."
| | 02:58 | No noun follows it, however. For some
people, it looks a little bit strange.
| | 03:02 | By implication, we want to do everything
from the next line until we see the word "loop."
| | 03:08 | And one of the words that
we can use here is "while."
| | 03:14 | Now switching to English for the
moment, how do we want to say this?
| | 03:18 | We want to do these actions while there is
data present, and we can't say it quite that way.
| | 03:23 | But we can use the term ActiveCell.
| | 03:27 | By the way, spell this as one word.
| | 03:29 | When we eventually press Enter or click
on a different line, the A and the C will
| | 03:33 | become capitalized in there.
| | 03:35 | So as we move through the data, we
want to do these actions while the active
| | 03:39 | cell has data it in.
| | 03:42 | I can't quite say it that way, but we can
say if it's not equal to blank. And not
| | 03:46 | equal to, similar to what we use in
regular Excel and functions, the less than
| | 03:51 | arrow followed by the greater than arrow,
| | 03:54 | this means not equal to.
| | 03:55 | So we want to take these actions as long
as the active cell is not equal to blank.
| | 04:01 | And how we do that?
| | 04:03 | Double quote, double quote.
| | 04:04 | I just click somewhere,
and you saw the capitalization take
| | 04:07 | place automatically.
| | 04:08 | This will work, but it's probably not
the best way to say this. It's a little
| | 04:11 | bit awkward to read.
| | 04:13 | Why don't we change the Equal sign?
| | 04:16 | Or can we just come along and change that?
| | 04:17 | If we change it to equal, then
the wording wouldn't make sense.
| | 04:21 | But there's another word we
could conceivably use here.
| | 04:23 | I am not suggesting in any way you
come along and just make up words, but the
| | 04:27 | opposite of "while" in Visual
Basic code here is "until."
| | 04:32 | So how do we want to say this now?
| | 04:34 | We want to take these actions
until the ActiveCell = empty.
| | 04:40 | And I think for many people that's a lot clearer.
| | 04:43 | So this would be a better
construction than the one we just saw.
| | 04:46 | Do all of these actions
until you see the word Loop.
| | 04:49 | Keep doing these over and over and
over again until the ActiveCell is empty.
| | 04:54 | Now recognize, too, that in the code
here, we are moving down the worksheet.
| | 04:59 | This line right here means move
from the active cell down three rows, and
| | 05:05 | this code here does the same thing.
| | 05:07 | So as we repeat the process, we are
moving down the worksheet three rows at a
| | 05:12 | time with those two statements.
| | 05:14 | Now another variation on this, and perhaps
clearer to many people--and certainly more
| | 05:18 | readable--is to introduce a word you
wouldn't necessarily know, but since we see
| | 05:24 | it, you'll know what it means.
| | 05:25 | I am going to type the phrase "isempty,"
left parenthesis and take off the
| | 05:32 | remaining portion of the code to the
right here and put in a right parenthesis.
| | 05:36 | And as soon as I either press Enter or
click on a different line, watch the upper- and
| | 05:40 | lowercase change, and this will work.
| | 05:43 | And for many people this is the
clearest of the three variations here.
| | 05:47 | And in English we are saying,
perform these actions repeatedly until the
| | 05:52 | active cell is empty.
| | 05:54 | So let's try this on our Shortlist.
| | 05:57 | For the moment, in the original data--
see it at the bottom of the screen--it looks
| | 06:01 | as if we got bold italic there already,
| | 06:03 | so why don't we manually click Column B,
go to the Home tab town here, click B,
| | 06:08 | click it again, click I. There we go.
| | 06:11 | Nothing here is bold or
italic, and we can run this macro.
| | 06:15 | Active cell is anywhere because
the macro automatically starts at B1.
| | 06:20 | There is no keystroke shortcut, so we
will use the View tab, rightmost button,
| | 06:25 | Macros > View Macros.
| | 06:28 | We are looking for BoldItalic. There it is.
| | 06:30 | Let's run.
| | 06:31 | Looks like it did the job.
| | 06:33 | And notice that it did stop just below the data.
| | 06:35 | Well, let's try this on October List.
| | 06:38 | This is 2,400 rows not 42 rows,
so active cell is anywhere.
| | 06:45 | Notice that we are not
changing the macro at all.
| | 06:47 | We are not indicating which worksheet
we are in. But I manually put the active
| | 06:51 | cell on a different worksheet,
and let's run this macro again.
| | 06:54 | And of course, in a real-life
situation, we would either have a keystroke
| | 06:58 | shortcut associated with it, possibly
a button right here in the worksheet.
| | 07:02 | For the moment, we don't have that.
| | 07:04 | We will click the Macros button > View Macros.
| | 07:07 | Let's run this macro again.
| | 07:09 | Double-click that or click Run.
| | 07:11 | Watch it happen.
| | 07:12 | 2,400 rows. It's done.
| | 07:15 | There it is, and it stopped just below the data.
| | 07:18 | And you might experiment with certain
macros from time to time. And I've done
| | 07:22 | this different times where I simply
copied this data, doubled it, copied it
| | 07:26 | all again, quadrupled it, just to see how
fast it would work. And many, many times
| | 07:31 | it's almost as fast when we have more data.
| | 07:34 | And we will see a bit later also, there
is a way to make macros run even faster.
| | 07:39 | But the key idea here is that the
"do" loop construction commonly used in
| | 07:43 | certain kinds of macros is ideal when we
don't necessarily know how many records
| | 07:49 | or rows we are going to be dealing with.
| | 07:51 | And particularly if the lists that we
are using here are going to vary, this is
| | 07:55 | exactly what we need to use.
| | Collapse this transcript |
| Creating interactive macros| 00:00 | To give a macro even more
flexibility, we can introduce the idea of an
| | 00:05 | interactive macro: a macro that
pauses and asks for input from a user.
| | 00:11 | In this workbook called Interactive,
you'll notice worksheets September, October,
| | 00:15 | November. And you could easily imagine
a different type of workbook where each
| | 00:20 | tab is a different region.
| | 00:22 | And maybe the macro action that needs
to be performed, you might need to have
| | 00:26 | that take place on one worksheet or another.
| | 00:29 | And at different times when you run the
macro, you want reaction from the user as
| | 00:34 | to which worksheet is to be selected.
| | 00:36 | So in this particular workbook,
there is already a macro in place.
| | 00:40 | I'm going to press Alt+F11. And there
it is. It makes alternate cells bold
| | 00:46 | and italic, but this could happen on any sheet.
| | 00:49 | We want to add code here that
provides input from the user.
| | 00:53 | So it's going to be the very first
line of the macro, so clicking in front of
| | 00:56 | Range and hitting Enter.
| | 00:58 | We want to introduce the Visual Basic
statement that's actually going to be
| | 01:03 | stored in a variable that we're going
to make up, and let's just call it sheet
| | 01:06 | name or worksheet name.
| | 01:08 | And you can make it upper- or
lowercase. You can even have a mix of upper-
| | 01:12 | and lowercase. Equals.
| | 01:14 | And now the Visual Basic statement inputbox,
left parenthesis, and you'll see a prompt here.
| | 01:21 | And you're likely to see more of these
prompts the more you work with Visual
| | 01:24 | Basic and use standard features.
| | 01:27 | It reminds us what we're
supposed to put in next.
| | 01:30 | The first entry we'd want to put in here
will be a prompt, and we do this within
| | 01:34 | double quotes. So double quote,
and the wording is strictly yours.
| | 01:39 | And ideally once this to be as user-
friendly as possible, so why don't we just
| | 01:43 | type in "type sheet name," or "type
worksheet name," or "type tab name," whatever
| | 01:48 | is clearest to you.
| | 01:50 | And if you want to put a colon behind
it, that's fine, but you must terminate it
| | 01:54 | with a double quote.
| | 01:55 | So anything is fair game
between the double quotes.
| | 01:59 | Comma. And although there are lots of
other options, the only one that's recommended
| | 02:03 | now is to put a title on the dialog
box that will appear at the top here.
| | 02:07 | Now a title in the box will be
something like "Worksheet Selector," and we end
| | 02:13 | with the parentheses.
| | 02:14 | Now the syntax here isn't obvious if
you haven't worked with programming
| | 02:18 | languages, but this action
here will prompt the user,
| | 02:22 | the user will type something, and
whatever he is type will gets stored into this
| | 02:26 | variable called WorksheetName.
| | 02:28 | I'm going to click on the next line,
and you see how spaces are introduced
| | 02:32 | automatically .Visual Basic does that.
| | 02:34 | We're not quite finished yet, because
this variable is holding the actual typed
| | 02:38 | information that the user will type in,
and we then need to connect that with
| | 02:43 | the statement that moves to
that particular worksheet.
| | 02:47 | And the next statement could easily have
been copied from another macro, or maybe
| | 02:52 | you saw it in book on Visual Basic.
| | 02:55 | It reminds us that there are some
analogies here with how we learn code.
| | 02:59 | It's very similar to how we learn a
language; a lot is by imitation, or having
| | 03:03 | seen a previous example.
| | 03:05 | So what I'm going to be typing here--
something you probably haven't seen
| | 03:08 | before--Worksheets and then the name that
we created, WorksheetName .activate,
| | 03:17 | and that is a valid statement.
| | 03:18 | Whatever name was typed in on the prompt
is stored in worksheet name, and that's
| | 03:22 | placed right here in
this Visual Basic statement.
| | 03:26 | Now when we test this, we're not
necessarily sure it's going to work, so how
| | 03:31 | might we say, in effect, ignore all
this? Let's just do the first two lines.
| | 03:35 | Let's run that part of the
macro and see what happens.
| | 03:38 | A couple of different ways to do this,
one way is to introduce something called
| | 03:42 | a breakpoint. We can just click right here,
and that will stop as it comes to this line.
| | 03:47 | By the way, you can click
that again to get rid of it.
| | 03:49 | Another way, a time-honored way, is simply
to put a single quote in front of these
| | 03:53 | lines, and that make some comments for now.
| | 03:56 | Maybe this is a little bit of
work here, but certainly not much.
| | 03:59 | So by doing this, we are essentially
saying these lines are no longer code;
| | 04:03 | they are just comments.
| | 04:05 | And that's handy at times,
particularly with just a few lines where you say
| | 04:09 | just don't use these right now.
| | 04:11 | We're going to run this macro.
It's only going to do two things. As we pres
| | 04:14 | Alt+F11 to go back into Excel, click
anywhere here. We're going to run this macro.
| | 04:20 | No keystroke shortcut available yet.
It hasn't been assigned. We'll use the Macros
| | 04:23 | button on the View tab, View Macros,
there is our only macro, let's Run. And there
| | 04:30 | is the prompt: Type worksheet name.
| | 04:32 | So what I'm going to be typing here is
going to be either September, October, or
| | 04:36 | November, or of course any
other sheet that might be here.
| | 04:39 | And then we will see if this
actually causes the macro to jump to that
| | 04:43 | particular worksheet.
| | 04:44 | So I'm going to type October here. Upper-
or lowercase isn't critical, but spelling
| | 04:48 | certainly is, October. And I'll click OK.
| | 04:52 | And it looks like it did
that. It went exactly to October.
| | 04:55 | Now the reason the cells are highlighted
is for whatever reason the last time I
| | 04:59 | happened to been on the sheet
that's why it was highlighted.
| | 05:02 | So don't make any special
conclusions out of having seen that.
| | 05:05 | Now if I run this again--I'm on the
October sheet--just to test that portion of it,
| | 05:10 | click the drop arrow for Macros > View
Macros. There we go again, and this time
| | 05:14 | I'll type in September and OK.
| | 05:18 | That cell on the September sheet.
So that portion of it is working, and as we
| | 05:22 | press Alt+F11, that's just
a simple way to test this.
| | 05:26 | There are couple of other ways to do this, too.
| | 05:27 | So take off these single quotes here.
And we're ready to run this macro.
| | 05:31 | The purpose here is simply to show
the interactive portion of the macro and
| | 05:36 | to emphasize the idea that you can prompt
for input. And you can easily imagine
| | 05:40 | taking the same kind of feature and
based on logic inside of a worksheet, prompt
| | 05:46 | for a different workbook to
be opened or to be closed.
| | 05:49 | So we're only beginning to touch the
surface of this capability, but it certainly
| | 05:53 | make sense in some macros to have an
interactive component that prompts for user
| | 05:58 | input, and that input is then
used in the course of the macro.
| | Collapse this transcript |
|
|
6. Macro Project: Converting a Mailing List into a Database ListPlanning a macro| 00:00 | How can you plan what you want a macro to do?
| | 00:03 | How do you know all the alternatives?
| | 00:05 | Here is a mailing list.
| | 00:07 | It's for the state of
Colorado, a group of realtors.
| | 00:10 | It's about 2,400 rows.
| | 00:12 | You belong to a consortium, and you're
going to be getting lists like this from
| | 00:16 | other states as well.
| | 00:17 | We've already got an Arizona list
and a California list planned.
| | 00:21 | And although the data is valuable,
in its current form it's not really the way
| | 00:26 | you want it to look. You would like
it to be in the database kind of form.
| | 00:29 | And I'm actually going to zoom back a
little bit here and make column C narrower,
| | 00:34 | and on the right is how we'd
like this list to look eventually.
| | 00:37 | We like to convert it into a list like this.
| | 00:41 | We can sort it, we can do filtering on
it. It's much easier to work with for most
| | 00:45 | Excel users than the list that we see in
columns A and B. This is probably great
| | 00:51 | for mailing labels, that sort of thing,
or at least portion of it is. But the big
| | 00:55 | issue here is how do you
get this data into this shape?
| | 00:59 | And there are probably five or
six major ways to tackle this.
| | 01:02 | One way is certainly could need some
kind of a filtering, or maybe we filter and
| | 01:06 | just share the names and
then copy them over there.
| | 01:09 | We do have to be thinking about what
happens with hidden data as we do that, but
| | 01:13 | that's certainly a viable approach.
| | 01:15 | Another one could be--as I delete this
data to the right, right-click and Delete.
| | 01:23 | Another approach could be what if we
could put Sheldon's name here over in
| | 01:27 | column D, and Connie's name right below
it right there, and Patricia right here,
| | 01:33 | and so on, and do the same thing with
the companies in column A? When you start
| | 01:38 | to think about doing this manually,
you get weary even thinking about it.
| | 01:42 | One thing that certainly could help is a
widely used feature, and some of you may
| | 01:46 | know of it: you can simply take this
data, the information about one particular
| | 01:51 | realtor, you could right-click and copy
it and then over here right-click and do
| | 01:56 | a Paste Special transpose.
| | 01:59 | You can do it this way. There are
some other ways to do it, too. Transpose.
| | 02:03 | Flip the data sideways.
| | 02:05 | I guess you could say it
makes this six times faster.
| | 02:07 | Then we get handle this
information and paste it over here.
| | 02:12 | Again, that may not sound like the best
way to do this. And so the whole idea of
| | 02:16 | "how do you tackle data?" there are
times when there is no simple answer.
| | 02:21 | But let's say that we have
decided to take this latter approach.
| | 02:24 | Now, you might not think at first
that it's the best, and I can hear rational
| | 02:27 | arguments for that. But we want to
get used to the idea that the recording
| | 02:32 | process gives us tools sometimes
for scoping out what we want to do.
| | 02:37 | If we think of only the first three
or four sets of names here--and we've done
| | 02:41 | this in a prior example--if we simply
were to turn on the macro recorder and do
| | 02:47 | what I just did here for one set of names
and do it for the next set and the next set,
| | 02:52 | what we're going to learn in the
process are techniques for using macros and
| | 02:58 | making them more efficient in ways
that we could handle a list like this no
| | 03:02 | matter how many rows it is.
| | 03:04 | Our Colorado list is a few hundred rows,
but our Arizona list is going to be
| | 03:09 | bigger, and of course our
California list is likely to be huge.
| | 03:12 | And we eventually anticipate getting
these lists from other states as well.
| | 03:17 | Maybe four or five times a
year we want to update this.
| | 03:20 | So we want to have a quick method that's
going to work with different volumes of
| | 03:24 | data, and we want it to be foolproof.
| | 03:26 | We eventually would want a title on
here, too. We'll get to that as well. But in
| | 03:30 | the process what we're going to show
you here are those macro techniques that
| | 03:35 | allow us to handle massive amounts of data.
| | 03:38 | Again, no easy answers upfront, and no
simple answer of the question "how do you
| | 03:43 | scope out a macro?" But you think of
alternatives, and you consider some of the
| | 03:48 | problems you're going to
encounter along the way.
| | Collapse this transcript |
| Recording partial code| 00:00 | It seems that many times when you're trying to
record a macro, you want to get it done fast.
| | 00:05 | So the question comes up, should I be
recording all the keystrokes and all the
| | 00:09 | actions that I need at once,
or should we do this in pieces?
| | 00:13 | Sounds kind of disjointed to do this
in pieces. But we want to show you the
| | 00:17 | value of actually recording portions
of what you need to do, saving that
| | 00:22 | portion, testing it out a
little bit, and then recording more.
| | 00:26 | If we've decided that we want to copy
and transpose these records one by one--at
| | 00:31 | least for a few of them to get our feet
wet--eventually, what we will need to do
| | 00:34 | is somehow pull that data together,
put in titles, and do some other things.
| | 00:39 | So let's possibly think of this as a
series of steps, rather than trying to
| | 00:43 | accomplish this all at once.
| | 00:45 | As we do this too, we need to
think out some other issues, too.
| | 00:49 | There's no question that when you're
handling large amounts of information, as
| | 00:53 | you move through the data,
| | 00:55 | you will always have to be thinking
about this absolute versus relative setting.
| | 01:00 | Remember, if I highlight this data,
and the references are absolute, the
| | 01:05 | explicit code refers to the cells B1 to B6.
| | 01:09 | And so as we record macros--and we can
do this within the macro and sometimes
| | 01:14 | even before the macro begins--we will
be going to the View tab and on the drop
| | 01:20 | arrow for the Macros button
that choice Use Relative Reference.
| | 01:24 | Again, remember as you look at this, if
the orange banner is there, the Relative
| | 01:30 | Reference option is set, and we turn it
off by clicking. And now if we look at
| | 01:35 | it again, it doesn't have the orange banner.
| | 01:38 | I think a lot of us agree that
this could be done a bit better here.
| | 01:41 | It's just not obvious until you get used to it.
| | 01:43 | But we do have to think out
the issue in the following way:
| | 01:46 | If we were to highlight this data,
copy and transpose it and put it into cell
| | 01:51 | D1, if we're recording in relative
mode, this means that we have moved two
| | 01:58 | columns to the right.
| | 02:00 | If we then come down and pick up the
next data, we've moved down six rows
| | 02:05 | and over two columns.
| | 02:07 | If we paste this in row 2, it means we are
moving up five rows and over here two columns.
| | 02:15 | It would make sense, and that
seems like the right thing to do.
| | 02:18 | One of our goals though is to say, if
we have to move through a large amount
| | 02:23 | of data, every action what we take as we
are testing this, we want it to be recorded.
| | 02:29 | If we just moved up five rows,
and now we have got to move down to row 13,
| | 02:33 | that's moving down 11 rows.
| | 02:36 | And so the code that we will see
will show a movement of 11 rows.
| | 02:41 | And so the idea of saying, let's put
this record over there, and let's put this
| | 02:45 | record there doesn't sound so good now.
| | 02:48 | It might not seem so good if
we copy this just rightward.
| | 02:52 | In other words, what if we put the
information for Sheldon right there and then
| | 02:56 | take Connie's information right here
and put it there? What's the advantage?
| | 03:01 | We are moving over two
columns to paste the data.
| | 03:04 | We did the same thing up there.
| | 03:06 | When we finish the pasting here,
we came down six rows to here.
| | 03:11 | If we paste Connie's information here,
and now go down to Patricia Willett, we
| | 03:16 | again go down six rows.
| | 03:18 | And so, though not intuitively obvious,
the idea of pasting the data to the right
| | 03:23 | instead of going up below the
previous pasting starts to make some sense.
| | 03:27 | These are the kinds of things you think
about, and when we are recording in small
| | 03:31 | chunks they just emerge all the more obviously.
| | 03:35 | Let's start the process.
| | 03:36 | We will turn on the macro recorder
and copy just a few records here.
| | 03:42 | We want to get our feet wet, so to
speak, take a look at this code, and look at
| | 03:46 | some of the implications of it.
| | 03:48 | And it's always going to work better if
we actually see this with the code being
| | 03:53 | created at the same time.
| | 03:55 | In the upper right-hand
corner, use the Restore button.
| | 03:58 | We want to make our Excel
window roughly half-screen.
| | 04:01 | Now you can put it on the bottom or top.
| | 04:03 | You need to shrink it a little bit.
And if you don't see the Visual Basic window
| | 04:07 | on the background, press
Alt+F11. We want to be there.
| | 04:11 | And the current workbook that we are
working with, let me jump back for a
| | 04:14 | second, remind you, it's called Partial.xlsm.
| | 04:18 | We do see it in the list here in Visual Basic.
| | 04:21 | If you're not seeing the Code window--
and you might or might not be--then you
| | 04:25 | went to View > Code from the window.
Now there is nothing there yet.
| | 04:30 | But let's do make our Visual
Basic window roughly half-screen.
| | 04:34 | Again, click that Restore button
in the upper right-hand corner, move
| | 04:37 | this around a little bit perhaps,
and let's start the recording process.
| | 04:42 | In the Excel window, I am going to
zoom back a little bit, so we can see the
| | 04:45 | data a little bit better.
| | 04:48 | And the active cell could
be anywhere as we start it.
| | 04:51 | We want to always think out what
happens at the beginning of a macro.
| | 04:54 | We want to make sure that no matter what
we do with this data, we start in cell B1.
| | 04:59 | We can either make the setting
right now--we can make sure that's
| | 05:03 | absolute, already is anyway.
| | 05:04 | We can do that in the course of running
the macro because eventually we do want
| | 05:09 | it to be relative, right after we go to B1.
| | 05:12 | So as we start the process, we can
start recording a macro. Lower-left corner,
| | 05:18 | the button next to Ready, if that
button is not there, right-click in your
| | 05:22 | status bar, click Macro Recording,
so that it has a check in front of it.
| | 05:26 | You will only need to do that once.
| | 05:28 | Let's click this button here.
| | 05:31 | We want to create a macro, and why not
give this a sensible name, CreateDatabase,
| | 05:36 | whatever it is you want to call it.
| | 05:38 | No spaces in the name.
| | 05:40 | You can use the underscore
character to separate words.
| | 05:43 | You can shift upper- and lowercase.
| | 05:45 | Macros never can begin with the number, as well.
| | 05:48 | Let's give this a keystroke shortcut.
| | 05:50 | Once again, one of 52 choices: any of
the lower case letters, any of the upper
| | 05:54 | case letters. And you probably don't
want to be using those lowercase letters
| | 05:58 | for commonly used features, like C for
copy, X for cut, V for paste, and so on.
| | 06:04 | So I'm going to use Shift+D here,
and we want to store that in this workbook.
| | 06:10 | We will get to the description later,
which I don't mean to discount, but
| | 06:13 | it's the kind of thing we often do
after our macros are working we provide
| | 06:17 | some documentation.
| | 06:19 | As we click OK, we have just created--and you
will see the upper window here--a module here.
| | 06:26 | There it is right there
under the VBAProject for Partial.
| | 06:29 | That's the name of our workbook. Click the
plus, here's Module, and we started the process.
| | 06:35 | I'm going to click on B1, but I want
make sure the arrow on the Macros button,
| | 06:41 | Relative References is not on,
therefore Absolute References.
| | 06:44 | We'll click on cell B1, and we see
that occur in the macro code up above.
| | 06:50 | And now, well let's say from now on--at
least in this part of the macro--we want
| | 06:54 | to make sure that our references are relative.
| | 06:57 | If I highlight this cell and the ones
below it, the code will appear as B1:B6.
| | 07:03 | We don't want to see that.
| | 07:05 | We want the idea of
highlighting six cells downward to appear.
| | 07:09 | So on the Macros button in the View
tab, we choose Relative References.
| | 07:13 | That itself doesn't generate any code, as you can see.
| | 07:16 | We will now highlight these six cells
and the indication up above in the code
| | 07:22 | means from wherever the active cell was--
it was in B1--highlight in the same
| | 07:27 | layout style as if you were highlighting
A1 to A6, in other words six vertical cells.
| | 07:33 | We'll do a right-click and copy,
and you can see that code of course.
| | 07:38 | And now we are going to right-click
in D1 and Paste Special > Transpose.
| | 07:44 | There's couple of different ways. There we go.
And you see the code that's generated there.
| | 07:51 | A bit later we will show you how you
can clean this up, although there is
| | 07:54 | certainly nothing wrong with it.
| | 07:56 | Here's the key phrase in the code.
| | 07:58 | Once again, we bring out the idea.
| | 08:00 | Someone who knows VBA really well
could possibly type this and type it in a
| | 08:04 | much shorter way, but on the other
hand, we learn about VBA a lot just by
| | 08:09 | watching the code being built.
| | 08:11 | The line above it means, from wherever
the active cell was--and it was in B1--
| | 08:17 | move two columns to the
right and select a single cell.
| | 08:21 | That represents the actual action of
right-clicking on cell D1, and here's
| | 08:25 | the paste transpose.
| | 08:27 | In order to see the worksheet a bit better,
I'm going to double-click the word View.
| | 08:32 | This simply is a way to collapse
the Ribbon, down the worksheet, so I
| | 08:37 | will double-click there.
| | 08:38 | And then let's highlight the next six records.
| | 08:41 | And by doing this, again we look
at the code, what does this mean?
| | 08:44 | From wherever the active cell was--
and it was in D1--we are going to move down
| | 08:49 | six rows to the left two
columns and pick up six more cells.
| | 08:53 | We are about to see the code repeating,
as we right-click in the data and copy.
| | 09:00 | And then we're going to jump into D7,
right-click > Paste Special and Transpose,
| | 09:05 | OK, and then we'll come down
and grab the next six rows.
| | 09:09 | Now, sometimes when you do this, you
will introduce scrolling into the code of
| | 09:15 | the macro, and you can later take that out.
| | 09:17 | Now that didn't happen yet, but it could.
| | 09:18 | So one more right-click here and copy, or
Ctrl+C if you prefer, right-click in
| | 09:24 | D13, Paste Special, and Transpose.
| | 09:27 | I am going to do now what I just
suggested that you might do occasionally.
| | 09:33 | In highlighting data--and I am in
here on the worksheet--I am going to
| | 09:37 | scrolling downward a bit to see
things better, and you will see that that
| | 09:40 | generates code in the macro.
| | 09:43 | Now I am doing that on purpose to
show that will happen from time to time.
| | 09:46 | Later we will get rid of that.
| | 09:47 | So I am going to now highlight this data here.
| | 09:50 | This is the last time around on these.
Right-click, copy, and then out here
| | 09:56 | right-click Paste Special >
Transpose. Stop recording.
| | 10:03 | Now, obviously we haven't moved all the
way through the data, and of course the
| | 10:06 | macro is not finished. But we have the
starting point for recognizing what a
| | 10:11 | macro does and how to start
to make some sense out of it.
| | 10:14 | And if we've done this using relative
mode as we have, you see the repetition
| | 10:18 | that occurs here, and we can then
enter the stage where we will compact this,
| | 10:23 | pull out only the code that we need,
and then introduce looping structures to
| | 10:28 | help us get through this data quickly.
| | Collapse this transcript |
| Using loop structures in context| 00:00 | In this workbook called
Partial-2, a macro has been recorded.
| | 00:05 | It's not complete, and you can
probably see what's happening here.
| | 00:08 | The macro has copied the first three
sets of names into column D. We want to
| | 00:14 | pick up and make some changes with it.
| | 00:16 | But we're not finished with the macro, of course.
| | 00:18 | What we really want to do now is
introduce the concept of looping and decide
| | 00:23 | which of the various
structures that we might use here.
| | 00:26 | In VBA, we hear the term "structures."
| | 00:30 | Three well-known structures are a for
loop, a do while loop, a do until loop.
| | 00:37 | What they all have in common is that
they allow us to process code repeatedly as
| | 00:42 | we move through large amounts of data.
| | 00:45 | Pressing Alt+F11. We're in
the world of Visual Basic.
| | 00:49 | We're looking at this macro here.
| | 00:51 | The workbook is Partial-2.
We're in Module1 here.
| | 00:55 | By recording a part of this, we see
some lines that we can get rid of. But the
| | 00:59 | key idea here is, after selecting B1,
for whatever reason sometimes we do this
| | 01:04 | twice, doesn't hurt--
| | 01:06 | we'd certainly get rid of that right away--
| | 01:08 | starting here with
Selection.Copy, I'm going to press Enter.
| | 01:11 | You can do this at any time in a macro to
offset, to give a visual impact and separation.
| | 01:17 | We copy the record.
| | 01:18 | We move two cells to the right to paste it.
| | 01:20 | We did paste it with transposing.
| | 01:22 | We moved down six rows.
| | 01:25 | Eventually, here is another copy. It looks
like we're doing the same thing again.
| | 01:29 | Come down here, and although three complete
cycles in this example didn't quite happen,
| | 01:33 | that's okay.
| | 01:34 | We're seeing repetition here.
| | 01:36 | Now here and there what might throw you
a little bit is if you did scrolling to
| | 01:41 | see different parts of the
worksheet, you will see that code.
| | 01:45 | But if it had been full screen,
you wouldn't have scrolled.
| | 01:48 | This ultimately has no impact
whatsoever on the macro recording.
| | 01:53 | You can actually delete it.
| | 01:54 | We got copy and copy.
| | 01:57 | Over to the right two cells,
and so on, back and forth.
| | 01:59 | We see what's happening; the code is repeating.
And if there still were some doubt,
| | 02:03 | maybe you go back and do this,
and go through three or four sets.
| | 02:06 | But let's say that it's working properly,
| | 02:08 | we started the third cycle there,
and now we enter that stage where we say we
| | 02:13 | obviously don't want to
do this for all 2,400 rows.
| | 02:17 | We've done it for a few.
| | 02:18 | It looks like things are
happening the way we want them to.
| | 02:21 | But we don't want this repetitious code.
| | 02:24 | Let's get rid of it.
| | 02:26 | Because the code here that gets
repeated does involve moving down the list--
| | 02:32 | here is the key one here,
| | 02:33 | this means move down six rows--because
this takes us through the data, this is
| | 02:38 | what we want to have repeated throughout
the entire list--no matter how long it is.
| | 02:44 | The for loop certainly has some possibilities.
| | 02:46 | A quick reminder of what the for loop does.
| | 02:50 | We can make up a word
here to keep track of things.
| | 02:53 | Counter is a frequent example.
| | 02:55 | The advantage of for is when we have a
distinct knowledge of how many entries
| | 02:59 | we have in the list.
| | 03:01 | If we know, for example, that
there are always 500 names here,
| | 03:04 | we'll say for counter = 1 To 500.
| | 03:10 | For certain kinds of list, that's just perfect.
| | 03:12 | We put the word counter down here
following, after the word next, Next counter,
| | 03:17 | and that will work just fine.
| | 03:19 | Another point here, too:
| | 03:20 | when you're typing entries like this,
keywords like Next will become capitalized
| | 03:25 | when you either press Enter
or click on a different line.
| | 03:28 | It's a good habit to get into.
| | 03:29 | Just type these in lowercase.
| | 03:31 | You get used to the visual impact as
you click away; the keywords are counted.
| | 03:36 | That doesn't happen if you misspell them.
So if I type nexxt and click up here,
| | 03:43 | I'm reminded I didn't get
that visual reaction there.
| | 03:46 | This is perfect for some
situations, but not this one.
| | 03:49 | We want this to be flexible.
| | 03:50 | We don't know how many names are going to
have here, how many cycles we need to go through.
| | 03:54 | So it's better to use a do loop.
| | 03:57 | This ends on the word loop,
and three major variations here.
| | 04:03 | They all will do the same thing.
| | 04:04 | We start with the word "do."
| | 04:06 | There is no noun that follows it.
| | 04:08 | It's implied everything from here to
loop is what we want to do, and certainly
| | 04:12 | one option is to use while.
| | 04:15 | It has its merits,
and sometimes it's the better choice.
| | 04:18 | Do while this ActiveCell, the one that
we always have in our Excel screens, is
| | 04:24 | not empty, and how can we say that?
| | 04:27 | Not equal to, or the less than arrow
followed by greater than arrow, Empty or
| | 04:31 | blank, not quite so perhaps,
double quote, double quote.
| | 04:36 | I think this is the least preferred of
the three options we're going to show you.
| | 04:40 | Once again, you notice when I clicked
elsewhere, the capitalization of the keywords.
| | 04:45 | This is one way to do it,
and by the way, this will work.
| | 04:47 | I'm not saying it won't.
| | 04:49 | But it's not the clearest,
I think, for most people.
| | 04:51 | Instead of while, until works better here,
because then we can eliminate the use
| | 04:57 | of the not equal to, and negative
logic tends to be little bit trickier.
| | 05:01 | Instead of saying do while the
ActiveCell is not empty, why don't we say do
| | 05:07 | until the ActiveCell = blank that way?
| | 05:12 | For many people, I think that would be
the preferred of those two choices that
| | 05:15 | we saw. And perhaps even
better, but not so obvious,
| | 05:19 | it reminds me if you're learning a
foreign language, you might learn a lot out
| | 05:23 | of a book, but when you're in that
country where the language is spoken, you
| | 05:27 | pick up on idiomatic phrases like this one;
| | 05:29 | who would ever guess you
can say this, isempty(ActiveCell)?
| | 05:34 | I guess you could say it's backward
English, but we don't need anything behind it.
| | 05:40 | So what are we saying now?
| | 05:42 | Perform all of these statements until
we see the word loop over and over
| | 05:45 | and over again, until the ActiveCell is empty--
| | 05:49 | one of the key building blocks in certain
kinds of macros like what we're seeing here.
| | 05:54 | So what we've set up here, and this is
actually the heart of this macro, as we
| | 05:58 | jump back into the data here,
| | 06:00 | we want to do this
throughout the length of this data.
| | 06:02 | Here is down 2,400 rows,
and this is the Colorado list.
| | 06:06 | Our California list is going to
be seven or eight times as big.
| | 06:10 | We want it to work no
matter how big the list is.
| | 06:13 | Although we have some other tasks to
complete here, the main purpose of this
| | 06:18 | macro is to get this data
into a database-type layout.
| | 06:22 | Obviously, we've got empty rows over
there which we need to deal with, but the
| | 06:25 | key idea here is introducing the
structure called a do loop, or possibly a for
| | 06:31 | loop in other situations.
| | 06:32 | But you're going to need either one of
those in a variety of situations where
| | 06:36 | macros are needed when
handling large amounts of data.
| | Collapse this transcript |
| Testing a macro in Step mode| 00:00 | A key concept in any programming
language is the idea of testing and
| | 00:05 | then debugging a macro.
| | 00:07 | Macros, of course, despite our wishes,
don't always work the way we want them
| | 00:11 | to. And sometimes it's a huge leap--
and it's even a dangerous leap--to say well,
| | 00:16 | I've tried my macro, or I
think I have it all set up,
| | 00:19 | I'll just run a macro on
all my data and hope it works.
| | 00:23 | In this particular workbook there is
a macro already written that is set up
| | 00:27 | to handle the data.
| | 00:29 | Pressing Alt+F11 here. Here's the code.
| | 00:34 | We want to, one by one, take each set
of data, copy it into column D, transpose
| | 00:41 | it, come along and do the next
record, and so on, and so on, and so on.
| | 00:43 | We're ready to try this, but should we
really try this on all of our data? Answer:
| | 00:49 | no! And particularly if your macro is
manipulating lots of data and possibly
| | 00:54 | deleting data--and as a general rule
of thumb I would suggest that if you do
| | 00:59 | have a macro that's about to
delete data, back up your data first.
| | 01:03 | Things go wrong sometimes,
and programmers have almost a fatalism that says if
| | 01:08 | something will go wrong, it will happen.
| | 01:10 | It's almost like Murphy's Law.
| | 01:11 | So what are we talking about here?
The idea that we might want to test this
| | 01:15 | macro line by line at least
for a few iterations here.
| | 01:19 | And so once again we want to use our
split screen technique. Upper-right corner,
| | 01:23 | click the Restore button, and in
Visual Basic--if you're not there, press
| | 01:28 | Alt+F11--we want to do the opposite,
put this on the other side of the screen.
| | 01:33 | And here and there, as we do this,
sometimes you click that button twice. There we go.
| | 01:37 | Then we have to move it
around and resize it manually.
| | 01:40 | And once again, this can be
on either side of the screen.
| | 01:43 | In Visual Basic, we have
something called Step mode.
| | 01:47 | We don't see this in the menu.
| | 01:48 | It's on the Debug menu.
| | 01:50 | But here's the idea; here
is what we'd like to do.
| | 01:52 | In the workbook part of our screen in
the regular Excel window, we want to have
| | 01:56 | the ActiveCell anywhere because we
want our macro always to start at B1.
| | 02:01 | That's what the code says so we have
this here. And we'd like to actually step
| | 02:05 | through the code and watch it work.
| | 02:07 | Obviously, we don't want
to do this for 2,400 rows;
| | 02:10 | we'd be here forever.
| | 02:12 | And so what do we want to do?
| | 02:14 | The key part of the macro that
still could be in doubt is this looping
| | 02:18 | concept going to work.
| | 02:20 | These statements are to be performed
over and over and over again until the
| | 02:25 | ActiveCell is empty,
but are we sure we got that right?
| | 02:28 | Is this going to work?
| | 02:30 | And if we move down our data six rows
at a time, does that mean that all six
| | 02:34 | cells have to be empty?
| | 02:35 | How is this going to play out?
| | 02:37 | So, we should be concerned.
| | 02:39 | Temporarily, here we'll
make this lower screen bigger.
| | 02:42 | And the idea here would be,
let's insert a break in the data.
| | 02:46 | It doesn't have to be 6 rows.
| | 02:48 | Let me go down maybe right to here,
and just put in a bunch of empty rows.
| | 02:52 | I don't care how many, just a few. Insert.
| | 02:57 | And if our macro that says do this
until it is empty, when it comes to an empty
| | 03:02 | cell, it should stop. But let's make
sure. And as we do this, we want to see the
| | 03:07 | code and learn more about
the code in the process too.
| | 03:10 | Again, we can split these windows on a
variety of different ways. There we go.
| | 03:15 | We've got the active cell
here. Now, what do we do?
| | 03:18 | We click inside the code somewhere.
| | 03:20 | It could be at the top, bottom;
| | 03:21 | it doesn't make any difference. Up there, it's on.
| | 03:23 | And we start the process usually--
this isn't the only way--but debug is the
| | 03:27 | sensible place to start.
| | 03:29 | It's the only word up here that
suggests what we are about to do, Debug.
| | 03:34 | And not so obvious, but the way you
will do this probably the next time and the
| | 03:38 | next time, and thereafter,
is simply start by pressing F8.
| | 03:41 | But you'll notice here it says
Step Into, and look what happens.
| | 03:46 | It highlights the code in yellow.
| | 03:49 | Now every time we press F8 the yellow
highlighting will move to the next line of
| | 03:54 | code in the macro. So if I press
F8, it jumps here to this line.
| | 04:00 | Now you'll notice that in the worksheet
portion of the screen here, this hasn't
| | 04:04 | happened yet, and it doesn't happen
until we move beyond it. So as I press F8
| | 04:08 | again, you'll see the next line
highlighted, but the action would have taken
| | 04:13 | place--namely, the active cell moves to
B1. And sure enough, as I press F8 again,
| | 04:20 | you know what's about the happen;
| | 04:21 | it's going to be highlighting the next
six cells, or this and the five cells
| | 04:25 | below it as I press F8,
and you see what's happening.
| | 04:29 | Now this line here will not generate
any difference, but action is being taken
| | 04:34 | behind the scenes. The ActiveCell is
being evaluated to see if it's empty.
| | 04:39 | If it is, nothing will happen inside this loop.
| | 04:42 | It will jump out of it.
| | 04:43 | We press F8. Now what?
| | 04:45 | We are about to do a copy. Press F8 again,
and you'll see something distinctive:
| | 04:50 | the so-called marquee lights that
we see around a range when we copy.
| | 04:54 | The next line means what?
Move over two columns to the right.
| | 04:57 | We stay in the same row.
| | 04:59 | The row reference doesn't
change, columns two to the right.
| | 05:03 | We move to the right and select one cell.
| | 05:05 | It's going to be D1.
| | 05:07 | Next statement, we will transpose. There it is.
| | 05:12 | Next statement, down six rows and two
columns to the left, and when we get
| | 05:18 | there, select six cells. There we go.
| | 05:22 | This line here which eventually
can be eliminated is a bit strange.
| | 05:26 | This will simply remove those
so-called marquee lights. Press F8.
| | 05:32 | Now we are at the Loop
command. What does this mean?
| | 05:34 | Go back to the top of the Loop.
| | 05:36 | Is the active cell empty? No, it's not.
| | 05:39 | Let's move in and do this again.
| | 05:40 | I'll speed this up a little
bit, copy and paste, back here.
| | 05:44 | Go to the loop, go back up top. Is it empty?
| | 05:46 | No, it's not.
We'll do this again.
| | 05:48 | And you see what's happening in the Worksheet.
| | 05:50 | There we go, back again. Is it empty?
| | 05:52 | No, it's not and go back again. Loop.
| | 05:56 | Now, where is the ActiveCell in our data?
| | 05:59 | The active cell is B25, and you see
it in the upper left-hand corner.
| | 06:03 | Even though a range is
selected, the active cell is B25.
| | 06:07 | If part of this had data in it, it
would still register as an empty cell.
| | 06:11 | But when we press F8 here, we go back to
the top of the loop, and now that cell
| | 06:16 | is being evaluated, and is it empty? Yes, it is.
| | 06:20 | So, what happens?
| | 06:21 | Action moves out of the Loop.
| | 06:24 | That's what you want to do in testing
macros, particularly if you are dealing
| | 06:28 | with large amounts of data.
| | 06:30 | Don't make the mistake.
| | 06:31 | I have done it in the past.
| | 06:32 | Early on in my use of macros, I had a
large amount of data, and one time I
| | 06:36 | zipped through it, and I was copying
and moving data in a really erratic way.
| | 06:41 | And remember this key idea:
| | 06:43 | when you run a macro, the Undo button
does not include the macro actions,
| | 06:49 | and you can't undo them, so it's a good
idea to back up your data first and be
| | 06:55 | careful with that whole concept.
| | 06:57 | And I would say stay away from macros
that delete data until you really need
| | 07:01 | them, until you get more
comfortable with the idea.
| | 07:03 | Now there is one other aspect to this.
Sometimes it will throw you. Sometimes
| | 07:08 | you jump back into the worksheet,
and then you might try this command or that
| | 07:11 | command, and occasionally you find
yourself saying, why can I not do this?
| | 07:14 | What's happening here?
| | 07:15 | The macro, in a certain sense, is still
running, but is suspended. And so in the
| | 07:20 | Visual Basic window, you do
want to click the Reset button.
| | 07:25 | Have we tested this enough?
| | 07:26 | A lot of people would say yes.
And so, what do we do next?
| | 07:30 | Let's delete these empty rows that we
put in. Right-click > Delete in this
| | 07:36 | example here, and let's
delete this data out here.
| | 07:39 | Are we ready to run this macro?
| | 07:45 | We can run it in one of two ways.
| | 07:47 | From the workbook, we can press the
keystroke shortcut, and from the Visual
| | 07:51 | Basic window, we can also go to the Run
command, click here, and notice also we
| | 07:57 | might just click F5.
| | 07:58 | So, either through the Command or F5,
or in the workbook part of the screen
| | 08:02 | here, we could press the keystroke shortcut.
| | 08:04 | Press F5. Watch the micro run.
| | 08:07 | It's got to go through 2,400 rows,
and how long is it going to take? Not very long!
| | 08:14 | Occasionally when you record a macro
you might see a pause here and there, but
| | 08:17 | usually it zips through the data pretty fast.
| | 08:19 | On certain occasions, I've taken data
like this, and just because I had the time
| | 08:23 | I wanted to check it out, I doubled the
data and then copied that quadrupled to
| | 08:27 | see how long it would take and
actually did some timings here and there.
| | 08:31 | And quadrupling the data, for
example, is not going to make the macro
| | 08:34 | four times as long; it will be longer.
| | 08:37 | But the main idea here is it handles
a tremendous amount of data quickly.
| | 08:41 | Now, we're not really finished with
the macro yet. And do notice where
| | 08:46 | the ActiveCell is now;
| | 08:47 | it's in B2401, just under the data.
| | 08:50 | I am going to press Ctrl+Home, and we
see that we still don't have the data the
| | 08:55 | way we want it. But the key idea--going
through the data relatively quickly with
| | 09:00 | the macro does get the data
in the shape that we want.
| | 09:03 | The next things involve pulling the data together.
| | 09:06 | But the key idea, going back to the
beginning of this particular movie, is the
| | 09:10 | idea that we test out our data using
just a few rows or a few columns, as the
| | 09:17 | case may be. We are satisfied with it.
We then put the data back in the order we
| | 09:22 | want it in and run the macro in its entirety.
| | 09:24 | But testing a macro is always a good
idea, particularly when you are working
| | 09:28 | with large amounts of data.
| | Collapse this transcript |
| Pulling the data together| 00:00 | In this workbook called Partial-4, a
macro has been run to take the names from
| | 00:06 | column B and transpose them into
columns D, E, F to the right, name after name,
| | 00:11 | or name set after name set,
all the way through the data.
| | 00:14 | Now the macro isn't complete because we
wouldn't want to leave the data looking
| | 00:18 | the way it does to the right
here--all those empty rows.
| | 00:22 | When the macro finished, also the
active cell is way at the bottom of the data
| | 00:26 | over in column B. Down around row 2,400.
| | 00:29 | But what's the next thing we want to do?
| | 00:31 | We actually want to record an additional
macro and then put the two macros together.
| | 00:37 | And the key idea here is we
want to pull this data together.
| | 00:41 | There are a couple of
different approaches to it, maybe more.
| | 00:44 | Here's one, and not so obvious--and
by the way, one of the things we'd be
| | 00:48 | thinking about here, too, is if we
want to get all this data together, we
| | 00:52 | probably will highlight these columns.
| | 00:55 | At this point, we would want to make
sure that we are in Absolute Reference
| | 00:59 | mode, not Relative Reference mode.
| | 01:01 | So we'll make sure that we do that, too.
| | 01:03 | Here's one approach, it has an advantage;
| | 01:06 | there's another approach,
it has more advantages.
| | 01:09 | I've selected these columns, and a
rather obscure command sequence--but one
| | 01:14 | that has some real power to it--
begins from the Home tab, the extreme right
| | 01:19 | button in the Editing group, Find and Select,
and the not so obvious choice, Go To Special.
| | 01:26 | In here, we can choose Blanks.
| | 01:28 | Out of all those cells there,
let's choose the blanks.
| | 01:31 | By the way, you might be thinking, does this
choose blanks all the way down a million rows?
| | 01:35 | No, just in the active part of the
data, we see blanks highlighted here.
| | 01:40 | So I just scroll down kind of quickly
here, and you'll see it's not highlighting
| | 01:44 | the cells below that.
| | 01:46 | There is that list at the bottom.
| | 01:47 | It just goes to there.
| | 01:48 | We want to get rid of those
blank rows, and we can do that.
| | 01:51 | I say rows, only the cells really,
between columns D and I. Right-click >
| | 01:57 | Delete, not the rows but all the cells
here, and we want to shift all the cells
| | 02:03 | below that are not blank upward. There we go.
| | 02:06 | Now that's an unusual command sequence,
kind of show-off-y in a certain sense.
| | 02:11 | But it does have that big advantage of
keeping the records in the same order,
| | 02:16 | and that could be important.
| | 02:18 | Maybe it's not obvious what order they
are in, but if it's important to you to
| | 02:21 | keep that same order--sometimes it is--
that's the advantage of this approach.
| | 02:26 | I am going to undo this by
pressing Ctrl+Z. Take us back here.
| | 02:31 | Another approach--and I think "better"
from a couple of perspectives--will be
| | 02:35 | simply to select these
columns and then sort the data.
| | 02:39 | The active cell was in column D, and
therefore if we sort by going to the Data
| | 02:44 | tab and pressing AZ, we'll sort the
entire list in ascending order based on the
| | 02:49 | data in column D. There it is.
| | 02:52 | The one advantage is this is a
standard, frequently used Command;
| | 02:56 | everybody use this sorting.
| | 02:58 | The other advantage of course is it
does put the names in alphabetical order.
| | 03:04 | So of the two approaches, I
think this is the preferable one.
| | 03:07 | But do remember that the previous choice
of selecting the blanks and getting rid
| | 03:12 | of them does have that advantage of
keeping the names in the same order.
| | 03:16 | So let's suppose we are
going to go down this path.
| | 03:18 | Once again, I'll press Ctrl+Z to undo,
and let's remind ourselves what we
| | 03:23 | want this macro to do.
| | 03:24 | At first, we want it to select these columns.
| | 03:29 | So even though the macro that has
already been run has left the active cell at
| | 03:33 | the bottom, we can start this
new macro by selecting the columns.
| | 03:39 | So, we are going to record.
| | 03:40 | And as we do this, it's going to make
sense to see the macro code being built.
| | 03:45 | Upper right-hand corner.
| | 03:46 | We'll use the Restore button, make this
roughly half-screen, do the same thing
| | 03:51 | with the other half if it's not already there.
| | 03:53 | And since we just opened this file and
the previous macro was created in the
| | 03:58 | Module1, if we record a new
macro, it will go into a Module2.
| | 04:03 | If we really want to see that, what
we'll have to do here, kind of quickly, is
| | 04:07 | just create a throw away
macro to activate that new module.
| | 04:10 | So I create a new macro, click here,
don't bother naming it shortcut anything,
| | 04:15 | click OK, and stop it immediately.
| | 04:19 | What has that done?
| | 04:20 | In the Visual Basic window
above, we've got a Module 2.
| | 04:23 | There is that throwaway macro.
| | 04:26 | Let's get rid of it.
| | 04:27 | Now as I record a macro, we'll be
seeing it up here in the top of the screen.
| | 04:32 | We want this macro first to jump
into Absolute mode and stay there, and
| | 04:37 | then select the columns.
| | 04:39 | Let's record a macro.
| | 04:40 | We don't need to name it or keystroke shortcut.
| | 04:43 | We want to make sure it's
in this workbook. Click OK.
| | 04:47 | Let's go to Absolute mode, View tab > Macros
arrow, no orange border, we are in Absolute mode.
| | 04:55 | Let's select columns D through I,
and you see the macro code above.
| | 05:03 | Data tab, AZ, and you see all this
sorting code up above. There it is.
| | 05:09 | Macro was sorted alphabetically by last name.
| | 05:14 | What else do we want to do with this?
| | 05:15 | We probably want to put titles in, so let's
just select these top cells here, right here.
| | 05:22 | Again, looking at the code from
above, and we see what's happening:
| | 05:26 | those cells are highlighted.
| | 05:28 | Let's simply right-click and Insert >
Shift cells down, and once again take
| | 05:35 | a look at the code.
| | 05:36 | Next thing to do is to scroll leftward here and
highlight the data in column A for our titles.
| | 05:44 | Right-click, Copy, and now we are
going to be right-clicking in D1, Paste
| | 05:52 | Special > Transpose, OK.
| | 05:56 | I think most of us probably would say,
let's align the data on the left.
| | 05:59 | So let's go to the Home tab
and choose left alignment.
| | 06:04 | And probably another thing most of us
would do would be to adjust the width
| | 06:07 | of the columns here.
| | 06:08 | Simply drag across the columns.
| | 06:10 | Now, we'd simply double-click one of
the column boundaries between letters, and
| | 06:14 | you see the code up above;
| | 06:15 | that's the so-called autofit.
| | 06:17 | Although not critical, it's more or
less a convention not to leave a macro
| | 06:21 | with data highlighted;
| | 06:23 | simply click on a single
cell. We'll click on D1.
| | 06:26 | That's it.
| | 06:28 | So this macro, which obviously is not a
stand-alone macro, but this macro does
| | 06:34 | the work of pulling the data together.
| | 06:36 | We saw two alternate approaches.
| | 06:38 | One got rid of blanks, one sorted the data.
| | 06:41 | I think we made the case for
the second approach being better.
| | 06:44 | Here's our macro code.
| | 06:46 | The next thing we need to do
eventually is to put this together with
| | 06:49 | the previous macro.
| | 06:50 | And there are lines in here
that will eventually disappear.
| | 06:54 | Any scrolling that you need to do to see
data on the screen can be gotten rid of.
| | 06:58 | That's going to be dealt with as well.
| | 07:00 | So the main idea here is
it's a continuation of the idea.
| | 07:03 | We record macros in pieces and
chunks, and we put them together into a
| | 07:07 | larger macro.
| | Collapse this transcript |
| Joining two macros| 00:00 | In this workbook called Partial-5, a
macro has been run to take the names from
| | 00:06 | column B, the names and addresses, and put them
in the columns D, E, F, etc., to the right.
| | 00:12 | Another macro was then written to sort
the data and pull it together, readjust
| | 00:16 | the column widths, and add the titles.
| | 00:18 | And it's not uncommon to write a
series of macros to achieve steps that
| | 00:22 | ultimately are going to be put together.
| | 00:25 | It's time to join the two macros. As I
press Alt+F11, here is the macro that
| | 00:31 | transposes the data, row
after row, 6 rows at a time;
| | 00:36 | that's the major focus of what we want to do.
| | 00:39 | Another macro was recorded later, and
because it was done at a later time after
| | 00:43 | the file was closed, it's in a
different module. Makes no difference whatsoever.
| | 00:48 | What we want to do is to put these
macros together and join them, and in the
| | 00:52 | process clean up some of what we see here.
| | 00:55 | So this macro here, the macro that
does the sorting--and by the way sorting
| | 00:59 | action takes place here.
| | 01:01 | All we need to do here is to copy this
code, and since we are actually going to
| | 01:06 | remove it, might as well cut it.
| | 01:07 | There is this is the scissors
for cut, or Ctrl+X of course.
| | 01:11 | And we are going to go Module 1
and just put this at the bottom.
| | 01:16 | Ctrl+V or Paste, we really don't need
Module 2 any more, and we could leave it empty.
| | 01:22 | If we want to get rid of a module,
Module 2 is empty. I just double-clicked it.
| | 01:26 | Right-click the module, remove it.
We don't want to export it, so its history.
| | 01:31 | Everything is in Module 1,
and there is the split.
| | 01:34 | We can join the two simply by getting
rid of these two lines right here.
| | 01:38 | The macros are joined, and we could run
them together; in fact we probably should
| | 01:43 | test the two of them together.
| | 01:46 | It's always worth exploring the code a
little bit. The thing want to be careful
| | 01:49 | about and be looking for are exact addresses.
| | 01:53 | Here's one right here. And this raises
a potentially serious issue: if we are
| | 01:58 | hoping to be able to use this macro
with different amounts of data, it looks as
| | 02:05 | if in the sorting process here this
only sorts the data in column D through I,
| | 02:11 | rows 1 through 2,401--that's how
many rows we have in this data.
| | 02:16 | How do we change this to make sure that it's
going to work no matter how many rows we have?
| | 02:22 | We need to take out the actual row
reference on both sides, and this
| | 02:26 | simply means sort the data from columns E
through I--the entire length of those columns.
| | 02:34 | Those are the kind of things you look
for. And from previous movies, you remember
| | 02:39 | that the choices like this when they
are by themselves have meaning, but when
| | 02:45 | we see them in context with offsets, those are
relative references. We don't worry about them.
| | 02:50 | But it is something to be concerned
about when you see addresses. For example
| | 02:53 | here, that's just fine;
it doesn't explicitly mean A1, A6.
| | 02:58 | We have got these two macros together,
and we don't want to run this in Step
| | 03:01 | mode. We've checked in Step mode how
this works; it works just fine. And we don't
| | 03:07 | necessarily have to see the code here.
Let's jump back into Excel with Alt+F11.
| | 03:11 | Let's delete the existing
data: right-click and delete.
| | 03:17 | Let's run this macros again.
| | 03:18 | Active cell can be anywhere.
| | 03:19 | We are going to press
Ctrl+Shift+C. It doesn't take very long.
| | 03:25 | The two separate macros are now together.
| | 03:28 | Once again, back to Alt+F11.
| | 03:30 | A few more things to clean up here
eventually, but the key idea here was, anytime
| | 03:35 | you record macros, the potential exists
for putting the macros together. And many
| | 03:39 | times it's just a copy and paste.
| | 03:41 | You put them next each other, delete
the final line of one and the opening one
| | 03:45 | of the other, and they are together.
| | 03:47 | Now a longer course in VBA will
introduce other concepts where you sometimes will
| | 03:53 | want to break up macros into separate
little programs, and then have one macro
| | 03:59 | call, or get, what another macro does.
| | 04:02 | That's a more sophisticated use of it.
| | 04:05 | For the example here, this makes
perfect sense. We recorded two separate macros
| | 04:10 | and then put them together
with copy-paste techniques.
| | 04:13 | We joined the two macros.
| | Collapse this transcript |
| Streamlining macros| 00:00 | When you run a macro, often it's very
fast, but do you really need to watch all
| | 00:04 | that copy and paste action?
And particularly if you're dealing with lots of data,
| | 00:09 | and you've run the macro a few
times, you have tested it out.
| | 00:12 | In this workbook, there's a macro.
The workbook is called Partial-6, a macro
| | 00:17 | that takes the data out of column B,
transposes into columns D, E, F, sorts it,
| | 00:22 | puts titles on top, does the job.
| | 00:25 | But after you have tested a few times--
I am going to run this again. First, I will
| | 00:28 | delete the data here, and simply
click anywhere on the worksheet and press
| | 00:34 | Ctrl+Shift+C. Nothing wrong with this,
and you want to see this at least a few
| | 00:40 | times. It gives you a sense of how it's working.
| | 00:43 | I am going to make sure it's making progress.
| | 00:45 | If we had three or four times as
many rows, it would take longer.
| | 00:48 | There is this question: can
we make the macro run faster?
| | 00:52 | By pressing Alt+F11, we
can take a look at the code.
| | 00:55 | Now you would never guess
that what I'm about to type here
| | 00:58 | will do this, and you wouldn't
necessarily know that the future exists.
| | 01:02 | But at the very beginning of this macro, I
am going to click in front of this first
| | 01:06 | statement, press Enter, and
enter a new line, application.
| | 01:12 | This is as a line of VBA code.
| | 01:15 | Look what happens when I press period.
| | 01:17 | It's a reminder that Visual Basic, VBA,
is a full-fledged programming language.
| | 01:22 | What might follow this--and this is
a huge list, a lot of choices here.
| | 01:28 | From my experience, I know that there's
a feature that will turn off or inhibit
| | 01:32 | all those updates. That's what they are
actually called, called screen updating.
| | 01:37 | So I am going to press the
letter S, and what do we see now?
| | 01:41 | The options that can occur next,
beginning with letter S. Here's what I
| | 01:44 | want, screen updating.
| | 01:46 | I don't think that's a phrase you'd
necessarily be searching for if you're
| | 01:50 | trying to find this feature.
| | 01:51 | Screen updating means changes you make to the
screen, as this macro does as we copy and paste.
| | 01:59 | I could either type this in or press Tab.
| | 02:02 | If we use the setting
ScreenUpdating equals False--and here too we could
| | 02:08 | press Tab--we will not see changes as
they occur on the screen. And of course
| | 02:13 | we do want to see that when we're testing
a macro initially, but eventually we
| | 02:16 | don't, in a lot of cases.
| | 02:18 | Now what I often do in macros that
deal with large amounts of data, I'll enter
| | 02:23 | this or put this into the code at the
beginning but not use it for a while.
| | 02:27 | By simply putting a single quote in
front of it, that makes it a comment.
| | 02:32 | So I have a position to be used a
bit later, and here and there if I make
| | 02:36 | changes to macro, I might
want to turn that on or off.
| | 02:40 | By activating this--in another words
making it not a comment, but code--I will take
| | 02:43 | this off, I am going to jump back into
the worksheet with Alt+F11, get rid of the
| | 02:49 | data, and run this macros again.
| | 02:54 | The downside of this will be
we are in suspense a little bit.
| | 02:57 | We won't know anything is
happening until the end.
| | 03:00 | So I am going to press Ctrl+Shift+C.
You might see some screen flutter here. It's done.
| | 03:06 | It was undoubtedly faster than
what we saw before. Alt+F11 again.
| | 03:10 | Now in certain macros that perhaps
open different workbooks or get to certain
| | 03:15 | stages, at a certain point in the macro
you might want to have the statement put
| | 03:19 | in reverse at a later time.
| | 03:22 | If we see Application.ScreenUpdating
equals True, from there on after that
| | 03:27 | statement, screen actions will be
visible. You might have it on just for the
| | 03:32 | code that opens a workbook, for
example, and then put this line in again to
| | 03:37 | turn off the updating.
| | 03:39 | So you have got some control of how
that's being displayed or not. But it's
| | 03:42 | a valuable tool in longer macros.
And I remember one macro a few years ago
| | 03:48 | that I'd run it was three minutes long, then
I applied this, and then it took 20 seconds.
| | 03:53 | So here and there, it will make
a difference with longer macros.
| | 03:56 | A few other aspects here that we might
want to change, none of them too critical.
| | 04:00 | When we copy and paste, this line of
code which you might have seen earlier
| | 04:05 | simply removes the marquee lights from
copying. And it's not really a critical
| | 04:11 | step; we can get rid of it.
| | 04:13 | Now what if you say, well I'm not sure
if I want to get rid of that, what can
| | 04:17 | we do instead? We might press Enter here,
copy this, Ctrl+C, or drag it, paste it,
| | 04:24 | so on, go right here, Ctrl+V, and make
one of these be a comment. And let's get
| | 04:31 | rid of the other one.
| | 04:31 | So what's the idea?
| | 04:32 | We might run this macro, and if it
works okay, we are going to come back here
| | 04:37 | and get rid of this.
| | 04:38 | If it appears to cause problems, we will
come back here and get rid of the single
| | 04:43 | quote and resurrect it.
| | 04:44 | Once again, you can put empty lines
wherever you want for clarity, and here
| | 04:48 | and there certain lines of
codes can be made more compact.
| | 04:52 | Programmers often like to have
their code be as concise as possible;
| | 04:58 | I wouldn't say that's a noble objective,
but here and there it's just easier to read.
| | 05:02 | Scrolling, if you happen to see scrolling,
we can get rid of these. The only reason
| | 05:06 | we did scrolling in the examples here
is because we needed to see certain data,
| | 05:11 | and that didn't involved
changing active cell or anything.
| | 05:13 | So these can be eliminated. Delete them.
| | 05:16 | That tightens it up a little bit.
| | 05:17 | In certain kinds of actions like
this, the action here was simply to
| | 05:22 | horizontally align the data on the left side.
| | 05:25 | That's the first part of this.
| | 05:27 | The other actions are all default settings.
| | 05:29 | We don't care about
those; we can get rid of them.
| | 05:33 | So that throws away a lot of code there.
| | 05:35 | So those of the kinds of
things you look for here and there.
| | 05:38 | We should get rid of that space as well, too.
| | 05:41 | And also with this action here, the
key action here is transpose. Do I really
| | 05:46 | need worry about these other actions
here? So we could just get rid of this.
| | 05:51 | These are default settings.
Highlight this data here, simply press Delete,
| | 05:56 | another Delete, that cleans that up.
| | 05:58 | Then we have similar paste up here, too.
| | 06:01 | Once again, not critical but the kinds
of things that simplify the look of this,
| | 06:06 | make it easier to read, occasionally
some of these shorten operation of the macro,
| | 06:10 | but usually not a whole
lot, what we are doing here.
| | 06:13 | So those of things we look for.
Let's run this macros again, and if it appears
| | 06:17 | that this doesn't make any difference
whatsoever, we will come back and simply delete it.
| | 06:21 | Alt+F11, delete the data,
press Ctrl+Shift+C, macro is done.
| | 06:29 | Let's say we don't see any problems
there. We will press Alt+F11 get rid of this.
| | 06:34 | So we've seen any number of different
techniques for streamlining a macro.
| | 06:38 | The key one in terms of
macro speed is the VBA line
| | 06:42 | Application.ScreenUpdating = False.
| | 06:45 | We also saw some other techniques here
to remove some extraneous code, make the
| | 06:49 | macro look more concise.
| | Collapse this transcript |
|
|
7. The Personal Macro Workbook Creating a personal macro workbook for macros with wide applicability| 00:00 | An important tool in your use of
macros is what we call the personal macro
| | 00:05 | workbook. And the question arises, how do
you create this personal macro workbook
| | 00:10 | that you need to use to store macros
that could have applications in a variety
| | 00:15 | of different workbooks?
| | 00:17 | On my screen here, I've got a workbook
that has some macros in it. The name of
| | 00:21 | this workbook is called Useful Macros.
| | 00:23 | Here's one of those macros, as I
jump over to the partial budget sheet.
| | 00:28 | I've got a macro that displays
large numbers like this in millions.
| | 00:33 | I am simply going to press
Ctrl+Shift+M, millions with a single decimal point,
| | 00:39 | makes the numbers easier to
read for these kinds of situations.
| | 00:43 | I've got another macro that I
recorded and stored in this particular workbook
| | 00:48 | that displays numbers like
this, quickly, without decimals.
| | 00:52 | Many of you know that the Dollar Sign
button in the Number group on the Home tab
| | 00:57 | applies Accounting Number format,
but it does apply two decimals.
| | 01:01 | You can easily push the button on
the right here twice to decrease those.
| | 01:05 | But what if you want to have a button
that makes the display instant rather than
| | 01:09 | having to use this each time?
| | 01:14 | So I have got a macro that displays
numbers like this in Accounting format with
| | 01:20 | Dollar sign, but without decimals.
Ctrl+Shift+A. I have got that.
| | 01:25 | The reason I would have a macro like
this is because I use that kind of feature
| | 01:31 | often. Now, in both of these examples I
have created the macro while using this
| | 01:35 | workbook, and that's where it stored.
And I have got a third one as well, Mixed
| | 01:40 | Names worksheet here. Here are some
formulas in column E. This is a simple,
| | 01:45 | little formula here using the upper
function, and you can see what it does.
| | 01:49 | It simply takes the letters that are in
the column D entries, makes them be
| | 01:53 | uppercase. It's easier to read and they are
used throughout the column here.
| | 01:58 | And what am I getting at? The idea that
many times when you have formulas like
| | 02:02 | this, you want to keep the results to
throw away the formulas. And we can do this
| | 02:07 | by a manual process, but I have
got a macro all set up for it here.
| | 02:11 | I will just highlight the data and
press Ctrl+Shift+V. And these are no longer
| | 02:16 | formulas; they are just pure values.
| | 02:19 | As I created these, they occur in this
workbook. So I am going to press Alt+F11,
| | 02:25 | and there they are: three small, short macros.
| | 02:29 | It would have been a better idea had
I created and stored these in personal
| | 02:33 | macro workbook, meaning if I had done
that, from now on, I could get to these
| | 02:39 | macros no matter which workbook was
open--and furthermore it would make sense
| | 02:44 | to have each of these macros be
represented by a button on the Quick Access Toolbar.
| | 02:48 | So let's say on the one hand maybe we
put these in the wrong place, but it's not
| | 02:53 | a major issue. It's certainly not
wrong when you're creating macros.
| | 02:57 | You don't necessarily need to put them
in personal macro workbook. Even if you
| | 03:01 | do think there is going to be wide
applicability, you can do it later, so you
| | 03:04 | can do it either way.
| | 03:05 | But you will notice on my screen here
that there is no personal macro workbook,
| | 03:10 | and that's the way it's going to be
in your environment as well, until you
| | 03:13 | actually create a macro to be
stored in the personal macro workbook.
| | 03:18 | If you are using a computer that someone
else has been using Excel on, maybe off
| | 03:22 | and on for a few years, you might very
well see, as you enter the Visual Basic
| | 03:27 | Editor, an entry over here that
indicates there is a file called Personal.
| | 03:32 | So how do we create this?
| | 03:34 | Well, fortunately, we don't have to go
through any formal creation process.
| | 03:38 | I am going to press
Alt+F11 and jump back into Excel.
| | 03:43 | I've already mentioned a macro
that displays values like this with a
| | 03:48 | single decimal place.
| | 03:49 | What if we want to create another
macros that's almost the same, that does apply
| | 03:55 | Dollar signs? In other words I would
like to see Dollar signs on this top row.
| | 03:58 | Well, let's highlight this data and
this time create a macro to go into the
| | 04:03 | personal macros workbook. And again,
you might be saying, well, there is no
| | 04:07 | personal macros workbook. Well, that's true as
of now, but this sets the process in motion.
| | 04:13 | We are not formally creating a new workbook.
| | 04:16 | Let's click the box in the lower left-hand
corner on the status bar, start a macro.
| | 04:21 | We want to make sure right away this
is going to go into the personal macro
| | 04:25 | workbook. There it is.
| | 04:28 | We will give it a keystroke shortcut
of Ctrl+Shift+K, and I want to call this
| | 04:35 | macro DollarValuesInMillions.
| | 04:43 | Now where is the macro going? Into the
personal macro workbook. That's where it's going to
| | 04:47 | be stored. And the only difference in
my display here will be the Dollar sign.
| | 04:52 | So by right-clicking and going into
Format cells, or pressing Ctrl+1, any number
| | 04:56 | of different ways to get here,
| | 04:58 | we want to be on the number tab,
and we can usually start this simply by
| | 05:02 | picking one of the entries
from the early list here.
| | 05:05 | If you have chosen Custom, pick, for example,
something like this and simply end this with .0,,
| | 05:13 | that means millions, and then on the
front of this, let's put in a Dollar sign.
| | 05:21 | Click OK and we see the results immediately.
| | 05:24 | We are done with this
macro. We stop recording.
| | 05:27 | Now let's press Alt+F11. And now we see
a new entry in the Visual Basic Editor,
| | 05:36 | top here, VBA project personal.
| | 05:38 | We have created the personal macro
workbook simply by saying that we want our
| | 05:44 | macro to be stored there.
| | 05:45 | We look under Modules.
| | 05:47 | There is the plus. Here is Module 1, we
double-click, and there is that macro.
| | 05:52 | Now here's something that will surprise you.
| | 05:55 | If you're in the Excel workbook
maybe would have done this right after
| | 05:59 | creating this macro. Surely Alt+F11 is
fast, but what if you went to the View
| | 06:04 | tab and chose Macros > View Macros?
And I'm looking around for macros
| | 06:11 | where? Not just this workbook.
We could choose either personal or all open
| | 06:16 | workbooks--either way.
| | 06:19 | There is the macro that just got recorded.
| | 06:21 | What if we want to edit the macro?
| | 06:23 | When you click Edit, you
are going to be surprised.
| | 06:26 | "Cannot edit a macro on a hidden workbook,
Unhide the workbook using the Unhide command."
| | 06:32 | Well, that's a little bit strange
isn't it? And this may throw you, and if you
| | 06:37 | haven't been aware of the Alt+F11
process, this might kind of stop you in your
| | 06:41 | tracks and say, well, I don't
know what's going on here at all.
| | 06:43 | This doesn't make any sense.
| | 06:45 | Alt+F11 is the way to get in there.
| | 06:48 | Now you did notice the phrase
"a hidden workbook." What's that all about?
| | 06:52 | Every time you open Excel after
having created the personal macro workbook,
| | 06:57 | that workbook, personal macro workbook, will
always be open, but is opened as a hidden file.
| | 07:04 | It's a little bit strange to go
into the View tab here to unhide.
| | 07:10 | We can choose this. What can we
unhide? Unhide workbook personal.
| | 07:14 | Is there any reason to do this?
| | 07:16 | Not really. You can click
OK and say, well, now what?
| | 07:20 | There is that workbook.
| | 07:21 | There is no reason to really
do that. So let's hide it.
| | 07:26 | But it does explain the
mechanism of what is happening here.
| | 07:29 | The personal macro workbook, once it has
been created, and it need only be created by
| | 07:34 | you storing a macro there, from there
on in, its always available every time you
| | 07:40 | open Excel. It is available as a hidden
workbook, but all we care about is when
| | 07:45 | we press Alt+F11, we can make changes
and additions and deletions to the macros
| | 07:51 | that are found there. And these other
macros that we created earlier that we are
| | 07:56 | in the useful macros workbook
right here--and I am down below here
| | 08:01 | double-clicking on Module 1--
| | 08:03 | we can copy these and put them into
personal macros workbook. In fact, better yet,
| | 08:09 | why don't we cut them from here using
Ctrl+X or the Scissors button up here?
| | 08:15 | Let's cut these from the workbook
called useful macros and put them into
| | 08:21 | personal macro workbook, because we
want to them to be available all the time.
| | 08:25 | Ctrl+V or paste right here.
| | 08:29 | So in a certain sense you can say, well,
it doesn't make any difference where you
| | 08:32 | create your macros and store them.
| | 08:34 | Well it does, ultimately, but you can
move them around relatively easily.
| | 08:38 | You can copy, you can paste, and what we
showed here was the idea that once you
| | 08:43 | create the personal macro workbook, it's
available. You can't get to it directly
| | 08:48 | by jumping into edit, but you can
always get into with Alt+F11. And once created,
| | 08:53 | you do have available, in
all workbooks, these macros.
| | 08:58 | And there is no strong reason for saying I
want to unhide the personal macro workbook.
| | 09:04 | It's open, and it will be open all the
time as you work with Excel, and it's the
| | 09:09 | ideal location for storing
macros with wide applicability.
| | Collapse this transcript |
| Closing and updating the personal macro workbook| 00:00 | Once you have created a personal macro
workbook and have started to use it on a
| | 00:04 | regular basis, you want to be sure
that when you make changes to macros there
| | 00:09 | that they're currently up to date.
| | 00:12 | And you might notice this, too: when
you enter Excel after having created the
| | 00:15 | personal macro workbook, the Unhide
button on the View tab is selectable.
| | 00:21 | Now even if this file is closed--it's
an empty file, I'll just close it--that
| | 00:27 | Unhide button is still there.
| | 00:29 | It's a reminder that we have a hidden
workbook that's open; we're not seeing it.
| | 00:35 | It's the personal macro workbook.
| | 00:37 | It's always available whenever you
open Excel once it has been created.
| | 00:42 | And whether we have a file open here
or not, we can quickly press Alt+F11 and
| | 00:49 | see that the personal macro workbook is
there and see the macros that are in
| | 00:53 | it. Here is Module1.
| | 00:55 | You do have control over the
organization of macros here. And what if you make
| | 01:00 | changes to the macros? If we were
not altering files, necessarily--
| | 01:03 | now probably we are most of the time,
but let's imagine jumping back into Excel here.
| | 01:08 | Maybe I'll open a recent file or
create a new file with Ctrl+N. Maybe I'm
| | 01:13 | building some date in here. Maybe I'm
using one of my macros, maybe not.
| | 01:18 | But from time to time, we have
thoughts about, what what's going on here?
| | 01:22 | What if we make a change to the macro,
either by adding a new one or altering
| | 01:26 | some of the text here?
| | 01:28 | Although you will get a prompt when you
exit from Excel about updating personal
| | 01:32 | macro workbooks--and you
would nearly always say yes--
| | 01:35 | you do have the option--and it's
recommended--that from time to time you go to
| | 01:40 | the File menu within the Visual
Basic editor and Save PERSONAL.XLSB.
| | 01:47 | That's the complete file name.
| | 01:48 | Just by doing this, you are sure that
your personal macro workbook is up to date.
| | 01:54 | If you forget to do that, again, you
will get a prompt when you do a normal exit
| | 01:58 | from Excel, but occasionally--you know
what happens--the system crashes or something
| | 02:02 | goes wrong and changes that we
made here will not have been saved.
| | 02:06 | There is an organizational idea here
also. Three of the macros here relate
| | 02:12 | to formatting issues.
| | 02:14 | And although you don't necessarily have
a lot of macros here, the more you have,
| | 02:19 | it might make sense to categorize
these and store them in a certain way.
| | 02:24 | Right now, under Personal here,
there is one module that's called Module1.
| | 02:28 | Let's change the name of this.
| | 02:31 | When you click the name Module1, you'll
notice in the Properties window below
| | 02:35 | this--and by the way if you don't see
the Properties window, go to the View tab
| | 02:41 | and activate it with this
button right here, Properties window.
| | 02:45 | So in the lower properties
window, we see the term Module1.
| | 02:49 | Let's just put in FormattingMacros.
| | 02:51 | That would be a good way to describe
those macros, at least three of them.
| | 02:57 | Whatever you type here, by the way, do
not put in space. And as soon as we press
| | 03:01 | Enter, we've provided that name.
| | 03:03 | If you'd like to add a module, it's
from the top menu: Insert > Module. And it
| | 03:10 | will have that default name Module1.
| | 03:13 | Maybe we just want to call this other
macros or just leave it this way for a while.
| | 03:17 | Again, we do this in the
Properties section of the screen here in the
| | 03:21 | Properties window from Module1 to
OtherMacros. Of course you can come up with
| | 03:26 | your own naming scheme.
| | 03:27 | And so the original Module1, now renamed
FormattingMacros, has all these macros in
| | 03:32 | them, and at least three of them I
want to keep, but say not this one.
| | 03:36 | So I want to highlight this
particular macro code, cut it, and then go to
| | 03:42 | OtherMacros and paste it there.
| | 03:46 | So you're in charge of this, and the
more macros you have that you put into the
| | 03:50 | personal macro workbook, you want to
have a sense that you're in charge of the
| | 03:53 | organization of them,
and you know what's happening.
| | 03:56 | What happens when we do exit from Excel
| | 04:00 | if I jump back into the Excel window
with Alt+F11? And whether I've got files
| | 04:04 | open or not is not the issue, for the
moment. I'm ready to exit from Excel.
| | 04:09 | Click the X in the upper right-hand
corner. Certainly one way to do this. And here
| | 04:13 | is the prompt that you want to be
very sensitive to: Do you want to save the
| | 04:17 | changes you made to the Personal Macro Workbook?
| | 04:20 | And nearly always you do want to do that,
so you probably going to click Save.
| | 04:24 | But it is a reminder: you want to do
a normal standard exit if you have a
| | 04:29 | personal macro workbook.
| | 04:31 | You don't want to just go turn off the
computer and fail to do this, because
| | 04:34 | it's not going to happen for you
unless you've been repeatingly updating it.
| | 04:38 | So you're in charge of the personal
macro workbook, and it's a great tool for
| | 04:42 | storing macros that you think
might have wide applicability.
| | Collapse this transcript |
|
|
ConclusionFinal thoughts| 00:01 | If you found this course beneficial
but if you want to know more about Visual
| | 00:05 | Basic, the language of macros, check
out Curt Frye's course on VBA In Depth.
| | 00:10 | If you want to review of some of the
new features in Excel 2010, you might want
| | 00:14 | to check out Bob Flisser's
course on Excel 2010 New Features.
| | 00:18 | If you want to know more about power
shortcuts, you might check out my course on
| | 00:22 | Excel 2010 Power Shortcuts.
| | 00:24 | Thank you for watching!
| | Collapse this transcript |
|
|
