I'm trying to add the following names to a workbook:

ActiveWorkbook.Names.Add Name:="A03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="B03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="D03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="E03_01", RefersToR1C1:=...

BUT!

Number 3:

ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...

Fails, with a message, saying that the name is invalid?!?

Why is "C03_01" invalid, and the rest are OK?!?


Thanks in advance...


CE

You are using R1C1 reference style and Excel won't accept C0 because
it is interpreted as a cell reference.


Gord



On Thu, 19 Apr 2012 19:29:52 +0200, "Charlotte E."
wrote:

I'm trying to add the following names to a workbook:

ActiveWorkbook.Names.Add Name:="A03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="B03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="D03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="E03_01", RefersToR1C1:=...

BUT!

Number 3:

ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...

Fails, with a message, saying that the name is invalid?!?

Why is "C03_01" invalid, and the rest are OK?!?


Thanks in advance...


CE

"Charlotte E." wrote:
I'm trying to add the following names to a workbook:
ActiveWorkbook.Names.Add Name:="A03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="B03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="D03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="E03_01", RefersToR1C1:=...
[....]
ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...
Fails, with a message, saying that the name is invalid?!?
Why is "C03_01" invalid, and the rest are OK?!?


"Gord Dibben" wrote:
You are using R1C1 reference style and Excel won't
accept C0 because it is interpreted as a cell reference.

I don't believe that is the right explanation.

First, read Charlotte's posting in detail. Why does "B03_01", but "C03_01"?
Why would B03_01 be any less of a cell reference than C03_01?

Second, try this manually on a new workbook (not in R1C1 mode by default).
With A1 selected, go to the Name Manager.

We can enter names like B01_03 and B03_01 just fine.

But we get a syntax error when we try to enter names like C01_03 and C03_01.

Also, we get a syntax error with the names B0103 and B0301 (no underscore).

My WAG: these are names (or forms of names) of __internal__ Excel objects,
just as the syntax error suggests. Whatever, they are obviously names that
Excel reserves.

On Thu, 19 Apr 2012 19:29:52 +0200, "Charlotte E." wrote:

I'm trying to add the following names to a workbook:

ActiveWorkbook.Names.Add Name:="A03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="B03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="D03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="E03_01", RefersToR1C1:=...

BUT!

Number 3:

ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...

Fails, with a message, saying that the name is invalid?!?

Why is "C03_01" invalid, and the rest are OK?!?


Thanks in advance...


CE

You do not write which version of Excel you are using. However, Names must follow the allowed syntax and you will find that names that are "C", "c", "R","r" or one of those letters followed by a number (in the range of values for your workbook) are not allowed since they can also refer to addresses. For example, in Excel 2007, with 16384 columns, C16384_01 is invalid, but C16384_01 is valid.

To workaround your problem, using something similar to your Naming scheme, I would recommend you insert an underscore after the first Letter.

e.g: C_03_01 should be a valid name.

Ron Rosenfeld explained on 4/19/2012 :
On Thu, 19 Apr 2012 19:29:52 +0200, "Charlotte E." wrote:

I'm trying to add the following names to a workbook:

ActiveWorkbook.Names.Add Name:="A03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="B03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="D03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="E03_01", RefersToR1C1:=...

BUT!

Number 3:

ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...

Fails, with a message, saying that the name is invalid?!?

Why is "C03_01" invalid, and the rest are OK?!?


Thanks in advance...


CE

You do not write which version of Excel you are using. However, Names must
follow the allowed syntax and you will find that names that are "C", "c",
"R","r" or one of those letters followed by a number (in the range of values
for your workbook) are not allowed since they can also refer to addresses.
For example, in Excel 2007, with 16384 columns, C16384_01 is invalid, but
C16384_01 is valid.

Both these example names are identical! *When/where* is one invalid but
the same other one not invalid.

To workaround your problem, using something similar to your Naming scheme, I
would recommend you insert an underscore after the first Letter.

