Excel 2007 Course: Record Macro (Expert Lesson 8-11: Record a macro with absolute references)

More Excel 2007 videos at http://ExcelCentral.com. You can also download and print the free Excel 2007 tutorial and sample files from the site. The course can be used for both self instruction for Excel beginners and those moving to Excel from an earlier version. The course is structured into session and lessons make it ideal for teachers who wish to run Excel 2007 courses. We've used years of experience from our classroom courses to perfect our teaching method which will enable you to learn Excel 2007 in a fraction of the time needed when learning from conventional text books.

---

Closed Caption:

for this lesson you need to open the
sample file
expenses claim warm from your sample
files folder
imagine that this is a form you need to
fill in
every week every time you make an
expense claim
you have to add your employee number and
all the other details
this isn't very efficient so you decide
to record a macro
that will automatically complete part
the form
let's begin recording a macro now now I
couldn't use the developer Tom
and in the code group record macro but
that see its twin
on the View tab on the View tab in the
metros group
i click the bottom half of the macros
split button
and here's record a macro as well i
click
record macro and the record macro dialog
appears
first I need to choose a name for my
macro now it's a bad idea to abbreviate
names
you may be tempted to type Phil ext
for that this will only confuse it's
much better
to not abbreviate your macro names and
call this
fill-in expense form note also that
macro names can't have a space
and always use mixed case the macro
names
now let's talk about the shortcut key a
good shortcut key people
this macro might be control and a lower
case
eat that that is a problem in using
shortcut keys with macros
Excel provides many shortcut keys
such as control C to copy if you were to
define
control C as your micro shortcut key
you'd
override excels shortcut key and YouTube
no longer be able to use
control C to copy as it would run the
macro
instead there's four strategies that
avoid causing clashes
with the built-in shortcut key the first
is to use capital letters instead of
lower case
this will result in a shortcut key of
control plus shift plus E and this will
remove
any possibility have a clash with a
built in short cut key
another strategy is to run your mac os
from the ribbon
instead of using shortcut keys the third
option
is to put buttons on your worksheets to
run the macros
you learn how to add buttons two
worksheets in Essen
816 and the full strategy is to add
buttons
to the quick Access Toolbar to run each
macro
you learn how to do this in lesson eight
to 16
for the moment though I'll stay with the
lower case
he now let's talk about where we're
going to stall the macro
we can store it in one to three places
the personal macro workbook eight new
workbook and this workbook
macros stored in this workbook are also
available
to other workbooks but only if the
workbook
containing the macro is open the
personal macro workbook
is a special workbook called personal
dot XLS be
this is stored in a folder called Excel
start
which is buried deep in the filesystem
when nobody is likely to find
change it whenever you open Excel the
personal macro workbook
opens in the background that it's hidden
so you never see it
because it's always open every workbook
has access to its macros at all times
the
only purpose of the personal macro
workbook is to act as a container
to store macros that you want to be
available to
every workbook remember that if used or
macros
in the personal macro workbook they'll
only work on your machine
if you email a workbook that depends
upon
one of these macros it won't work on the
recipients machine
in this type of scenario you'd want to
store
the macro in this workbook you
really want to use the new workbook
option
if you record a macro and a new workbook
you'd have to
always make sure that it was open before
you could access it's macros
so will choose the most normal location
which is
this workbook it's good practice to
describe your macros
so that other users will understand what
they used full
the description is displayed in the
macro dialog
when the user runs the macro so that's
type in
a description for this macro a good
description would be
Phil in the name Department
employees number bank
account number and
salt code in at the expenses
claim form on out like okay
and we're recording on macro to begin
I'm going to make cell
b3 the active cell by clicking in it
this is a potential pitfall supposing
when I started recording a macro b3
was already the active cell if that was
the case
you'd have to click outside b3 and then
back
inside b3 again otherwise the recorder
would place your name into whatever
sells the active cell
when the macros run and this might not
be
b3 now let's fill in the form
with some fictitious details let's have
elvis
presley the department can be rock
and role the employee number
he P 999 the bank account number
just make one up and the sort code once
again
I'll make one up make sure that you
press the
enter or control and Turkey after
entering
the last value otherwise you'll stay in
edit mode
and won't be able to stop the macro
recorder so I'll press and to
also to help out a little more it's
likely that the user this form
will begin by entering his claim details
so
I'll move the active cell down to sell a
sixteen
and now I'll stop recording my macro by
clicking the stop button
on the bottom status bar now I contest
the macro I've just recorded so out
elite
all those details I'll take a nacelle
away from the form
to prove that cell b3 is being selected
and I'll run the macro by clicking View
tab on the ribbon
and in the macros group the apart other
macros
split button and as the fill-in expenses
form
macro out like run and
although the details are entered into
the form but I can say a small problem
in the bank account number because XL
has interpreted
it as a number rather than a string so I
need to
reform at the bank account number let's
format it
as a number rather than general and when
I click
okay the bank account number displays
we'll also need to get rid of those
decimal places
so I clicked home on the ribbon and and
the number group
i click decrease decimal twice of course
that's not an issue
with the macro its an issue in how the
form was for matted
so if I delete all the cells one more
time
and I run the macro again this time and
run it with the
control a hotkey so i press control
E and the form is completed we're not
going to save the form in this lesson
but we will keep the form open for the
next lesson
that's because in order to save a
workbook
that contains a macro you need to
understand more about
security and workbook formats you learn
this
in the next lesson lesson 8 12 so that
concludes
this lesson but keep the workbook open
and
move on to lessen 8 12