Error moving from 2003 to 2007

[Bishop]

I've been building a spreadsheet in 2003 for several months now that has
several functions and macros coded in VBA. My company just updated our MS
Suite to 2007. I'm trying to save my '03 version of the spreadsheet to the
new '07 version. I'm using the Macro-Enabled Excel Workbook because it has
macros (obviously). However, I'm getting the following error:

The name, PF1, either conflicts with a valid range reference or is invalid
for Excel. the name has been replaced with _PF1.

You may still need to manually update any references to this name used in
VBA code or as text arguements in functions. You must close and reopen the
workbook before these changes take effect.

I have PF1, PF2, ..., PF64 in column A as text. I get a popup for each
occurence. What is causing this error?

 

[Chip Pearson]

The problem is that a defined name may not be the same as a cell
reference. E.g., you cannot have a defined name of "A1" since that is
also a cell reference, and Excel wouldn't be able to determine whether
a formula was to reference the cell A1 or the defined name A1.

Excel 2003 and earlier has 256 columns, going out to column IV. Thus,
your name PF1 was legal since it wasn't a valid cell reference.
However, Excel 2007 has 18,000 columns, so what were legal names in
2003 are no longer legal in 2007. For example, "PF1" is a valid cell
reference in 2007 (but not in 2003) so Excel must change it so that
there is no ambiguity between cell PF1 and the name PF1. That's why
Excel is renaming the range _PF1; the _ makes it a legal name.

When you get the message, click "OK To All" and let Excel change all
the names. However, Excel won't update any VBA code that you might
have, so you'd have to update that yourself.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Chip Pearson]

The problem is that a defined name may not be the same as a cell
reference. E.g., you cannot have a defined name of "A1" since that is
also a cell reference, and Excel wouldn't be able to determine whether
a formula was to reference the cell A1 or the defined name A1.

Excel 2003 and earlier has 256 columns, going out to column IV. Thus,
your name PF1 was legal since it wasn't a valid cell reference.
However, Excel 2007 has 18,000 columns, so what were legal names in
2003 are no longer legal in 2007. For example, "PF1" is a valid cell
reference in 2007 (but not in 2003) so Excel must change it so that
there is no ambiguity between cell PF1 and the name PF1. That's why
Excel is renaming the range _PF1; the _ makes it a legal name.

When you get the message, click "OK To All" and let Excel change all
the names. However, Excel won't update any VBA code that you might
have, so you'd have to update that yourself.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Frank H]

Hi Chip
When I click 'yes to all' ...each time that I try to save I get the error
again?