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

[berkeman]

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!

 

[Melbourne Guy]

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 like Serena]

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.

 

[berkeman]

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"

 

[I like Serena]

Recorded macros are usually stored as absolute cell references. Those won't survive deleting rows or moving cells around.

 

[berkeman]

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

 

[berkeman]

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.

 

[I like Serena]

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.

 

[berkeman]

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.

 

[phinds]

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.

 

[I like Serena]

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.

 

[Melbourne Guy]

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.

 

[phinds]

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

 

[pbuk]

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.

 

[pbuk]

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.

 

[berkeman]

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

 

[pbuk]

for future use with macros (I was about ready to give up on them).

O dear - just because workarounds exist is no reason not to give up on VBA if you have the choice.

 

[hutchphd]

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.

 

[I like Serena]

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.

 

[phinds]

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

 

[I like Serena]

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.

 

[phinds]

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.

 

[BWV]

Think it was mentioned above, but here is some info on referring to cells w index numbers

https://docs.microsoft.com/en-us/of...-ranges/refer-to-cells-by-using-index-numbers

does not solve the basic problem of the data in cell B3 becoming data in cell B2 if a row above is deleted

you could set the code to find a reference point and use the index references from that

 

[pbuk]

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

moveableCellAddress = Range("C7").Value
moveableCellValue = Range(moveableCellAddress).Value

 

[I like Serena]

Let me provide a quick getting started guide to use named ranges.

In Excel we can do:

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

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

And in VBA we can use:

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.

 

[pbuk]

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.

 

[I like Serena]

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.

 

[pbuk]

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.