VBA error adding a Name with a formula in the reference

Question

I am trying to add a Name to my workbook. The reference has an INDEX formula. I am getting an error on this line of code:

ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=Reference

I tried it with ActiveWorkbook and also tried defining a worksheet.

I guess it doesn't work, because the name range can not be matched with a worksheet because it has a function in it, but i do not know how to solve. Does anyone have a suggestion?

Sub NameRange_Add3()
Dim RangeName As String
Dim Reference As String
Dim i As Integer


For i = 2 To 6
    RangeName = "list" & i
    Reference = "=INDEX(tabla_1;;MATCH(" & "hszis" & i & ";hszi_list;0))"
    ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=Reference
Next i

End Sub

Answer 1

When you are using creating formulas in VBA you need to use the English notation, which means points as decimal separators and commas as function argument separators.

You can either do what @brettdj did and use commas

Reference = "=INDEX(tabla_1,,MATCH(" & "hszis" & 1 & ",hszi_list,0))"

or use RefersToLocal instead of RefersTo

ActiveWorkbook.Names.Add Name:=RangeName, RefersToLocal:=Reference

I would prefer the first solution though because otherwise it could fail if you execute the macro on a machine with different language settings.