Search code examples
excelvbanamed-ranges

Excel named range starting with C and a digit

I'm working on a macro where I need to name a bunch of ranges and I've just run into an obscure limitation.

Apparently named ranges can be a single letter except 'r', 'R', 'c', or 'C', which, when typed into the Excel Name Box are shortcuts for selecting the entire row or column of the active cell (a functionality I've never needed and can't imagine myself ever needing).

Unfortunately for me: a) I need to name ranges with the format /^C\d+_.*/, and b) while the little bit of documentation I've seen on this (not officially from Microsoft) says you're just restricted from using the single letter C, I'm finding that I can't use any name ranges matching that regex.

Here's an example of what I'm running into:

Sub test()
    On Error Resume Next

    ' These work:
    ThisWorkbook.Names.Add Name:="A0", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="C0", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="D0", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="A1_Test", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="A1Test", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="Test_A1", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="D1_Test", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="D1Test", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="Test_C1", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="Capricious", RefersTo:=Range("A1")

    ' These fail:
    ThisWorkbook.Names.Add Name:="C1_Test", RefersTo:=Range("A1")
    ThisWorkbook.Names.Add Name:="C1Test", RefersTo:=Range("A1")

    Debug.Print "Named Ranges:"
    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
        Debug.Print vbTab & nm.Name
    Next nm
    
End Sub

Notice that I can use names where C is replaced with other letters in that regex, and I can use names starting with C but not followed by a digit, but anything I've tried matching that regex fails.

Am I missing something? Is there actually a way to use a named range like "C1_Test"?

I'm in Office 365 if that's relevant.

Thanks.

enter image description here

Solution

  • Seems like "resembles" a cell address also includes "starts with" a cell address. I think the closest you'll be able to come is to have an underscore as either the first character, or as an intervening character. Eg: _C1Test or C_1Test

    R and C are special cases, since they are also used in R1C1 notation.

    And, I agree that the naming restrictions are not well documented