41 thoughts on “Illegal Range Names”

1. “=SUM(b1.b12)”

   That’s awesome!

   As for named ranges, I wish more people I work with knew about them.

2. Does anybody know why the period-as-colon is in there? I thought it was a Lotus 1-2-3 holdover, but I’m not sure.

3. I have this theory:

   If your name resembles a range reference, and contains a period AND a “c” or “f”, it’ll raise an invalid name error.

   For example: c1.c2, f1.f2, c2.c1, f50.a1, s1.c50 are invalid names, but:

   d1.d2, j50.a1, aa1.w50 are VALID names.

   Excel seems to have something with the c’s and f’s in this issue. Somebody knows why?

4. Well, there goes my theory. f1.f2 works as does f50.a1, for me. It’s just Cs and Rs that don’t work – columns and rows in R1C1 notation. So I can’t use R1C1 or R1.C1, but I can use A1.A1. Odd.

5. I am surprised that they use the “period” as a delimiter in the Sum function, it is so inconsistent. Try “=Sum(1.1)” it doesn’t produce “=Sum(1:1)”.

   Slightly off topic, but I get had a very annoying problem with delimiters in the dynamic range names on non US/UK format regional settings. This is because of the two types of List Separator “;” vs “,”.

   For example I often use a range name with a dynamic “Refers to” as it allows me to refer to all the cells below a header row. “=OFFSET(Sheet1!$B$1,1,0,MAX(COUNTA(Sheet1!$B:$B)-1,1),1)”

   This can be very a useful way to reduce the amount of VBA in a project by typing something like
   set rng = ThisWorkbook.Names(“DynJan”).RefersToRange

   The problem occurs when I switch my machine to Dutch or French regional settings etc. If I use a dynamic range name in Excel they operate fine, but if I use them in a VBA program on a European setup, they cause an error, I thought originally this was only a 2003 bug but it seems that it has not been fixed in 2007.

   I found an unsatisfactory workaround to this. If I make a simple range name (ie one without any commas) that refers to my dynamic range name, then I can refer to the simple range name in vba and get the dynamic range name. You can test this yourself by playing with the Regional Settings in Control panel.

   European delimiters are such a pain, but probably not as bad as having to work on a French Keyboard !

6. I see: Columns and Rows are Columnas y Filas in Spanish, version I’m using. In English don’t work the r’s. In Spanish, the f’s.

   Then the new theory is: a name can not have a period and resemble a range reference in R1C1 notation. But in A1 notation, it’s ok the period.

   It works for you?

7. Nit-picker remark: rule #2, “Names cannot be the same as a cell reference, such as Z$100 or R1C1?, should be restated as “cannot be the same as a range reference”. “A:A”, for instance, is not accepted as a valid name for a range…

8. Dick – yes I think it was a Lotus 123 compatability thing too. In 123 ranges displayed as A1..A10 by default (I just checked, they still do), but you could (can) specify a range from the keyboard with a single “.”. Using ranges in a function (like @SUM) you can still specify the range with a single period, but you can’t use a period as a list separator, because they would be confused with decimal points.

9. i recently found you can use greek letters for names too, eg a,p (alpha,pi) – this can be useful for working with math models and reducing formula length. It seems they can also be defined using other unicode character sets eg accented characters, hebrew and chinese!

10. Names can also begin with a backslash, a hangover from Lotus macros.
    is valid, \_ is not, \__ is.

11. I think named ranges are over rated a bit, but I like the idea of calling a range “A1?, that’s genius, even better if it refers to cell B2!!!!

12. Does anyone use a good naming convention for range names, I generally define names in VBA following gidelines in Prof Excel Development but the take on names there is either not good or I don’t understand it (eg what’s a ptr, what’s a rgn?).

    Also isn’t the name manager in 2007 so much better, I now flip flop between that and Decision Models one. Best new feature by far is ability to simply localise names to Worksheet. Real shame this is not the default action!

13. For named ranges that I intend to use in code, I prefix with ‘rng’. If I’m using them just in worksheet functions, I don’t prefix them at all. For named constants, I prefix with ‘con’ regardless of where I intend to use them.

    So, to answer your question, No, I don’t use a good naming convention. But it works for me.

14. Neal: I don’t like the excel 2007 built-in name manager, because it takes more clicks/keys to edit an existing or define a new name from there. The add name button of Charles and my Name Manager does allow adding localised names btw.

15. Dick,

    When I read the title of this post I thought it would be about range names that exist, but are illegal. In other words: corrupt range names. Like:

    PRO

    Names like these cannot be removed.

16. Anyone knows where one could find a list of Reserved Names

    Eg
    Criteria
    Print_Area
    Extract
    R
    C
    _FilterDatabase

17. “*_FilterDatabase”
    “*Print_Area”
    “*Print_Titles”
    “*.wvu.*”
    “*wrn.*”
    “*!Criteria”

    Note that these are the english names, not necessarily the ones shown in the UI (NameLocal property).

