Excel VBA Programming - Getting Started | 8 - Absolute vs Relative References I

Summary

This 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

00:00

📊 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.

05:00

🔄 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.

Keywords

💡Absolute References

Absolute references in Excel refer to cell addresses that remain constant regardless of where the formula is copied or moved. In the context of the video, absolute references are the default setting, meaning macros will always refer to the exact cell address, such as B1. This ensures the macro will perform actions on the specified cell location consistently, regardless of the starting point.

💡Relative References

Relative references in Excel adjust based on the position of the cell where the formula or macro is used. The video explains that macros recorded with relative references navigate based on their position relative to the starting cell. This allows for more flexibility, as the macro can be applied to different cell ranges without being tied to a specific cell address.

💡Macro Recorder

The macro recorder in Excel is a tool that captures the actions performed by the user and translates them into VBA code. The video emphasizes how the macro recorder interprets cell navigation differently based on whether absolute or relative references are used. Understanding this tool is crucial for automating tasks in Excel.

💡VBA (Visual Basic for Applications)

VBA is a programming language used within Excel to create macros and automate tasks. The video mentions the VBA editor where recorded macros are displayed, showing the difference in syntax between absolute and relative references. VBA enables users to customize and enhance their Excel functionality.

💡Cell Navigation

Cell navigation refers to the movement between cells within an Excel worksheet. The video discusses how cell navigation is interpreted by the macro recorder, with absolute references focusing on specific cell addresses and relative references considering the steps taken from the starting cell.

💡Active Cell

The active cell is the currently selected cell in an Excel worksheet. In the video, the active cell is used as the starting point for recording macros with relative references. The actions are then recorded relative to this cell, allowing the macro to be more adaptable.

💡Offset Method

The offset method in VBA is used to move a certain number of rows and columns from a specified cell. The video explains how this method is utilized in relative references to navigate to a cell based on its position relative to the active cell. For example, moving zero rows down and one column to the right.

💡Range

A range in Excel refers to a selection of one or more cells. The video shows how ranges are defined differently in macros recorded with absolute and relative references. Absolute references use fixed ranges like B1, while relative references use offsets to define ranges dynamically based on the starting cell.

💡Macro Execution

Macro execution refers to running a recorded macro to perform its programmed tasks. The video demonstrates executing macros with both absolute and relative references, showing the different outcomes based on the initial cell selection. Understanding macro execution is key to effectively using macros in Excel.

💡Control Plus Enter

Control Plus Enter is a keyboard shortcut in Excel used to enter data into a cell without moving the selection to another cell. In the video, this action is used during macro recording to input a value into a cell and stay in the same cell. This ensures that the macro records the data entry accurately without altering the cell focus.

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