Excel VBA Programming - Getting Started | 8 - Absolute vs Relative References I
Summary
TLDRThis lesson explores the differences between absolute and relative references in Excel macros. Absolute references target specific cell addresses, ensuring consistent cell navigation, while relative references depend on the starting cell's position, allowing for more flexible macro application. The instructor demonstrates recording two macros: one with absolute references and one with relative references, highlighting how each method affects the macro's behavior. By the end, learners will understand the significant impact of choosing the correct reference type for successful macro execution.
Takeaways
- 🗂️ Absolute references in Excel macros refer to a fixed cell address, such as B1.
- 🔄 Relative references in Excel macros refer to a position relative to the starting cell.
- 📌 The default setting in Excel is absolute references.
- 💡 Absolute references ensure macros target the exact same cell every time.
- 🛠️ Relative references allow macros to be more flexible and used in different locations.
- 🎥 Demonstrating both types of macros helps to understand their differences.
- ✍️ Absolute reference macros record specific cell addresses, while relative reference macros record navigation steps.
- 📄 VBA editor shows absolute macros with fixed cell ranges and relative macros with offset methods.
- 🔍 Deleting unnecessary VBA code can make macros more readable and compact.
- 📝 Relative reference macros adapt to the starting position, making them versatile.
Q & A
What are absolute references in Excel macros?
-Absolute references in Excel macros note the actual address of the cell being navigated to, meaning the cell location is fixed and definite.
What are relative references in Excel macros?
-Relative references in Excel macros consider cell navigation relative to the starting cell, focusing on the sequence of steps to get to the destination rather than the final cell address.
How does the macro recorder interpret cell navigation by default?
-By default, the macro recorder in Excel uses absolute references, which means it records the exact cell address that is being navigated to.
Why might you prefer to use relative references when recording macros?
-Relative references allow the macro to be used in various locations because they are not tied to a specific final cell address, making them more flexible and less constrained.
How can you enable relative references in the macro recorder?
-You can enable relative references in the macro recorder by selecting the 'Use Relative References' option, which will be indicated by a gray background under the setting.
What is the key difference between the VBA code generated for absolute and relative macros?
-The key difference is that absolute macros have fixed cell references (e.g., 'Range("B1").Select'), while relative macros use the 'ActiveCell.Offset' method to navigate relative to the starting position.
How do absolute and relative references affect the outcome of running a macro?
-Absolute references will always navigate to and perform actions on the same fixed cell, whereas relative references will navigate and perform actions based on the position relative to the starting cell.
What does the 'Offset' method do in a relative macro?
-The 'Offset' method in a relative macro moves the selection from the active cell by a specified number of rows and columns (e.g., 'ActiveCell.Offset(0, 1)' moves one column to the right).
Why might the macro recorder generate extra, unnecessary code?
-The macro recorder might generate extra, unnecessary code because it records every action performed, including those that may not be essential for the macro's functionality.
What is the significance of comments in VBA code generated by the macro recorder?
-Comments in VBA code, indicated by lines starting with an apostrophe, are used to leave notes and are ignored by VBA during execution. They help programmers understand the code but do not affect the macro's functionality.
Outlines
📊 Understanding Absolute and Relative References in Excel
This lesson covers the differences between absolute and relative references in Excel, highlighting how these affect cell navigation and macro recording. The default setting in Excel is absolute references, which means that macros record the exact cell address, ensuring that the macro always references a specific cell. In contrast, relative references record navigation steps relative to the starting cell, allowing for more flexibility and adaptability in different locations within the workbook.
🔄 Recording Macros with Absolute and Relative References
The lesson demonstrates recording two macros: one with absolute references and one with relative references. The absolute macro records navigation to cell B1 and entering a value, while the relative macro records the navigation steps relative to the starting cell. This practical example shows how the macro recorder interprets cell movements and how these macros differ in execution.
Mindmap
Keywords
💡Absolute References
💡Relative References
💡Macro Recorder
💡VBA (Visual Basic for Applications)
💡Cell Navigation
💡Active Cell
💡Offset Method
💡Range
💡Macro Execution
💡Control Plus Enter
Highlights
Introduction to differences between absolute and relative references in Excel macros.
Default setting in Excel is absolute references.
Absolute references mean macros note the actual address of the cell navigated to.
Absolute references are definite, final, or fixed cell locations.
Relative references consider navigation relative to a starting cell.
Macros with relative references look at navigation as a sequence of steps.
Relative references allow macros to be used in more locations.
Recording two macros: one with absolute references and one with relative references.
Example macro with absolute references: moving from cell A1 to B1 and entering a value.
Example macro with relative references: moving from cell A1 to B1 and entering a value.
Examining the code for absolute and relative macros in the VBA editor.
Absolute macro code selects a fixed range, while relative macro code uses active cell and offset.
Running the absolute macro writes the value to cell B1 regardless of the starting cell.
Running the relative macro writes the value to a cell relative to the starting position.
Summary: Absolute references target specific cells, while relative references target cells based on the starting position.
Impact of choosing the correct reference type on macro success.
Encouragement to practice recording macros with both reference types.
Transcripts
in this lesson we'll discuss the differences between absolute references and relative
references these are two different ways of looking at cell navigation in our Excel workbook and they
greatly affect how the macro recorder is going to interpret all of our navigation the default
setting in Excel is absolute references which means that macros will note the actual address
of the cell that we are navigating to for example if I have absolute references enabled which is
default and I click in a macro into cell B1 the VBA recorder will interpret that as a absolute
reference to cell B1 you can think of the word absolute as meaning definite or final or fixed if
somebody tells you I know I'm 100 absolutely right you know that they're 100 certain of
something and in this case it's the exact same kind of idea the place where we're going to end
up the cell location the range is certain it's guaranteed it's always the same thing if we go
into cell b one had the macro recorder will treat that as an actual reference to cell B1
in comparison macros recorded with relative references are relative they look at navigation
as relative to a starting cell compared to a starting position while absolute references
are more concerned with the final destination where you end up relative references are more
concerned with how you get there so the way the macro recorder looks at navigation is in a
sequence of steps like move two cells to the right and move three cells down it doesn't matter what
the final cell is it matters the Journey of steps to get there which allows macros that are recorded
with relative references to be used in a lot more locations because they're not so constrained they
don't have so many limitations because they're not bound to ending up in any specific location the
easiest way to demonstrate the difference between these two is to actually record a macro with both
so in this lesson we're going to record two macros one with absolute references and one with relative
references it's going to be the simplest kind of macro we're going to put our a cell cursor
into cell A1 click into there and in both of our macros as soon as we start recording we're going
to navigate one cell over to B1 enter a value in the cell and then just end the macro completely
and that's it so let's begin with absolute references I'm going to click record macro
I'm going to give this macro a name of move right absolute this is an example of absolute
references let's click OK right here and we are actively recording so I'm going to press the
right arrow key on my keyboard to go into cell B1 I'm going to type in a value here let's say hello
and I'm going to press Ctrl plus enter to enter the value in the cell without moving so Control
Plus enter okay looks good to me so I'm going to click stop recording and we have our very
first macro next up is a macro using relative references so I'm going to delete my value in
cell B1 I'm going to navigate back into cell A1 this time I'm going to enable the use relative
reference setting right here you'll know that it's activated because it's going to
have a gray background under the setting so we're all good here we can now record a macro
with relative references let's call this macro move right relative let's click OK here we are
now recording once again exact same procedure right arrow key to go to cell B1 I'm going to
enter the value hello Control Plus enter okay it's entered in the cell and now I can stop recording
let's take a look at both of these macros in our actual VBA editor so I'm going to open up Visual
Basic you can do that right here by clicking this key and right here under modules we're
going to have module one that's where all of our macro code will be collected let's open that up
these little lines in green are what's known as comments they are completely ignored by VBA
they're just used to leave notes behind for programmers and the macro recorder will make
a couple of them for us but they're negligible so we can go ahead and delete them just to make these
macros a bit more Compact and readable so let's delete the ones there as well as the ones in the
second macro below let's also buy ourselves some space so we can compare these macros
and here we have it so I know that some of the syntax is still going to be a bit unfamiliar but
hopefully you'll get the general gist of what's going on here at the very top we have our absolute
macro the one recorded with absolute references and just as I mentioned earlier notice that all
of the cell references the range references are definite they're absolute they're fixed the very
first line of code here selects the range that encapsulates cell B1 right a literal reference
to B1 this second line of code which is actually identical between the two macros simply writes the
value hello to that cell the more important line is the very first one we're selecting uh cell B1
explicitly in comparison in the relative macro we are starting at the Active cell which represents
the currently activated cell the one that the user selected and we're navigating with this offset
method we're going to dive into that a little bit later but what it does is it is it essentially
navigates it moves to somewhere else in the spreadsheet and this zero one syntax means 0
0 rows down one column over so we're moving over relative to our original cell one column over
this syntax right here range A1 is a little bit confusing because this is a relative macro it's
actually a little bit of what I call that extra useless VBA code that the recorder generates for
us we can actually go ahead and remove this it's actually a little bit confusing and the macro will
still work regardless this is much better so we're starting at an active cell we're moving
over one column to the right we're selecting that cell and then in that selected cell we're writing
the exact same value hello so this one is relative from where we start and this one will always end
up in cell B1 now let's take a look at how these macros will actually work in our Excel workbook
I'm going to go ahead and delete the value in cell B1 and the important thing here is that I'm
actually going to navigate to some random cell in the workbook it doesn't really matter where
so let's say D6 for me it doesn't matter where you go I'm in cell D6 I just want to make sure I want
to turn off this reference it won't really matter because the macro has already been recorded I just
want to go back to excel's default let's go ahead and run our two macros in a row so I'm going to
open up my macros dialog box here it is and let's execute move right absolute when I execute this
we're starting in cell D6 but what happens here well we have our value of hello written in cell B1
why well because our macro is absolute so when we recorded it remember we literally referenced cell
B1 that's how it interpreted our movement one cell over it's it's said to itself oh they moved into
cell B1 and then they wrote the value hello in there that's an absolute reference that's how I'm
going to record it let's go ahead and delete this value in cell B1 let's go back into another cell
I'm just going to go into D6 again just to kind of have an equal experiment set up but this time
when I execute a macro I'm going to execute move right relative let me just move this box a little
bit out of the way here and execute this and now we're going to end up in cell E6 why how come
when we recorded the original macro it was cell B1 but now we're in E6 the answer of course is
because of relative references relative references don't care about the Final Destination they're
recording relative to where we started from so when we started in cell A1 relative references
interpreted cell B1 as one cell to the right so when we started the macro from cell D6 it once
again applied the same methodology one cell to the right then write the value hello and thus the
value ended up in cell E6 to summarize absolute references Target a specific cell a specific
location while relative references Target a cell relative to a starting position and as much as it
may seem like a small difference it actually has a huge impact on how successful a macro can run you
really have to know which option you should use if you're going to be using the macro recorder but
that's all there is to cover in this lesson I hope you're excited to tackle your very next challenge
Voir Plus de Vidéos Connexes
5.0 / 5 (0 votes)