Creating a local and hidden name; Parameters

[Kevin H. Stecyk]

Hi,

I am using XL 2003.

I am curious about adding a range name programmatically.

What I want to do is create a range name that is both local to the sheet AND
hidden.

Be careful with the word wrap.

ActiveWorkbook.Names.Add Name:="Sheet1!Blah", Visible:=False,
NameLocal:=True, RefersToR1C1Local:="=Sheet1!A25:B25"

It is now both local and hidden. So that works.

When I am typing this in, I see the prompts as:

Add([Name),[RefersTo],[Visible],[MacroType],[ShortcutKey],[Category],[NameLocal],
[RefersToLocal],[CategoryLocal],[RefersToR1C1],[RefersToR1C1]Local]) as
Name.

Questions:

1) Is there a description somewhere of the various parameters? I didn't
find much luck with the help file.

2) Am I correct to use "RefersToR1C1Local? And am I correct to use
"NameLocal:=True"? (It seems what makes the name local is

Name:="Sheet1!Blah"<.)

3) I note that is add([lots of parameters]) as Name. I think I could use
the parenthesis but then I would have to specify parameters in order? Is
that correct. I am trying to understand the difference between using and
not using parenthesis. This is a beginner question.

4) I note that as the very end, it has "as Name". I don't use that
anywhere. Should I be using somewhere.

Thank you.

Regards,
Kevin

 

[Rob van Gelder]

Put your cursor on the word Add then hit F1
Select Excel Add
Then expand the item that reads "Add method as it applies to the Names
object."

I usually use RefersTo but it all depends on your goal.

..Add is a Function so it can return something.
The brackets are for syntax.
You dont use the brackets when there's nothing returning.
eg. .Add "MyName"
or
Set myname = .Add("MyName")

You dont need to return and store Name if you dont want to.
It's useful to store in a variable if you intend to work with it later in
the code.


--
Rob van Gelder - http://www.vangelder.co.nz/excel

Hi,

I am using XL 2003.

I am curious about adding a range name programmatically.

What I want to do is create a range name that is both local to the sheet
AND hidden.

Be careful with the word wrap.

ActiveWorkbook.Names.Add Name:="Sheet1!Blah", Visible:=False,
NameLocal:=True, RefersToR1C1Local:="=Sheet1!A25:B25"

It is now both local and hidden. So that works.

When I am typing this in, I see the prompts as:

Add([Name),[RefersTo],[Visible],[MacroType],[ShortcutKey],[Category],[NameLocal],
[RefersToLocal],[CategoryLocal],[RefersToR1C1],[RefersToR1C1]Local]) as
Name.

Questions:

1) Is there a description somewhere of the various parameters? I didn't
find much luck with the help file.

2) Am I correct to use "RefersToR1C1Local? And am I correct to use
"NameLocal:=True"? (It seems what makes the name local is

Name:="Sheet1!Blah"<.)

3) I note that is add([lots of parameters]) as Name. I think I could use
the parenthesis but then I would have to specify parameters in order? Is
that correct. I am trying to understand the difference between using and
not using parenthesis. This is a beginner question.

4) I note that as the very end, it has "as Name". I don't use that
anywhere. Should I be using somewhere.

Thank you.

Regards,
Kevin

 

[Bob Phillips]

When I am typing this in, I see the prompts as:

Add([Name),[RefersTo],[Visible],[MacroType],[ShortcutKey],[Category],[NameLo
cal],
[RefersToLocal],[CategoryLocal],[RefersToR1C1],[RefersToR1C1]Local]) as
Name.

Questions:

1) Is there a description somewhere of the various parameters? I didn't
find much luck with the help file.

Look up Add Method (Names Collection) in VBA help, it does a preety good
job.

2) Am I correct to use "RefersToR1C1Local? And am I correct to use
"NameLocal:=True"? (It seems what makes the name local is

ReferstoLocal argument of the Names.Add method
does NOT accept local formulas. It accepts local FUNCTIONS but needs US
English
separators and references. AFAICS, it is useless.

3) I note that is add([lots of parameters]) as Name. I think I could use
the parenthesis but then I would have to specify parameters in order? Is
that correct. I am trying to understand the difference between using and
not using parenthesis. This is a beginner question.

Parenthese are nothing to do with the order. More on how the function is
called. For instance, you can use

Msgbox "hello"

but if you want to trap the MsgBox response, you need to enclose the
arguments in parentheses

ans = Msgbox("hello")

4) I note that as the very end, it has "as Name". I don't use that
anywhere. Should I be using somewhere.

