Should Excel macros self-adjust when you delete lines above the affected cells?

In summary: Just one that you name "Refs." and put all your references in that column.Something I do, since I have a sheet with numerous references but to which I add/subtract rows all the time, is that I create a hidden column ** and in it I put a code for each line I need to tie a reference to. For example, my "end of data" code is "xxx". I then create a subroutine for which the pseudocode is "find me the row that has the code <specify code>. This gives me the absolute cell reference but it does it in real time so adjusts for any added/subtracted rows.This
  • #1
berkeman
Mentor
68,057
21,599
I just wanted to check to see if this is a known problem, or if my issue is more likely related to something different.

I have an Excel spreadsheet that has a number of lines with comments and other information at the top, and then a matrix of cells below that on several rows. I had some Excel macros working to exchange information between the cells in those rows, but after deleting a couple extra comment lines above those rows, the macros are doing some nutzoid things in terms of moving cells.

I expected that the macro cell references would have been adjusted just like normal Excel cell references are when you delete rows or columns, but so far it looks like that did not happen. This problem may have been caused by a strange laptop crash/OS update around the same time, so that's why I'm asking if Excel macros have a known issue. Figuring that out will help me to do a better job of going back to my last known good check-in of my file.

Thanks!
 
Technology news on Phys.org
  • #2
Hard to know without seeing the spreadsheet, @berkeman, but within-cell references do normally adjust for deletes, however named cells may not and macros that use absolute cell references won't cope either.
 
  • #3
It's not unusual that deleting cells messes up formulas and/or macros. If a macro has a hard reference to A3, and a line at the top was deleted, then the macro does not refer to the correct cell anymore.
 
  • #4
Melbourne Guy said:
Hard to know without seeing the spreadsheet, @berkeman, but within-cell references do normally adjust for deletes, however named cells may not and macros that use absolute cell references won't cope either.
I can upload the *.xlsm file if needed, but usually we discourage the posting of files that can contain macros (let alone ones that explicitly contain them). I recorded the macros with copy/paste mouse clicks, so pretty generic cell references in the macros. I'll look through the updated macro files tomorrow to see if the differences are obvious after the file update. Thanks.

File name after changes:
"Rubiks Cube 2x2 Macros Simplified3 Only 2 Temp Variables 19-Aug-2022.xlsm"
 
  • #5
Recorded macros are usually stored as absolute cell references. Those won't survive deleting rows or moving cells around.
 
  • #6
I like Serena said:
Recorded macros are usually stored as absolute cell references. Those won't survive deleting rows or moving cells around.
Yikes, absolute references would be bad for edit updates. From using the Macro, Edit feature, so far they look like relative references...

1660958275261.png
 
  • #7
Just for full disclosure, this project is just for fun to code up a Python Rubik's Cube program to help me work on getting better at solving the 3x3 Cube. This is a simplified version of a 2x2 cube in Excel to help me figure out the best way to code the full GUI program in Python + Tk.

1660958773309.png
 
  • Like
Likes Wrichik Basu
  • #8
Those are hard absolute references. Note that "F27" won't change if you delete the top row.
In a regular formula it's a relative reference. But even absolute references in formulas will be automatically adjusted. Not in a macro though.
 
  • Informative
  • Like
Likes BWV and berkeman
  • #9
