How do I use structured references in Excel VBA names.add?

Question

I'm trying to add a name to the active workbook using VBA in Excel 365.

In the active workbook, I have already created a table named TestTable. It has two columns, the first of which is named One.

If I use Excel's name manager, I can add the name TestOne with "refers to" set to =TestTable[One]. Everything works as expected: the "refers to" is set to the string as entered, and if I use =TestOne in a cell, I get the contents of the table row (as spillover).

But when I try to add the same thing using VBA, it throws an error (Run time error 1004. There's a problem with this formula.)

Here's the test sub:

Sub Test()
    ActiveWorkbook.Names.Add name:="TestOne", RefersTo:="=TestTable[One]"
End Sub

If I remove the equal sign within the quotes, then the name is added, but the result is just a text string, not a reference.

Is it possible to define a name as a formula using VBA that uses a structured reference? If so, what do I need to do differently?

Answer 1

After some experimenting, I found that it works if I use "RefersToR1C1" instead of "RefersTo".

Sub Test()
    ActiveWorkbook.Names.Add name:="TestOne", RefersToR1C1:="=TestTable[One]"
End Sub