No, that is just telling you that the expression returns a Names object.

 

[Kevin H. Stecyk]

Rob and Bob,

Thank you for the excellent responses.

Regards,
Kevin

 

[Michael R Middleton]

Kevin H. Stecyk -

What I want to do is create a range name that is both local to the sheet
AND hidden.
ActiveWorkbook.Names.Add Name:="Sheet1!Blah", Visible:=False,
NameLocal:=True, RefersToR1C1Local:="=Sheet1!A25:B25"
It is now both local and hidden. So that works.

If the sheet is active, another approach is to use ActiveSheet.Names.Add
...., in which case you don't need to include Sheet1! in the Name argument.

- Mike

www.mikemiddleton.com

 

[Kevin H. Stecyk]

Michael R Middleton wrote...

If the sheet is active, another approach is to use ActiveSheet.Names.Add
..., in which case you don't need to include Sheet1! in the Name argument.

- Mike

www.mikemiddleton.com

Hi Mike,

I tried your note, and it seems that if I use activesheet.names.add then I
can remove "Sheet1!" in both the name argument as well as the RefersToR1C1
argument.

That is very helpful to know.

Thank you.

Regards,
Kevin

 

[Bob Phillips]

Kevin,

You are creating different types of Names. By using the worksheet name in
the Names name (if you see what I mean ;-)). you are creating a worksheet
name. By not using it, you are creating workbook names. In most cases, the
latter is all that is required, but there are instances where the former is
useful.

You might find it useful to read this
http://www.xldynamic.com/source/xld.Names.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

Kevin,

You are creating different types of Names. By using the worksheet name in
the Names name (if you see what I mean ;-)). you are creating a worksheet
name. By not using it, you are creating workbook names. In most cases, the

No, not quite. The following creates one worksheet name and one
workbook name.

Sub createNames()
ActiveWorkbook.Names.Add "aName", RefersTo:="3"
ActiveSheet.Names.Add "aName", RefersTo:="2"
End Sub

However, due to the vagaries of how the Add method works for the Names
collection, the following creates and modifies a *worksheet* name.

Sub createNames()
ActiveSheet.Names.Add "aName", RefersTo:="2"
ActiveWorkbook.Names.Add "aName", RefersTo:="3"
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

You are much better off staying away from shortcuts that skip
identifying the parent of a range. While it appears to be very
convenient, it can come back to haunt you.

If you use ActiveSheet.Names.Add, refer to the referenced range as
"'" & aRng.Parent.Name & "'!" & arng.address
or
"'" & ActiveSheet.Name & "'!" & arng.address

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bob Phillips]

No, not quite. The following creates one worksheet name and one
workbook name.

Sub createNames()
ActiveWorkbook.Names.Add "aName", RefersTo:="3"
ActiveSheet.Names.Add "aName", RefersTo:="2"
End Sub

Yes quite.

What you say may be true, but in the quoted text he was using
Activeworkbook, so by using the worksheet's name he was creating a worksheet
name. If he had omitted the sheet name, he would have created a workbook
name. In the context of the question, the answer was accurate, it was
attempting to cover all possibilities.

 

[Tushar Mehta]

What you say may be true, but in the quoted text he was using
Activeworkbook, so by using the worksheet's name he was creating a worksheet
name. If he had omitted the sheet name, he would have created a workbook

Kevin's response to Mike's suggestion made *no* mention of
activeworkbook. He specifically referred to activesheet.names.add

-- begin quote --
Hi Mike,

I tried your note, and it seems that if I use activesheet.names.add
then I
can remove "Sheet1!" in both the name argument as well as the
RefersToR1C1
argument.

That is very helpful to know.

Thank you.

Regards,
Kevin
-- end quote --

name. In the context of the question, the answer was accurate, it was
attempting to cover all possibilities.

Given all the idiosyncrasies with the Names collection and the Name
object and XL's name management in general, that would need a
looooooonnnnnnngggggg post. {grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bob Phillips]

Given all the idiosyncrasies with the Names collection and the Name
object and XL's name management in general, that would need a
looooooonnnnnnngggggg post. {grin}

I missed a 'not' in that statement. The looooooonnnnnnngggggg post is in the
article I referred to previously {grin2}

 

[Kevin H. Stecyk]

Bob,

Thank you for the reference article.

Thank you very much!

Best regards,
Kevin

 

[Kevin H. Stecyk]

Tushar Mehta,

It is subtle, isn't it?

Thank you very much for your examples and explanations. They helped to
clarify my understanding.

Best regards,
Kevin