e.g: C_03_01 should be a valid name.

<FWIW
I'd recommend using a more descriptive naming scheme. This is just as
cryptic as using cell addresses, except users can figure out cell
addresses. Labels or field names would be more self-documenting and
much easier for users to understand the formulas using the names.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I really don't care what you think and certainly don't need a lecture
on cell reference styles and defined names.

When using R1C1 reference style Excel won't accept C or R as the
prefix if followed by a number.


Gord



On Thu, 19 Apr 2012 12:06:40 -0700, "joeu2004"
wrote:

"Charlotte E." wrote:
I'm trying to add the following names to a workbook:
ActiveWorkbook.Names.Add Name:="A03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="B03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="D03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="E03_01", RefersToR1C1:=...
[....]
ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...
Fails, with a message, saying that the name is invalid?!?
Why is "C03_01" invalid, and the rest are OK?!?


"Gord Dibben" wrote:
You are using R1C1 reference style and Excel won't
accept C0 because it is interpreted as a cell reference.

I don't believe that is the right explanation.

First, read Charlotte's posting in detail. Why does "B03_01", but "C03_01"?
Why would B03_01 be any less of a cell reference than C03_01?

Second, try this manually on a new workbook (not in R1C1 mode by default).
With A1 selected, go to the Name Manager.

We can enter names like B01_03 and B03_01 just fine.

But we get a syntax error when we try to enter names like C01_03 and C03_01.

Also, we get a syntax error with the names B0103 and B0301 (no underscore).

My WAG: these are names (or forms of names) of __internal__ Excel objects,
just as the syntax error suggests. Whatever, they are obviously names that
Excel reserves.

Adding to my <FWIW...

That was for Charlotte, not you, Ron. I meant to add...

Unless there's a really good reason why these names *must have*
workbook-level scope, I recommend they have sheet-level scope!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"Gord Dibben" wrote:
When using R1C1 reference style Excel won't accept
C or R as the prefix if followed by a number.

I see your point. And arguably Excel might also disallow it even when
__not__ using R1C1 reference style, as I did, since we might switch to R1C1
reference style at any time.

However, I don't believe that explains why "B0130" evokes the same error,
whereas "B01_03" is acceptable.

Any theory about the latter?

My theory is that the underscore in B01_03 is looked upon as text when
in A1 style and is accepted. B0130 "looks" like a cell reference to
Excel so is not acceptable.

BUT..........If that is true then.........why is the underscore not
treated as text in R1C1 style?

It's all beyond my ken. I just plod along using accepted conventions
learned from experience and leave the logic or lack thereof to the
developers.


Gord


On Thu, 19 Apr 2012 14:50:52 -0700, "joeu2004"
wrote:

"Gord Dibben" wrote:
When using R1C1 reference style Excel won't accept
C or R as the prefix if followed by a number.

I see your point. And arguably Excel might also disallow it even when
__not__ using R1C1 reference style, as I did, since we might switch to R1C1
reference style at any time.

However, I don't believe that explains why "B0130" evokes the same error,
whereas "B01_03" is acceptable.

Any theory about the latter?

Thanks to everyone for your suggestions and comments :-)

It actually makes sense that names looking like cell references cannot
be used - so, I've chanced the names to a more 'error free' system .-)

Thanks...

CE



Den 19.04.2012 19:29, Charlotte E. skrev:
I'm trying to add the following names to a workbook:

ActiveWorkbook.Names.Add Name:="A03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="B03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="D03_01", RefersToR1C1:=...
ActiveWorkbook.Names.Add Name:="E03_01", RefersToR1C1:=...

BUT!

Number 3:

ActiveWorkbook.Names.Add Name:="C03_01", RefersToR1C1:=...

Fails, with a message, saying that the name is invalid?!?

Why is "C03_01" invalid, and the rest are OK?!?


Thanks in advance...


CE