18. Hi Jan,

    What does those Last 3 mean and when they are used ? I haven’t seen anything like that.

    Regards
    Kanwaljit

19. Speaking of reserved names, I think the inability to name a worksheet “history” is pretty funny. A little googling and it appears that this is why:

    http://www.pcreview.co.uk/forums/thread-2592835.php
    http://spreadsheetpage.com/index.php/oddity/a_sheet_named_history/

    Like JW, I never use Track Changes so I never would have known this.

20. Jan, The names that are corrupt can sometimes be removed by setting the cell refs to R1C1 and back again. this can force corrupt names to be made legal just long enough to remove them.

    As Jan suggested localised range names have been around for a long time and many name managers allow you to control them quite easily. Even in the native Excel you use Ctrl+F3 to set a range. If you do it again it becomes a local range name to the sheet that you are on.

    I also follow Dick’s style of naming and it seems to work for me.

21. Jan:
    1. I know that way they can be removed, my Name Manager uses that trick.
    2. I have never seen a local range name being created by defining the same name twice, the second time simply overwrites the first definition. Copying a sheet effectively creates local range names for all global ones pointing to the original sheet. This is one of the big problems in files with copied sheets, they gather lots of local range names and hence cause the file to produce odd results because people do not expect those.

22. JKP – “I don’t like the excel 2007 built-in name manager”

    Me neither. It’s awkward and less efficient for simple to intermediate use than the old interface. And for advanced use, nothing beats the Name Manager.

23. […] Daily dose of Excel name ranges – Good article on how to name ranges well   [link]  [TB] […]

24. Kanwaljit:

    The wvu names are inserted when you create custom views
    I forgot what the wrn’s are for
    The Filter name is inserted when you use advanced filter.

25. Hi Jan,

    I wish if something like the “Comments” portion of Name Manager in Excel 2007 can be added to NM.

    Regards
    Kanwaljit

26. Dick:

    It’s interesting you chose to use ozgrid as your source for guidelines for naming cells. Following your link leads to:
    * The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
    * Names cannot be the same as a cell reference, such as Z$100 or R1C1.
    * Spaces are not allowed. Underscore characters and periods may be used as word separators for example, First. Quarter or Sales_Tax.
    * A name can contain up to 255 characters.
    * Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.

    Now, check Excel 2003 help for ‘About labels and names in formulas’ particularly the section titled ‘Guidelines’:

    What characters are allowed? The first character of a name must be a letter, an underscore character (_), or a backslash (). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
    Can names be cell references? Names cannot be the same as a cell reference, such as Z$100 or R1C1.
    Can more than one word be used? Yes, but spaces are not allowed. Underscore characters and periods may be used as word separators — for example, Sales_Tax or First.Quarter.
    How many characters can be used? A name can contain up to 255 characters.
    Note If a name defined for a range contains more than 253 characters, you cannot select it from the Name box.
    Are names case sensitive? Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.

    Finally, check Excel 2007 help for ‘Learn about syntax rules for names’

    * Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
    Note You cannot use the uppercase and lowercase characters “C”, “c”, “R”, or “r” as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.
    * Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.
    * Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
    * Name length A name can contain up to 255 characters.
    * Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

    Going to the source (i.e., the Excel help files) leads to additional information about starting characters as well as other subtleties. Additionally, there is a change in behavior in how Excel treats duplicate names (see the last rule in both Excel 2003 and Excel 2007)

27. Tushar, Dick has done what a lot of us, myself included, tend to do: Assume there will be nothing helpful in the built-in help. It’s amazing how often this is true but Excel (Including VBA) tends to buck the trend.

    The worst of it is when you forget to check your own notes on a subject, spend an hour working it all out again, then go to write it down and… find all your notes from the previous time.

28. And there’s inconsistency between the Excel’s “Define Name” dialog and VBA.
    A local worksheet name of C346!Labels is legit when done via the name mangager but Selection.Name = “C346!Labels” fails complaing about a reserved name. Change the worksheet name to xC346 and all is right with the world.

29. JimC: Works just fine in 2010. In the Immediate Window…

    selection.name=“c346!abc”
    ?selection.name.name
    ‘c346’!abc

30. Tushar – You’re right.
    My bad – I didn’t include the entire label name. The ‘ST’ at the end of ‘C346’ seems to cause the problem.
    Selection.Name = “C346ST!Labels” fails (XL2010) but is ok when entered via ‘Define Name’.

31. I opened up a 2003 file that had sheets previously used by Excel 2007, and in testing a formula for a macro I set the tools-options to R1C1 to simplify the formula, this all went fine until I tried to restore the settings to normal. At this point a large list of “sleeping” 2007 style cell references/names activated errors in the name collection preventing me from returning back to A1 format. I then proceeded to try to find a solution to the problem and read my own comment (which though memorable didn’t really resolve my problem). At this point rather than trying to rename all of the inconsistent name references I decided to close & save the file shut down excel and restart it. Luckily this managed to get around having to rename a million odd corrupt names. Funny how small the Excel world is.
    …If Dick,Tushar, fzz and Jan haven’t already solved it its probably best to work it out your self.

32. For clarity Jan = JKP (and not myself obviously.)

33. Another example of a name that fails (XL2010): “R1234yf_Comp_In_SH” fails as both a local and global name. It appears Excels valid name checking algorithm gives up after the first few characters. I can see where “R1234” should fail – cell R1234 exists. But “R1234yf_Comp_In_SH”, if the entire name was checked, should pass the test.

34. Another twist: The failure only occurs when the file is open under XL2010 in compatibility mode. When an attempt to create a name out of “R1234yf_Comp_In_SH” in native XL2010 an underscore is silently pre-pended to the name and the range “_R1234yf_Comp_In_SH” is sucessfully created. Now I need to figure out how do replicate the XL2010 naming algorithm in my code.

35. I just checked in Excel 2003, and it won’t let me create names R1234y or C12y, but it will let me create R0000 and C99999. Certainly Excel excludes names which are also A1-style cell addresses, but it also seems to exclude anything which could begin R1C1-style cell addresses. Beginning with R0 or C0 seems to work, and R65537 and C65537 also seem to be OK. So it’s just R or C followed by 1..65536 which are invalid.

    Note: while Excel help doesn’t say so, names can contain backslashes, e.g., Excel accepts R\C and \C\R and even \ as valid names. This is so in both Excel 2003 and 2010. I don’t have other versions to test.

36. My solution was to turn off error checking long enough to check the Err.Number. If it equals 1004 then I pre-pend an underscore to the attempted name, turn error checking back on, and try again.

37. JimC, I’d be very interested in seeing a little sample code about turning error checking off and checking the Err.Number. I’m asking a user to type in a name and then I use that for a range name, and I’m trying to figure out how to manage if they type in a ‘ or a & or * , etc. I can screen for all those, but I still would like to check the proposed name before it just crashes my code.

38. Here’s the test. Need to have an error handler in place.

    
For Each myCol In myRng.Columns
With myCol

    'AssignRangeName
    On Error Resume Next
    .Resize(.Rows.Count - 1, 1).Offset(1, 0).Name = "'" & .Parent.Name & "'!" & .Cells(1).Value

    If Err.Number = 1004 Then
    'Range name creation fails; resembles a cell address; prepend underscore then try again
    On Error GoTo CreateNames_DataBlock_Error
    'error handling back on in case this attempt to resolve the error fails
    .Resize(.Rows.Count - 1, 1).Offset(1, 0).Name = "'" & .Parent.Name & "'!_" & .Cells(1).Value
    End If

    On Error GoTo CreateNames_DataBlock_Error
    'turns error handling back on and resets the error number to 0

    End With

    Next myCol


39. I have actually just started a blog on this stuff.
    I have worked with Excel Names since they first appeared many moons ago.
    Excel 2007 has made it easier with the Name Manager as you can now “rename” a Name.
    I’ve just written a couple of posts specifically on Naming Conventions for Excel Named Ranges.

    My concept going right back to Excel 95 was to prefix all names. I start all Workbook Names with “AA_” This seperates my names from anything that excel throws up and removes all the problems discussed in comments above while also making it really easy to use the auto-matching in Excel 2007. But most importantly, it differentiates your names from anything else that may exist in your workbook thus making it possible to use the Excel’s Find and Replace to search or replace any specific name, or group of names.
    Link to post http://www.excelcodex.com/2012/06/defined-names-naming-conventions-part-1/

    The comments on original source of data is so true. Usually, it is far quicker to use Google to find a solution that resort to Microsoft help. However, now that MS has it all on-line, Google usually finds references to their original material which does contain useful comments often left out by those who quote it.

    It is dangerous to include anything that resembles a cell reference or range and I feel much safer not getting caught up in what future versions of Excel may do. Thus, stick to alphanumeric and underscores and use a consistent prefix to avoid anything that may resemble a cell reference. Although backslashes are legal, I think they are best avoided so your names never resemble a path.

    Corrupt Names. I’ve never suffered corrupt names. Corrupt styles – definitely. I will check this out.

40. Thank you all for this blog, very usefull.

    I ran into an interesting related problem. The reserved names are different in different languages. I noticed when sharing the same model in Excel (US English) with colleagues in Europe (German, French). As long as they used Excel 2003 there was no problem with the model in different installation languages. When we switched to Excel 2010 suddenly the workbooks started failing differently for different installation languages. As a chemist C, H, O, N, S etc. are interesting names for the different elements. In English C is illegal, in German S is illegal. I suspect L is illegal in French and as remarked earlier F is illegal in Spanish.

    So this quirk limits portability of applications in Excel!

    A workaround is to avoid all range names with a single character and allways add e.g. an underscore.

41. Thanks! This was very helpful in discovering why my location IDs (AL042, etc.) were not acceptable as range names. I appreciate you saving me a lot of time and frustruation!

Posting code? Use <pre> tags for VBA and <code> tags for inline.