Excel 2010 Course: Expert Lesson 8-11: Record a macro with absolute references

More Excel 2010 videos at http://ExcelCentral.com. You can also download and print the free Excel 2010 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 2010 courses. This lesson teaches you how to record a macro with absolute references. We've used years of experience from our classroom courses to perfect our teaching method which will enable you to learn Excel 2010 in a fraction of the time needed when learning from conventional text books.

---

Closed Caption:

him
this lesson you need to opened 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 expenses claim
you have to fill in your name
Department employee number and other
details
this isn't very efficient so you decide
to record a macro
that will automatically complete part of
the form
so I'll begin recording the macro by
clicking the
you champ on the ribbon in the macros
group
the split button at the bottom the
macros button
and then record macro and the record
macro
dialog appears
first I need to choose a name for my
micro
now it's always bad practice to
abbreviate names
so you might be tempted to call the
macro feel
expense from that that's a really bad
name
because it's going to confuse in the
long run
so you should only spell out your micro
names in full Phil
a.m. expense full would be a great name
for the macro
let's not talk about the shortcut key
you might think a great shortcut key
would be control
and lower case easy but there's a
potential problem
when you use lower case letters full
macro
shortcut keys
Excel provides many showed cookies have
its own
such as controlled see to copy
if you were to define control see as a
macro
shortcut key you'd override excels
shortcut key and you'd no longer be able
to use
controlled C to copy as this would run
the macro
instead there are several strategies to
avoid causing crashes with the build in
shortcut keys
the first is to use a capital letter
feel shortcut key
then you need to press controlled shift
he to run the macro
and this won't conflict with any other
excels built
in shortcut keys the second strategy is
to
only run macros using the ribbon you've
already seen
how to do this by clicking the View tab
and in the matters group
macros on the ribbon
that this is quite an awkward way to
access your macros
the third method is to provide the user
with buttons
to run macros that you in bed into the
work she's
you're going to learn how to do this in
lesson 8
17 run a macro from the button control
the full method would be too and a
button to
either the Quick Access Toolbar all the
ribbon to run
each macro
you're going to learn how to do this in
lesson 8
17
but for the moment will stay with
controlled shift and
capital E
now that's talking about the store macro
n options
that free possible places to store a
macro
micro 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
word bug
called personal dot Excel sp
this is stored in a folder called Excel
Stolt
which is buried deep in the filesystem
where nobody
is likely to find and change it whenever
you
open Excel the personal macro
workbook opens in the background but
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
up the personal macro workbook is to act
as a container
the stone markers that you want to be
available to
every workbook
remember that if you still macros in the
personal macro workbook
that only going to 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 want to
store the macro
in this workbook
you really want to use the new workbook
option
if you record a macro in a new workbook
you'd have to always make sure that it
was open
before you could access its macros
so having discussed those options we're
going to stay with the default
this workbook
now for a description for the macro it's
good practice to
described all my clothes in a detailed
way so that
other users will understand what they
used for
the description is displayed in the
macro dialog
when the user runs the macro so I'm
going to provide
very detailed description
filled in the name
Department employees
eat number thank
camps number and
salt code
in the expenses lame
form
an outlet okay to begin recording my
micro
I'm not going to click in cell b3 to
make it the
active cell this is a potential pitfall
if cell the three was already the active
cell
when you began recording the macro and
then you simply
tight in your name the recorder will
place your name
into whatever cell was the active cell
when the macro is run
and this may not be be three
for this reason how do I started to
record with
cell b3 as the active sound I'd have to
click
away from b3 and then back in to be
three
gain to explicitly record my intention
to move the cursor to sound be three
now I'm going to fill in each box with
fictitious details
for the name I'll have elvis presley a
press ENTER twice
for the department I'll have rock and
role
for the employee number and have EP
999
for the bank account number 102 time 000
1,2,3,4,5 now there's going to be a
problem
when I press the Enter key because
excels kind you
interpret this as a number I press the
Enter key
now and you can see that the leading
zeros
have been stripped away and that's not
what I'd want
for a bank account number so the easy
solution to this
is too tight an apostrophe fast this
signals to excel
that the following number is actually a
textual found you
and then 000 1,2,3,4,5
and when I press the and Turkey this
time it's interpreted
is taxed so I retain the leading zeros
for the so-called I'll have 32 77
6 three
to make this phone even easier to use
for my years as
I'm going to position the cursor to sell
816
because it slightly that after filling
in the header
the user is going to want to and to the
claim details
and now I'll stop recording as you know
there's many ways to do this
one is to click the stop button at the
bottom after the status bar
another is if you tap on the ribbon and
in the macros group
stop recording and the third way is the
developer Tom
on the rhythm and in the code group
stopped recording
I think I'll use this method and click
stop recording
I'm a macro is now saved
now that's test the macro so I'll delete
the current contents
have the cells on the screen out like a
way to a different cell just to prove
that sell b3 is being explicitly
selected
and to run the macro out like view on
the ribbon
and in the macros group macros then
view macros that's my fill in expense
for Mac right
and notice that you can see the
description at the bottom of the dialog
that I entered before I recorded the
macro al now click
run and the claim form is completed and
the customers
to sell a sixteen ready for me to begin
entering my claim details
let's do it one more time using the
keyboard shortcut
so I'll remove the text click anywhere
on the screen
and control shift E
and the macro runs and populates the
fields as before
don't save your work yet but keep the
workbook open
because you need to understand more
about security
and workbook for maps before you can
save
a work bag that contains a macro you're
going to save this work that
in the next lesson lesson 8 12
understand macro security
that we're now finished with this lesson
so use now completed
lesson 8 11 recalled micro
with absolute references
do