I like Serena said:
Those are hard absolute references. Note that "F27" won't change if you delete the top row.
In a regular formula it's also a relative reference, but there it will be automatically adjusted. Not in a macro though.
Oh my. Thanks ILS. That would explain what happened. Time to revert back one revision and keep working from there (more carefully until I transition from the Excel experiments to the Python+Tk version.
 
  • #10
berkeman said:
Oh my. Thanks ILS. That would explain what happened. Time to revert back one revision and keep working from there (more carefully until I transition from the Excel experiments to the Python+Tk version.
Something I do, since I have a sheet with numerous references but to which I add/subtract rows all the time, is that I create a hidden column ** and in it I put a code for each line I need to tie a reference to. For example, my "end of data" code is "xxx". I then create a subroutine for which the pseudocode is "find me the row that has the code <specify code">. This gives me the absolute cell reference but it does it in real time so adjusts for any added/subtracted rows.

This would be tedious if all of your references are to set cells that can float. Most of mine are to cells within an unchanging number of cells before or after a coded row. E.G. once I have the absolute row number of the row marked "xxx" I can go to that row plus or minus a number. I keep several rows on both sides of the xxx row and their relative position doesn't change so if my macro knows where row xxx is, it knows where they are.

** it doesn't HAVE to be a hidden column, I just do it that way for aesthetics since the sheet is QUITE crowded/complex.
 
Last edited:
  • Like
Likes berkeman
  • #11
I recommend naming cells and ranges if we refer to them and if they are not immediately above or to the left or some such. It makes the formulas readable and it also makes them resilient against moving and deleting cells.
 
Last edited:
  • Informative
Likes berkeman
  • #12
One other way to use relative references in the macro is with a unique text to label in a cell as the start point and use the search function to locate that unique text before your macro commences its operational manipulation.
 
  • Informative
Likes berkeman
  • #13
Melbourne Guy said:
One other way to use relative references in the macro is with a unique text to label in a cell as the start point and use the search function to locate that unique text before your macro commences its operational manipulation.
Yes, that's exactly what I said in post #10
 
  • Like
Likes berkeman
  • #14
This is one reason why Excel VBA is a nightmare to develop for.

A couple of workarounds already suggested, another one is to use the CELL function referring to the "moving" cell in another one that doesn't move and read the address from there in the macro.
 
  • #15
phinds said:
Yes, that's exactly what I said in post #10
I don't think it was - nothing about searching (VBA Find function) in your post.
 
  • #16
Thanks everybody! You've given me several great ideas for workarounds in this spreadsheet, and for future use with macros (I was about ready to give up on them). :smile:
 
  • #17
berkeman said:
for future use with macros (I was about ready to give up on them). :smile:
O dear - just because workarounds exist is no reason not to give up on VBA if you have the choice.
 
  • Haha
  • Like
Likes I like Serena, phinds and berkeman
  • #18
berkeman said:
Thanks everybody!
You know in about five years that 2x2 Rubik's Cube might be a perfect birthday gift for me. Just sayin. Might be a business opportunity: gifts for all ages.
 
  • Haha
Likes anorlunda and berkeman
  • #19
pbuk said:
O dear - just because workarounds exist is no reason not to give up on VBA if you have the choice.
I also do first explorations with Excel before switching to a real programming language.
Generally I stick to regular formulas though and avoid VBA, and certainly recorded macros.
In this case for doing something with a Rubik's cube I guess there is no real choice. A proof of concept will have to use VBA. That, or just switch to a real programming language and perhaps copy-and-paste results back into Excel.
 
  • Like
Likes pbuk and berkeman
  • #20
I like Serena said:
... or just switch to a real programming language and perhaps copy-and-paste results back into Excel.
If you are going to use a "real programming" language why would you want to bother having to cut/paste the results back into Excel? Why not just program it using the Excel library to put it directly into Excel?
 
  • #21
phinds said:
If you are going to use a "real programming" language why would you want to bother having to cut/paste the results back into Excel? Why not just program it using the Excel library to put it directly into Excel?
I use Excel just for a proof of concept, and quick-and-easy calculations and visualizations. When I've got something properly working, I stop using excel. I consider excel then too cumbersome, not robust enough, and unable to handle large data sets. I might still use it to make nice graphs for a report though.
 
  • Like
Likes berkeman and pbuk
  • #22
I like Serena said:
I consider excel ... unable to handle large data sets.
No argument with you there. I never deal with large data sets though, and I find Excel w/ VBA to be a very robust platform for my needs.
 
  • #24
There are a couple of good workarounds posted above, another that I have used in the past which is pretty robust and auditable is to have a cell in a fixed location (lets assume C7) that refers to the "movable" cell with the formula CELL("address",$AZ$239). Then in VBA you can do
Excel VBA:
moveableCellAddress = Range("C7").Value
moveableCellValue = Range(moveableCellAddress).Value
 
  • Informative
Likes berkeman and phinds
  • #25
Let me provide a quick getting started guide to use named ranges.

In Excel we can do:
1661337492589.png

We can select the "C4" at the left top and change it into "SpeedOfLight".

1661337510188.png


Now that cell is named "SpeedOfLight" we can use it in formulas like this:
1661337928764.png


And in VBA we can use:
Code:
Range("SpeedOfLight").Value
The sheet and the macros are robust against deleting cells and moving cells around now.
And both the regular formulas and the VBA macros are actually readable, making them maintainable.

Furthermore, Excel has quite neat facilities to update existing formulas elsewhere so that they start referring to the named range instead of "C4". And also to instantly create a set of names rather than typing them manually one by one.

In the case of the Rubik's cube we can identify the whole area with a name, and then index that area like a matrix in VBA.
 
Last edited:
  • Like
  • Informative
Likes berkeman and BWV
  • #26
I like Serena said:
I'll save that for a later tutorial.
You seem to be a fan of named ranges in Excel.

There is an opposing school of thought among advanced Excel modellers which believes that in general, named ranges cause more problems than they "solve", and in most cases there is a more robust and maintainable solution that avoides them.
 
  • #27
pbuk said:
You seem to be a fan of named ranges in Excel.

There is an opposing school of thought among advanced Excel modellers which believes that in general, named ranges cause more problems than they "solve", and in most cases there is a more robust and maintainable solution that avoides them.
Can you provide more details? I've never heard of problems that named ranges cause.

Either way, I do believe that referring to cells with "codes" like "C7" is a bad idea. It's not clear what "C7" is and there is no guarantee it will remain where it is. Of course it _is_ a way to work around some cell deletion issues, but it won't solve deleting lines at the top.
 
Last edited:
  • #28
I like Serena said:
Can you provide more details? I've never heard of problems that named ranges cause.
Three main areas:
  • not being able to see what cell(s) a formula refers to when editing e.g. when using a named range for a lookup you can't easily see that the lookup includes all the cells it is supposed to (often compounded by lack of understanding of the importance of "barrier cells").
  • slowing down incremental development e.g. I am calculating gas volume for a range of input pressures named "InputPressures". I then decide I want to compare this with another set of inputs so I create "InputPressures2". Now I want to create 100 different input pressures...
  • Scoping conflict/confusion (names can be scoped either "global" i.e. scoped to the workbook or "local" i.e. scoped to the sheet) e.g. working with the InputPressures in the previous example, I create another sheet linked to it which displays some charts; now I decide to create a different scenario and copy the sheet with the InputPressures and calculations. Now it is easy to get confused between what InputPressures refers to in the different contexts.
 

FAQ: Should Excel macros self-adjust when you delete lines above the affected cells?

1. What is an Excel macro?

An Excel macro is a set of instructions or commands that automate tasks in Microsoft Excel. It can be created using the Visual Basic for Applications (VBA) programming language and can perform a variety of actions, such as calculations, formatting, and data manipulation.

2. Why would I need to use an Excel macro?

Excel macros can save time and reduce the potential for human error by automating repetitive or complex tasks. They can also be used to create customized functions and perform calculations that are not available in the standard Excel functions.

3. Do Excel macros self-adjust when I delete lines above the affected cells?

No, Excel macros do not automatically adjust when you delete lines above the affected cells. Any references to cells or ranges in the macro will remain the same, which may cause errors or unexpected results if the referenced cells are deleted.

4. How can I make my Excel macro self-adjust when I delete lines above the affected cells?

To make an Excel macro self-adjust when you delete lines above the affected cells, you can use relative cell references instead of absolute cell references. This means that the macro will refer to cells based on their position relative to the current cell, rather than a specific cell address.

5. Are there any drawbacks to using self-adjusting Excel macros?

While self-adjusting Excel macros can be convenient, they may also be less precise and more prone to errors. If the structure of the workbook changes significantly, the macro may need to be updated or rewritten. It is important to thoroughly test and debug any self-adjusting macros to ensure they are functioning correctly.

Back
Top