Can't Add a Valid Defined Name in Excel VBA

[Shundra9]

Long story short, I am trying to add a defined name to the workbook by running the following Excel VBA code:

reference = "=OFFSET(Recording_" & numFiles & "!$A$1:$A$" & maxRows & ",0," & MEA_DIMENSION & "*(ROW(Averages!B2)-2)+(COLUMN(Averages!B2)-2))"
ActiveWorkbook.Names.Add name:="dataColumn" & numFiles, RefersToR1C1:=reference

When this code runs, it throws the following error message:

Run-time error '1004':

The formula you typed contains an error.

• For information about fixing common formula problems, click Help.
• To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library group).
• If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or preceed it with a single quotation mark (').

Which of course is the same error I would get if I tried to manually add the name and it had an incorrect formula. However, if I do try to manually define the name as the code is running (by running the first line in the Immediate Window then copy-pasting the result into the "New Name" dialog), everything works fine. I'm very confused. Why is this VBA macro giving me an error that doesn't occur outside of the macro?

Thanks in advance for your help.

 

[iliace]

I think your problem is that you're trying to assign the formula as the RefersToR1C1 parameter, but your actual reference uses A1-style addresses. Try instead:

  ActiveWorkbook.Names.Add Name:="dataColumn" & numFiles, RefersTo:=reference

 

[Shundra9]

I think your problem is that you're trying to assign the formula as the RefersToR1C1 parameter, but your actual reference uses A1-style addresses. Try instead:

  ActiveWorkbook.Names.Add Name:="dataColumn" & numFiles, RefersTo:=reference

Wow, duh! Thank you so much!