Excel should rewrite macros when adding rows or columns
 

The way that formulas rewrite themselves when adding rows or columns so that
they still apply to the right blocks is not true in the case of macros. While
it is true that the macro is more an external feature, it is terrible that
the addition of one row or column distorts the application of the macro to
every row beneath or beyond the insertion. For static workbooks / templates
the macro function is essentially worthless and editing them all for each
addition wastes all the time the macro should be saving. An internal macro
system just for Excel (the greatest program on Earth) would be fantastic.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Excel should rewrite macros when adding rows or columns
 

I wouldn't want Excel to attempt to rewrite any of my code. There
are so many ways to specify a range in VBA that the potential for
error is too high. You can avoid the problem entirely by using
named ranges on your worksheet and using those names in your VBA
code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"badorphan" wrote in
message
...
The way that formulas rewrite themselves when adding rows or
columns so that
they still apply to the right blocks is not true in the case of
macros. While
it is true that the macro is more an external feature, it is
terrible that
the addition of one row or column distorts the application of
the macro to
every row beneath or beyond the insertion. For static workbooks
/ templates
the macro function is essentially worthless and editing them
all for each
addition wastes all the time the macro should be saving. An
internal macro
system just for Excel (the greatest program on Earth) would be
fantastic.

----------------
This post is a suggestion for Microsoft, and Microsoft responds
to the
suggestions with the most votes. To vote for this suggestion,
click the "I
Agree" button in the message pane. If you do not see the
button, follow this
link to open the suggestion in the Microsoft Web-based
Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Excel should rewrite macros when adding rows or columns
 

I'm sorry, everything I know about using Excel I pretty much figured out
myself and I am not familiar with terminology. I do not know what "VBA" is.
Are you saying that the macro should point to a specific cell (presumably on
a part of the page that would never move, that would react to the static
nature of the page) and from there it would direct the "where-to's"? Would
those cells be flagged with a simple equal sign in that "master cell"? If so,
THANK YOU! I really should have thought of that myself.

"Chip Pearson" wrote:

I wouldn't want Excel to attempt to rewrite any of my code. There
are so many ways to specify a range in VBA that the potential for
error is too high. You can avoid the problem entirely by using
named ranges on your worksheet and using those names in your VBA
code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"badorphan" wrote in
message
...
The way that formulas rewrite themselves when adding rows or
columns so that
they still apply to the right blocks is not true in the case of
macros. While
it is true that the macro is more an external feature, it is
terrible that
the addition of one row or column distorts the application of
the macro to
every row beneath or beyond the insertion. For static workbooks
/ templates
the macro function is essentially worthless and editing them
all for each
addition wastes all the time the macro should be saving. An
internal macro
system just for Excel (the greatest program on Earth) would be
fantastic.

----------------
This post is a suggestion for Microsoft, and Microsoft responds
to the
suggestions with the most votes. To vote for this suggestion,
click the "I
Agree" button in the message pane. If you do not see the
button, follow this
link to open the suggestion in the Microsoft Web-based
Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Excel should rewrite macros when adding rows or columns
 

VBA is Visual Basic for Applications, the language in which
macros are written. I took your original message to mean that
Excel should change a line of code like

Range("A1").Value = 123
to
Range("A2").Value = 123

when a row is inserted. This sort of change would be impossible
to make consistently and accurately. There are just too many ways
to specify, in VBA code, a reference to cell A1.

What I meant by using defined names for ranges is that if you
name a cell, that name will always point to that cell, even when
rows/columns are inserted or deleted. You can reference defined
names in VBA with code like

Range("MyRange").Value = 123


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"badorphan" wrote in
message
...
I'm sorry, everything I know about using Excel I pretty much
figured out
myself and I am not familiar with terminology. I do not know
what "VBA" is.
Are you saying that the macro should point to a specific cell
(presumably on
a part of the page that would never move, that would react to
the static
nature of the page) and from there it would direct the
"where-to's"? Would
those cells be flagged with a simple equal sign in that "master
cell"? If so,
THANK YOU! I really should have thought of that myself.

"Chip Pearson" wrote:

I wouldn't want Excel to attempt to rewrite any of my code.
There
are so many ways to specify a range in VBA that the potential
for
error is too high. You can avoid the problem entirely by using
named ranges on your worksheet and using those names in your
VBA
code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"badorphan" wrote in
message
...
The way that formulas rewrite themselves when adding rows or
columns so that
they still apply to the right blocks is not true in the case
of
macros. While
it is true that the macro is more an external feature, it is
terrible that
the addition of one row or column distorts the application
of
the macro to
every row beneath or beyond the insertion. For static
workbooks
/ templates
the macro function is essentially worthless and editing them
all for each
addition wastes all the time the macro should be saving. An
internal macro
system just for Excel (the greatest program on Earth) would
be
fantastic.

----------------
This post is a suggestion for Microsoft, and Microsoft
responds
to the
suggestions with the most votes. To vote for this
suggestion,
click the "I
Agree" button in the message pane. If you do not see the
button, follow this
link to open the suggestion in the Microsoft Web-based
Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Excel should rewrite macros when adding rows or columns
 

You understood my post correctly. It took me a little while to figure out
exactly what you meant, but I get it. I renamed a few cells, recorded the
macro, went into the editor and changed the A1 A2... to their respective new
names, added the new row and it worked. I foresee many tedious hours ahead!!
Chip, thank you.

"Chip Pearson" wrote:

VBA is Visual Basic for Applications, the language in which
macros are written. I took your original message to mean that
Excel should change a line of code like

Range("A1").Value = 123
to
Range("A2").Value = 123

when a row is inserted. This sort of change would be impossible
to make consistently and accurately. There are just too many ways
to specify, in VBA code, a reference to cell A1.

What I meant by using defined names for ranges is that if you
name a cell, that name will always point to that cell, even when
rows/columns are inserted or deleted. You can reference defined
names in VBA with code like

Range("MyRange").Value = 123


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"badorphan" wrote in
message
...
I'm sorry, everything I know about using Excel I pretty much
figured out
myself and I am not familiar with terminology. I do not know
what "VBA" is.
Are you saying that the macro should point to a specific cell
(presumably on
a part of the page that would never move, that would react to
the static
nature of the page) and from there it would direct the
"where-to's"? Would
those cells be flagged with a simple equal sign in that "master
cell"? If so,
THANK YOU! I really should have thought of that myself.

"Chip Pearson" wrote:

I wouldn't want Excel to attempt to rewrite any of my code.
There
are so many ways to specify a range in VBA that the potential
for
error is too high. You can avoid the problem entirely by using
named ranges on your worksheet and using those names in your
VBA
code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"badorphan" wrote in
message
...
The way that formulas rewrite themselves when adding rows or
columns so that
they still apply to the right blocks is not true in the case
of
macros. While
it is true that the macro is more an external feature, it is
terrible that
the addition of one row or column distorts the application
of
the macro to
every row beneath or beyond the insertion. For static
workbooks
/ templates
the macro function is essentially worthless and editing them
all for each
addition wastes all the time the macro should be saving. An
internal macro
system just for Excel (the greatest program on Earth) would
be
fantastic.

----------------
This post is a suggestion for Microsoft, and Microsoft
responds
to the
suggestions with the most votes. To vote for this
suggestion,
click the "I
Agree" button in the message pane. If you do not see the
button, follow this
link to open the suggestion in the Microsoft Web-based
Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming