Sleeper: Searching For the Best Statement to Define a Named Range

[Cyberdude]

When I had to define some ranges programatically (sp?), I came up with the following for the cell "L4":

ActiveWorkbook.Names.Add Name:="RngNm1", RefersToR1C1:="=Main!R4C12"

It occurred that this is a pretty tough statement to write if I wanted to define a name for a cell in column "HG". Who knows what the column number of column "HG" is? So I rewrote the statement like this:

ActiveWorkbook.Names.Add Name:="RngNm2", RefersToR1C1:="=Main!R4C" & Range("HG1").Column

I gotta admit I was surprised when it worked!
If any of you folks have a slicker (and shorter) statement for the name-defining task, I'd love to see it.

[Ken Puls]

Well, if you're just trying to get away from the R1C1 reference style, you could always go with:

ActiveWorkbook.Names.Add Name:="RngNm2", RefersTo:="=Main!HG1"

Pretty much about as short as you can go though.

[Bob Phillips]

Well, if you're just trying to get away from the R1C1 reference style, you could always go with:

ActiveWorkbook.Names.Add Name:="RngNm2", RefersTo:="=Main!HG1"

Pretty much about as short as you can go though.

Is that a challenge?

Worksheets("Main").Range("HG1").Name= "RngNm2"

You should also note that by not using an absolute reference in the RefersTo value, the name will refer all over the place, the only place it will refer to HG1 is in A1.

.

[Cyberdude]

I just knew there was a better way to write that statement. Now I have to figure out which of the two formats I prefer.

You should also note that by not using an absolute reference in the RefersTo value, the name will refer all over the place, the only place it will refer to HG1 is in A1.

I'm not sure I follow your comments, xld, especially
"the only place it will refer to HG1 is in A1". Could you elaborate a bit on that?

[mdmackillop]

Hi Sid,
HG1 is a relative reference
If you run

ActiveWorkbook.Names.Add Name:="RngNm2", RefersTo:="=Main!HG1" 

this will add the range name. If you press PF5 and enter RngNm2, the selected cell will be that offset 215 columns on the same row as the active cell.

[Cyberdude]

OK, here's what my testing showed up.
1. xld's statement seems to be the format of choice.

Worksheets("Main").Range("HG1").Name= "RngNm2"

2. Ken's format works if you want to assign a name to a literal, in this case "Main!HG1". It does not assign a name to cell HG1. The name shows up in the names table, but if you put your cursor on the cell, the name isn't there. I put the name in a formula like:
=RngNm2
and got the string "Main!HG1" as the returned value. I had put the value 100 into the cell "HG1".

ActiveWorkbook.Names.Add Name:="RngNm2", RefersTo:="=Main!HG1"

3. Apparently if you want to use a "RefersTo.." parameter, then it's gotta be "RefersToR1C1". It seems that none of the others will work.

So that's that, folks.

Oh, and thanx for clarification, Malcolm.

[Ken Puls]

Is that a challenge?

Worksheets("Main").Range("HG1").Name= "RngNm2"

You should also note that by not using an absolute reference in the RefersTo value, the name will refer all over the place, the only place it will refer to HG1 is in A1.

.

Fine then, prove me wrong, why don't you!

Thanks for the feedback, Bob. I should have mentioned the relativity too, you're right.