VBA: creating name for ListObject column range fails when it's in "A1"

[ozgurluk]

Hi,

I want to create a name for the data range of a given table column, using the syntax "Table[Column_name]", so that if the table moves, expands, shrinks, is renamed, or reorder columns (...), the range referred to by the name is still correct (per opposition to use the range address syntax like "$A$1:$A$4").

Weirdly, creating such a name perfectly works, except when the table column "starts" in cell A1.
Any idea for this ? Excel bug (oh my god ) ?

See code hereafter:
Table1 is in "C1:C4", creating the name testname1 = Table1[Col1] works
Table2 is in "A1:A4", creating the name testname2 = Table2[Col2] FAILS, throwing a 1004 error (formula error)
if you modify Table2 to be created in "A2:A5" or "B1:B4", it works !!!!
What's wrong with A1 cell ?

Sub testname()
 Dim oSh As Worksheet, oLo1 As ListObject, oLo2 As ListObject
 Dim refersTo As String
 Const nm1 As String = "testname1", nm2 As String = "testname2"
 Const addr1 As String = "C1:C4", addr2 As String = "A1:A4"
 
 'Create sheet and tables
 Set oSh = ActiveWorkbook.Sheets.Add(ActiveWorkbook.Sheets(1))
 'Set oSh = ActiveWorkbook.Sheets(1)
 oSh.Activate
 Range(addr1).Value = Application.Transpose(Array("Col2", "v1", "v2", "v3"))
 Range(addr2).Value = Application.Transpose(Array("Col1", "v1", "v2", "v3"))
 Set oLo1 = oSh.ListObjects.Add(xlSrcRange, Range(addr1), , xlYes)
 Set oLo2 = oSh.ListObjects.Add(xlSrcRange, Range(addr2), , xlYes)
 oLo1.Name = "Table1"
 oLo2.Name = "Table2"
 
 'If already exist, delete the names
 On Error GoTo err0:
 ActiveWorkbook.Names(nm1).Delete
 ActiveWorkbook.Names(nm2).Delete
err0:
 
 'Create the name for tables
 refersTo = "=" & oLo1.Name & "[" & oLo1.ListColumns(1).Range(1, 1) & "]"
 On Error Resume Next
 ActiveWorkbook.Names.Add Name:=nm1, Visible:=True, refersTo:=refersTo
 If Err.Number > 0 Then
  MsgBox "Could not create name for : " & refersTo & vbCrLf & Err.Number & ": " & Err.Description
 Else
  MsgBox "Name successfuly created for : " & refersTo
 End If
 On Error GoTo -1 'reset error if any
 
 refersTo = "=" & oLo2.Name & "[" & oLo2.ListColumns(1).Range(1, 1) & "]"
 On Error Resume Next
 ActiveWorkbook.Names.Add Name:=nm2, Visible:=True, refersTo:=refersTo
  If Err.Number > 0 Then
  MsgBox "Could not create name for : " & refersTo & vbCrLf & Err.Number & ": " & Err.Description
 Else
  MsgBox "Name successfuly created for : " & refersTo
 End If
 
End Sub