Up next in 10
How to Record Dynamic and Relative Macros—Even If You Don't Know VBA
50 views
May 22, 2025
In this tutorial, we take a recorded Excel macro and rewrite it into a cleaner, more flexible version using VBA. You'll learn the difference between absolute and relative references in recorded macros, and how to use ActiveCell, Offset, and Resize to create relative references in VBA. 00:00 Recording a Basic Macro to Apply Multiple Formatting 02:39 Recording a Macro to Record Multiple Actions 06:04 Enable "Use Relative References" Option 06:26 Recording a Dynamic Macro using Relative References 09:50 Writing Macros Directly Using VBA 10:58 Explaining Absolute and Relative References in Context of VBA 11:59 Rewriting the Recorded Macro using VBA to Optimize its Code In this tutorial will help you write smarter macros and understand what's going on behind the scenes.
View Video Transcript
0:00
hello and welcome let's get started by
0:02
recording a simple macro that applies
0:04
several different formatting options to
0:06
the currently selected cells first go to
0:08
the view tab on the ribbon click the
0:10
macros drop down and choose record macro
0:13
this opens the record macro dialogue box
0:16
here give a macro a name the name must
0:18
not contain spaces or special characters
0:21
you can use underscores if needed the
0:23
name is the only required field if you
0:26
like you can also assign a keyboard
0:28
shortcut which lets you run the macro
0:29
instantly without using the ribbon this
0:32
can make your work faster and more
0:33
efficient you can also choose where to
0:35
store the macro in the store macro in
0:38
drop down select this workbook if you
0:40
want to save the macro in your current
0:41
file choose new workbook if you want to
0:44
record the macro in a new file or select
0:47
personal macro workbook if you want the
0:49
macro to be available globally so it
0:51
works in any workbook you open for this
0:54
tutorial we'll keep the default option
0:56
which is this workbook you can also
0:58
write a short description of the macro
1:00
to help explain what it does now click
1:02
okay to begin recording excel is now
1:05
recording your actions you can confirm
1:07
this by looking at the bottom left
1:08
corner of the window you'll see a small
1:10
square icon in the status bar hovering
1:12
over it shows the message a macro is
1:15
currently recording click to stop
1:17
recording let's record some cell
1:19
formatting steps we won't type anything
1:21
or change the cell selection we'll
1:24
simply format the cell that's already
1:25
selected since we're now recording a
1:27
macro every action we take will be
1:30
recorded including any cell selection
1:32
changes so it's important to stay on the
1:34
current cell now go to the home tab and
1:37
in the font group apply a fill color for
1:39
example brown then change the font color
1:41
to white select a different font
1:44
style and finally apply borders let's go
1:48
with all borders
1:50
once you've finished applying the
1:52
formatting go back to the view tab open
1:54
the macros drop down and click stop
1:56
recording your macro has now been
1:58
recorded every action you performed from
2:00
formatting to styling has been saved to
2:03
use the macro first select a different
2:05
set of cells then go to the view tab
2:08
click on macros and choose view macros
2:10
select your macro from the list and
2:12
click run you'll see that Excel
2:14
instantly applies the same formatting
2:16
you recorded try running the macro on
2:18
other cell selections to see it in
2:20
action it will always repeat the exact
2:23
steps you recorded this kind of macro is
2:25
especially useful when you want to apply
2:27
the same set of formatting actions over
2:29
and over instead of repeating those
2:31
steps manually you can simply select the
2:33
cells and run the macro it saves you
2:36
time and keeps your formatting
2:38
consistent let's create another macro
2:41
unlike the previous one where we only
2:43
formatted the selected cell this time
2:45
we'll enter text into cells move the
2:47
selection and apply formatting for this
2:50
example we'll create a simple weekly
2:52
sales report that includes three
2:53
columns: day date and sale just like
2:57
what you see on the screen each time we
2:59
run this macro it will automatically
3:01
generate the days and corresponding
3:03
dates the sale column will stay empty so
3:06
it can be filled in manually later let's
3:08
begin by recording the macro go to the
3:10
view tab click the macros dropown and
3:12
choose record macro enter a name for
3:15
your macro then click okay to start
3:24
recording by default cell A1 is selected
3:28
in cell A1 type day in B1 type date in
3:32
C1 type sale now in cell B2 enter
3:35
today's date then use the fill handle to
3:37
drag this formula down to B8 filling the
3:40
next seven days next go to cell A2 and
3:43
enter the formula shown on screen this
3:46
extracts the day name from the date in
3:48
cell B2 now double click the fill handle
3:50
on A2 this automatically fills down the
3:53
formula to match the entries in column B
3:55
to finish the table select the header
3:57
row and apply bold formatting once done
4:00
go back to the view tab click the macros
4:02
dropown and choose stop recording your
4:05
macro is now ready let's test it open a
4:07
new worksheet and run the macro you'll
4:09
see that it recreates the entire sales
4:12
table exactly as
4:18
recorded try again on another new sheet
4:21
the macro writes the table starting from
4:23
cell A1 to C8 just like
4:26
before now let's try something different
4:29
select cell G6 and run the macro again
4:32
you'll notice that only the word day
4:34
appears in cell G6 and nothing else is
4:37
created why did that happen well by
4:39
default Excel macros record with
4:41
absolute references they store exact
4:44
cell locations like A1 B2 etc so if the
4:48
macro recorded all actions starting from
4:50
A1 and you move the selection to G6 only
4:53
the very first step typing day tries to
4:56
run at G6 while the rest still target
4:58
the originally recorded locations
5:02
let's do one more test open a new sheet
5:05
select cell I7 and run the macro this
5:07
time you'll see the word day appears in
5:10
cell I7 but the rest of the table is
5:13
written again in A1 to C8 just like
5:16
before why is this happening by default
5:18
Excel records macros using absolute
5:21
references this means it not only
5:23
records your actions but also the exact
5:25
cell locations where those actions
5:27
occurred including any selections made
5:29
using the mouse or keyboard so when we
5:32
recorded the macro we typed day into
5:35
cell A1 without changing the selection
5:37
which is why day appears in the
5:39
currently selected cell when the macro
5:40
runs however when we enter the date we
5:44
manually selected cell B2 that change in
5:46
selection was also recorded along with
5:49
every other cell movement and action as
5:51
a result each time you run the macro
5:53
Excel repeats not just what you did but
5:56
where you did it jumping to those exact
5:58
cells if you want the macro to work
6:00
based on your current selection rather
6:02
than fix cell positions you can enable
6:04
the use relative references option
6:06
before starting the recording this way
6:08
the macro adapts to wherever your active
6:10
cell is when it's run to enable the use
6:12
of relative references go to the view
6:14
tab click the macros dropown and select
6:17
use relative references from this point
6:19
on Excel will record your actions using
6:22
relative references instead of absolute
6:24
ones let's start recording the macro
6:26
this time we'll name it relative ref
6:29
we'll repeat the same steps as in the
6:30
previous macro named brain bell but now
6:33
everything will be recorded relative to
6:35
the current selection first we create
6:37
the header row starting from cell A1
6:40
type day in A1 date in B1 and sale in C1
6:44
then in cell B2 enter the formula today
6:47
this function returns the current date
6:49
however since it's a volatile function
6:51
it will update every time Excel
6:53
recalculates which is not what we want
6:56
to prevent that we right click on cell
6:58
B2 choose copy then rightclick again and
7:01
choose paste values this converts the
7:03
dynamic formula result into a static
7:05
date all these steps are now being
7:07
recorded in the macro including entering
7:09
the today function and replacing it with
7:11
a fixed value notice that we didn't just
7:13
type today's date manually instead we
7:16
use the today function first and then
7:18
replace it with its result this method
7:20
ensures that each time the macro runs it
7:23
captures the current system date and
7:24
then freezes it so the table always
7:26
starts from the day the macro is run but
7:29
won't change afterward next use the fill
7:31
handle to drag the date down from B2 to
7:34
B8 filling seven consecutive dates now
7:37
in cell A2 enter the text formula as
7:39
shown on screen to extract the day of
7:41
the week then double click the fill
7:43
handle to automatically fill the day
7:45
column down to A8 finally select the
7:48
header row and make the text
7:50
bold now go to the view tab click the
7:53
macros drop down and choose stop
7:55
recording that's it we've recorded a
7:57
macro using relative references from now
8:00
on whenever you run this macro it will
8:03
insert the sales table starting from the
8:05
cell you currently have selected the
8:07
macro adapts to your selection making it
8:09
flexible and easy to use anywhere in
8:12
your worksheet try selecting different
8:13
cells and run the macro again you'll
8:16
notice that the entire table adjusts its
8:18
starting position based on where your
8:20
selection is that's the power of using
8:22
relative references earlier I mentioned
8:25
that we used the today function while
8:27
recording this macro and then converted
8:29
it to a static value let's see why
8:31
that's useful go to your systems date
8:33
settings and change the date to February
8:35
4th 2024 or you can choose any past or
8:38
future date then come back to Excel when
8:41
I run the relative ref macro it creates
8:44
the sales table starting from February
8:46
4th 2024 all the way to February 10th
8:50
that's because Excel reads the date from
8:52
your operating system
8:54
now if I run the previous macro named
8:56
brainbell you'll see it always creates
8:58
the same date range from May 14th 2025
9:02
to May 20th 2025 that's because during
9:05
recording we manually typed the date
9:07
using the keyboard shortcut control
9:09
semicolon so the date was fixed and
9:12
doesn't change with the system date in
9:14
contrast the relative ref macro is more
9:17
dynamic it uses the current date each
9:19
time it's run and also adapts the
9:21
starting location based on your current
9:27
selection when you record a macro every
9:30
action you take whether it's selecting a
9:32
cell formatting or entering text is
9:35
actually written as VBA code in a
9:37
subruine the name you give your macro
9:40
becomes the name of that subruine so in
9:43
fact macros are just VBA subutines
9:45
behind the scenes now let's see how we
9:47
can create and manage macros directly
9:49
using VBA first we'll open the VBA
9:53
editor if the developer tab isn't
9:55
already visible on your ribbon
9:56
rightclick anywhere on the ribbon and
9:58
choose customize the ribbon this opens
10:00
the Excel options dialogue box on the
10:03
right hand side check the box for
10:05
developer and click okay now the
10:07
developer tab should appear on your
10:08
ribbon go to the developer tab and click
10:11
on the Visual Basic button to open the
10:13
Visual Basic editor on the right side of
10:15
the editor window you'll see the project
10:17
explorer expand the modules folder by
10:20
clicking the plus sign excel stores each
10:22
macro inside a separate module since we
10:25
created only one macro in this workbook
10:28
you'll see a single module listed module
10:31
one keep in mind we created another
10:33
macro earlier in a different workbook
10:35
which is currently closed so that one
10:37
doesn't appear here double click on
10:39
module one to open its code window
10:42
here's what the macro looks like in VBA
10:44
this code shows exactly what Excel
10:46
recorded when we created the macro
10:47
typing text into cells applying formulas
10:50
using autofill selecting ranges and
10:53
formatting columns each step is laid out
10:55
as VBA code that runs when you execute
10:58
the macro this macro uses absolute
11:01
references don't confuse this with
11:03
Excel's absolute references that use
11:05
dollar signs like dollar a dollar one in
11:07
VBA cell references like A1 B1 or C5 are
11:12
also considered absolute when used in
11:13
recorded macros because they always
11:15
point to a fixed location on a sheet
11:17
let's take a look at the code of the
11:19
macro that uses relative references
11:21
you'll notice that it uses the offset
11:23
method along with the active cell
11:24
property to select enter or format data
11:27
the offset method is what makes the
11:29
macro use relative references it moves
11:32
the selection relative to the current
11:33
active cell for example offset method
11:36
having first argument zero and second
11:39
one means stay on the same row but move
11:41
one column to the right this allows the
11:43
macro to adapt to wherever you started
11:46
instead of always targeting fixed cells
11:47
like a one or B1 so when a macro begins
11:51
at a different cell the same series of
11:53
offset instructions will build the table
11:55
in the correct position relative to that
11:57
starting
11:58
point now let's create the macro using
12:01
VBA we'll start by modifying the brain
12:03
bell macro which originally used
12:06
absolute references step by step we'll
12:09
replace the existing code with a cleaner
12:10
and more flexible version that uses
12:12
relative references as we go we'll
12:15
remove the old lines so the final code
12:17
is shorter and easier to understand
12:19
first we declare a variable named cell
12:22
and assign it the current active cell as
12:24
shown on screen this means that wherever
12:26
the active cell is when we run the macro
12:28
that cell will be stored in the variable
12:30
cell and everything else we do will be
12:32
relative to this starting point next we
12:35
use just one line to insert all three
12:37
column headings date date and sale here
12:40
resize method creates a range that's one
12:42
row tall and three columns wide and the
12:45
array function fills each cell in that
12:47
range with our headings then we bold the
12:49
heading row and that's it we've added
12:52
and formatted all the headers using only
12:54
two lines of code now delete the old
12:56
eight lines that did the same thing one
12:58
cell at a
13:01
time also delete the old line that bolds
13:04
the headings it's at the end because
13:05
that was the last step recorded we've
13:07
already done it using the resize
13:09
function so that line is no longer
13:11
needed next we insert today's date in
13:13
the cell just below the date heading
13:15
this uses the offset method to move one
13:18
row down and one column right from the
13:20
starting cell the date function returns
13:22
today's date dynamically to fill the
13:24
rest of the week we use autofill to copy
13:27
the date down for seven rows
13:29
automatically increasing each by one day
13:32
the autofill method copy that date
13:34
increasing it by one day into a range of
13:37
seven cells using the resize method in
13:39
short the offset function defines the
13:42
starting cell and the resize function
13:44
defines the size of the range we want to
13:46
fill then we make sure the column is
13:48
wide enough to display all the dates
13:50
this prevents Excel from showing hash
13:52
symbols when the date doesn't fit in the
13:54
cell now remove the old code that
13:56
inserted a fixed date manually that
13:59
version always used a hard-coded date
14:01
our new code uses the date function so
14:04
it's dynamic it always shows the current
14:06
date when a macro runs next we insert a
14:09
formula to extract the weekday name from
14:11
each date this line places the formula
14:14
one column to the left of the date
14:16
column the formula uses the text
14:18
function to convert the date into a day
14:20
name like Monday or Tuesday to fill that
14:22
formula down for the rest of the week we
14:25
use the fill down method along with
14:26
offset and resize that copies the
14:29
formula into seven rows one for each day
14:36
now remove the old lines that inserted
14:39
each formula individually we've replaced
14:41
them with just two lines of efficient
14:43
code finally we adjust the column width
14:46
of the day names and that's it our new
14:48
macro is much cleaner uses relative
14:51
references and is dynamic every time you
14:53
run it it updates based on where you
14:55
start and what day it is
15:00
with this new version of the macro
15:02
you've replaced bulky rigid code with a
15:04
much cleaner and more dynamic version
15:06
using relative references the result is
15:09
not only easier to read but also more
15:11
adaptable to wherever you run the macro
15:13
on a sheet so here are the three
15:16
versions of the macro the first one
15:18
that's our code is simple clean and easy
15:21
to follow the second one uses absolute
15:24
references and the third uses relative
15:26
references but both are longer and more
15:28
cluttered our version shown in bullet
15:31
point one is much neater and way easier
15:33
to work with hope you like the tutorial
15:35
thanks for watching