CODEWITHSUNDEEP
vbaexcelnamed-ranges
Laurenz
Laurenz

Reputation: 13

Named Range String limitations

I recently tried to add named ranges using a self-written function. While playing around with the names I found out that it is not possible for some reason to add names such "nam3" or "3nam" while "name3" is allowed for example.

Can someone explain to me why it seems that there is a restriction when using just three chars in combination with one number? Or is this behaviour caused by another circumstance I didn't recognize?

Here's my simplified code:

Public Sub addNames()
Dim readNames()
Dim i As Integer
Dim rng As Range

readNames = Range("rngNames").value

For i = LBound(readNames, 1) To UBound(readNames, 1)
    Set rng = ActiveSheet.Range(Cells(i, i), Cells(i + 1, i + 1))
    ActiveWorkbook.Names.Add Name:=readNames(i, 1), RefersTo:=rng
Next i

End Sub

ExcelTestSheet

Upvotes: 1

Views: 322

Answers (1)

CallumDA
CallumDA

Reputation: 12113

To address nam3 and 3nam as names ranges:

  • nam3 is not allowed because it is a cell address (i.e. column NAM and row 3)
  • 3nam is not allowed because named ranges cannot start with numbers

Stick to these rules and you'll be fine:

enter image description here

Upvotes: 3

Related Questions