|
Wierd range name defined as array of constants?!?
I have a spreadsheet with some range names in it (at least that's what I
think they are) whose definitions dont look like [file]'sheet'!range, and whose names have periods, which is normally illegal. For example: wrn.Assumptions. is defined as ={"Assumptions",#N/A,FALSE,"Model"} I can see these names only from code, i.e., by enumerating ThisWorkbook.Names -- they don't appear in Insert/Name/Define, Insert/Name/Paste/List or Goto or anywhere else, and there are no formulas which refer to any of these names. And when I try to delete them in code, they won't go away. What are these, and how do I get rid of them? -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC |
Wierd range name defined as array of constants?!?
If yo have 2003 then look under this menu
file - Properties - Custom - Properties These may be queries or tables that have been added into the workbook. the only way of deleting them is to delete the data in the Assumption worksheet. Highlight the entire worksheet using Cntl- A and then press the delete key. "Andy Smith" wrote: I have a spreadsheet with some range names in it (at least that's what I think they are) whose definitions dont look like [file]'sheet'!range, and whose names have periods, which is normally illegal. For example: wrn.Assumptions. is defined as ={"Assumptions",#N/A,FALSE,"Model"} I can see these names only from code, i.e., by enumerating ThisWorkbook.Names -- they don't appear in Insert/Name/Define, Insert/Name/Paste/List or Goto or anywhere else, and there are no formulas which refer to any of these names. And when I try to delete them in code, they won't go away. What are these, and how do I get rid of them? -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC |
Wierd range name defined as array of constants?!?
There are no custom properties, and there's no worksheet named "Assumptions" -- I checked even the very hidden sheets. -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC "Joel" wrote: If yo have 2003 then look under this menu file - Properties - Custom - Properties These may be queries or tables that have been added into the workbook. the only way of deleting them is to delete the data in the Assumption worksheet. Highlight the entire worksheet using Cntl- A and then press the delete key. "Andy Smith" wrote: I have a spreadsheet with some range names in it (at least that's what I think they are) whose definitions dont look like [file]'sheet'!range, and whose names have periods, which is normally illegal. For example: wrn.Assumptions. is defined as ={"Assumptions",#N/A,FALSE,"Model"} I can see these names only from code, i.e., by enumerating ThisWorkbook.Names -- they don't appear in Insert/Name/Define, Insert/Name/Paste/List or Goto or anywhere else, and there are no formulas which refer to any of these names. And when I try to delete them in code, they won't go away. What are these, and how do I get rid of them? -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC |
Wierd range name defined as array of constants?!?
Defined names are actually Named Formulae, although most people call them
Named Ranges because thats what they are most often used for. So you can define a name as any valid Excel formula, which includes arrays of constants or things like Dynamic Range Names. You should be able to delete them using code, or try using Name Manager (download from http://www.decisionmodels.com/downloads.htm) Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Andy Smith" wrote in message ... I have a spreadsheet with some range names in it (at least that's what I think they are) whose definitions dont look like [file]'sheet'!range, and whose names have periods, which is normally illegal. For example: wrn.Assumptions. is defined as ={"Assumptions",#N/A,FALSE,"Model"} I can see these names only from code, i.e., by enumerating ThisWorkbook.Names -- they don't appear in Insert/Name/Define, Insert/Name/Paste/List or Goto or anywhere else, and there are no formulas which refer to any of these names. And when I try to delete them in code, they won't go away. What are these, and how do I get rid of them? -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC |
Wierd range name defined as array of constants?!?
I've tried deleting them in code. I even do On Error Resume Next and
Err.Clear before deleting each one, and checking Err.Number afterward, and it's zero every time. They don't go away. -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC "Charles Williams" wrote: Defined names are actually Named Formulae, although most people call them Named Ranges because thats what they are most often used for. So you can define a name as any valid Excel formula, which includes arrays of constants or things like Dynamic Range Names. You should be able to delete them using code, or try using Name Manager (download from http://www.decisionmodels.com/downloads.htm) Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Andy Smith" wrote in message ... I have a spreadsheet with some range names in it (at least that's what I think they are) whose definitions dont look like [file]'sheet'!range, and whose names have periods, which is normally illegal. For example: wrn.Assumptions. is defined as ={"Assumptions",#N/A,FALSE,"Model"} I can see these names only from code, i.e., by enumerating ThisWorkbook.Names -- they don't appear in Insert/Name/Define, Insert/Name/Paste/List or Goto or anywhere else, and there are no formulas which refer to any of these names. And when I try to delete them in code, they won't go away. What are these, and how do I get rid of them? -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC |
Wierd range name defined as array of constants?!?
Try using Name Manager ...
Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Andy Smith" wrote in message ... I've tried deleting them in code. I even do On Error Resume Next and Err.Clear before deleting each one, and checking Err.Number afterward, and it's zero every time. They don't go away. -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC "Charles Williams" wrote: Defined names are actually Named Formulae, although most people call them Named Ranges because thats what they are most often used for. So you can define a name as any valid Excel formula, which includes arrays of constants or things like Dynamic Range Names. You should be able to delete them using code, or try using Name Manager (download from http://www.decisionmodels.com/downloads.htm) Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Andy Smith" wrote in message ... I have a spreadsheet with some range names in it (at least that's what I think they are) whose definitions dont look like [file]'sheet'!range, and whose names have periods, which is normally illegal. For example: wrn.Assumptions. is defined as ={"Assumptions",#N/A,FALSE,"Model"} I can see these names only from code, i.e., by enumerating ThisWorkbook.Names -- they don't appear in Insert/Name/Define, Insert/Name/Paste/List or Goto or anywhere else, and there are no formulas which refer to any of these names. And when I try to delete them in code, they won't go away. What are these, and how do I get rid of them? -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC |
Wierd range name defined as array of constants?!?
Excel creates names that it uses (without your permission and usually without
your knowledge). Do you use any of those tools|Data analysis features? (I don't recognize which wrn.Assumptions belongs to (or if it really does), but that's where I'd start.) Andy Smith wrote: I have a spreadsheet with some range names in it (at least that's what I think they are) whose definitions dont look like [file]'sheet'!range, and whose names have periods, which is normally illegal. For example: wrn.Assumptions. is defined as ={"Assumptions",#N/A,FALSE,"Model"} I can see these names only from code, i.e., by enumerating ThisWorkbook.Names -- they don't appear in Insert/Name/Define, Insert/Name/Paste/List or Goto or anywhere else, and there are no formulas which refer to any of these names. And when I try to delete them in code, they won't go away. What are these, and how do I get rid of them? -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC -- Dave Peterson |
Wierd range name defined as array of constants?!?
The only add-ins I have enabled are Analysis Toolpak, Analysis Toolpak VBA
and Solver, and I've never used them. However this spreadsheet comes from elsewhere, and they might use such tools there. FYI I've searched the code for all these wierd names, and they're nowhere. -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC "Dave Peterson" wrote: Excel creates names that it uses (without your permission and usually without your knowledge). Do you use any of those tools|Data analysis features? (I don't recognize which wrn.Assumptions belongs to (or if it really does), but that's where I'd start.) Andy Smith wrote: I have a spreadsheet with some range names in it (at least that's what I think they are) whose definitions dont look like [file]'sheet'!range, and whose names have periods, which is normally illegal. For example: wrn.Assumptions. is defined as ={"Assumptions",#N/A,FALSE,"Model"} I can see these names only from code, i.e., by enumerating ThisWorkbook.Names -- they don't appear in Insert/Name/Define, Insert/Name/Paste/List or Goto or anywhere else, and there are no formulas which refer to any of these names. And when I try to delete them in code, they won't go away. What are these, and how do I get rid of them? -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC -- Dave Peterson |
Wierd range name defined as array of constants?!?
Try Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager: NameManager.Zip from http://www.oaltd.co.uk/mvp (I'm not sure what you mean by searching the code. I didn't mean that you created the names in your code. I really meant that excel creates them--whether you like it or not.) Andy Smith wrote: The only add-ins I have enabled are Analysis Toolpak, Analysis Toolpak VBA and Solver, and I've never used them. However this spreadsheet comes from elsewhere, and they might use such tools there. FYI I've searched the code for all these wierd names, and they're nowhere. -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC "Dave Peterson" wrote: Excel creates names that it uses (without your permission and usually without your knowledge). Do you use any of those tools|Data analysis features? (I don't recognize which wrn.Assumptions belongs to (or if it really does), but that's where I'd start.) Andy Smith wrote: I have a spreadsheet with some range names in it (at least that's what I think they are) whose definitions dont look like [file]'sheet'!range, and whose names have periods, which is normally illegal. For example: wrn.Assumptions. is defined as ={"Assumptions",#N/A,FALSE,"Model"} I can see these names only from code, i.e., by enumerating ThisWorkbook.Names -- they don't appear in Insert/Name/Define, Insert/Name/Paste/List or Goto or anywhere else, and there are no formulas which refer to any of these names. And when I try to delete them in code, they won't go away. What are these, and how do I get rid of them? -- Andy Smith Senior Systems Analyst Standard & Poor''s, NYC -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 01:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com