Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
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 "Kevin H. Stecyk" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
"Kevin H. Stecyk" wrote in message
... 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 Name:="Sheet1!Blah"<.) 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
Rob and Bob,
Thank you for the excellent responses. Regards, Kevin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
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) "Kevin H. Stecyk" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
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 In article , says... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
"Tushar Mehta" wrote in message ... In article , says... 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
In article ,
says... 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 In article , says... "Tushar Mehta" wrote in message ... In article , says... 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
"Tushar Mehta" wrote in message ... 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} I missed a 'not' in that statement. The looooooonnnnnnngggggg post is in the article I referred to previously {grin2} |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
Bob,
Thank you for the reference article. You might find it useful to read this http://www.xldynamic.com/source/xld.Names.html Thank you very much! Best regards, Kevin |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a local and hidden name; Parameters
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
after creating a copy of a sheet, several rows missing (not hidden | Excel Discussion (Misc queries) | |||
2007 Worksheet, Hidden Columns, .CSV Format Saves Hidden Column Da | Excel Discussion (Misc queries) | |||
Creating a counter that does not include hidden rows | Excel Discussion (Misc queries) | |||
Reference styles and local/non-local formulae - international problems. | Excel Programming | |||
Saving hidden data with a worksheet (preferably without using a hidden sheet) | Excel Programming |