Prevent inclusion of sheet names when adding a name for range of cells with vba

Question

I'm working with some data on Excel 2011 for Mac (version 14.3.1) that I need to graph and add more data later on. To do that, I'm using names for ranges as it is explained here. Basically, create Name ranges with the formula =OFFSET($A$2,0,0,COUNTA($A:$A)-1) (in spanish: =DESREF($A$2,0,0,CONTARA($A:$A)-1)).

I have many columns, so I wrote this vba to do it for me:

Sub Botón6_AlHacerClic()
 For i = 1 To Columns.Count
  Names.Add Name:=ActiveSheet.Name & Range(Cells(1, i).Address).Value, RefersTo:="=DESREF(" & Cells(2, i).Address & ",0,0,CONTARA(" & Replace(Cells(1, i).Address, "$1", "") & ":" & Replace(Cells(1, i).Address, "$1", "") & ")-1)"
 Next i
End Sub

Where:

1. Cells(2, i).Address is the cell id for the second row (Eg: $A$2)
2. Replace(Cells(1, i).Address, "$1", "") is the row letter (Eg: $A)

The problem I'm having is that when entering the names manually, it replaces the formula =DESREF($A$2,0,0,CONTARA($A:$A)-1) with =DESREF(Sheet1!$A$2,0,0,CONTARA(Sheet1!$A:$A)-1) which is fine and works great. But when I do it by the vba, it replaces it with =Sheet1!DESREF(Sheet1!$A$2,0,0,CONTARA(Sheet1!$A:$A)-1) that doesn't work.

I tried with the different options of the Add manual and even tried to run this code after the names are created to eliminate the Sheet1! at the beginning but at the end they keep the Sheet1!:

Sub Botón7_AlHacerClic()
 Set nms = ActiveWorkbook.Names
 For i = 1 To nms.Count
  nms(i).RefersTo = Replace(nms(i).RefersTo, ActiveSheet.Name & "!DESREF", "DESREF")
 Next i
End Sub

Another thing I tried was to replace the Sheet1!DESREF for something that is not a function:

Sub Botón7_AlHacerClic()
 Set nms = ActiveWorkbook.Names
 For i = 1 To nms.Count
  nms(i).RefersTo = Replace(nms(i).RefersTo, ActiveSheet.Name & "!DESREF", "DESREFF")
 Next i
End Sub

And in this case it gives me: =DESREFF($A$2,0,0,CONTARA($A:$A)-1) But I haven't find a way to do it with the DESREFwithout adding the Sheet1!

How can I prevent the Sheet1! from appearing at the beginning?
Thank you

Answer 1

I've tried with different versions of Excel and it seems to be a bug in Excel 2011 for Mac (version 14.3.1).

I tried it in the following versions and it didn't add the Sheet1! at the beginning:

1. Excel for Mac 14.3.9
2. Excel for Windows 16.0.6001.1054

The problem now with both of those versions is the following:
I have a column with the following cells:

C1-> data
C2-> 400
C3-> 100
C4-> 100

And after C5 empty. I run the script to create the names:

Sub Botón6_AlHacerClic()
 For i = 1 To Columns.Count
  Names.Add Name:=ActiveSheet.Name & Range(Cells(1, i).Address).Value, RefersTo:="=DESREF(" & Cells(2, i).Address & ",0,0,CONTARA(" & Replace(Cells(1, i).Address, "$1", "") & ":" & Replace(Cells(1, i).Address, "$1", "") & ")-1)"
 Next i
End Sub

And it created me the name Sheet1data with the reference: =DESREF(Sheet1!$C$2;0;0;CONTARA(Sheet1!$C:$C)-1) which is correct. But if I go to any cell and insert the formula =SUM(Sheet1data) it resolves with a formula error #Name?, even though explaining the formula seems to point to the right cells.

More strange, if after creating it I go to edit the name and just hit Enter, the formula works automatically and presents 600. If I go again to the name editor it shows =DESREF(Sheet1!$C$2;0;0;CONTARA(Sheet1!$C:$C)-1), which is the same as before.

Finally I found this web with examples where there was one explaining something similar to what I wanted to do:

Sub DynamicNames()
  Dim LastCol As Long, _
      LabelRow As Long, _
      Col As Long
  Dim sName As String
  Dim c As Range
  Dim Sht As String

  'assign row and column parameters
  '**adjust for the row containing your headings
  LabelRow = 1
  LastCol = Range("IV1").End(xlToLeft).Column

  'grab sheet name
  Sht = "'" & ActiveSheet.Name & "'"

  For Each c In Range(Cells(LabelRow, 1), Cells(LabelRow, LastCol))
    Col = c.Column
    sName = c.Value
    If Len(sName) > 1 Then
      'replace spaces with underscores
      sName = Replace(sName, " ", "_", 1) 
      'create the name
      ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:= _
        "=OFFSET(" & Sht & "!R2C" & Col & ",0,0,COUNTA(" & Sht & "!C" & Col & ")-1,1)"
    End If
  Next c
End Sub

The main difference is that they where using RefersToR1C1 instead of RefersTo, so I changed it but it still wasn't working. Finally I changed the formulas to english even though I had to write them in spanish by hand and that did the final trick: Names.Add Name:=ActiveSheet.Name & Range(Cells(1, i).Address).Value, RefersToR1C1:="=OFFSET(" & ActiveSheet.Name & "!R2C" & Cells(2, i).Column & ",0,0,COUNTA(" & ActiveSheet.Name & "!C" & Cells(2, i).Column & ")-1)"

So, the problems where:

1. The Excel 2011 for Mac (version 14.3.1) seems to have a bug.
2. Using RefersToR1C1 instead of RefersTo makes it easier to work with vba formulas and maybe it helped.
3. Even though in all my Excel I use spanish formulas, the ones in vba have to be in english. (I tried the parameter _ RefersToR1C1Local_ but it gave me an error).

Finally, the code that worked:

Sub Botón6_AlHacerClic()
    For i = 1 To Columns.Count
        Names.Add Name:=ActiveSheet.Name & Range(Cells(1, i).Address).Value, RefersToR1C1:="=OFFSET(" & ActiveSheet.Name & "!R2C" & Cells(2, i).Column & ",0,0,COUNTA(" & ActiveSheet.Name & "!C" & Cells(2, i).Column & ")-1)"
    Next i
End Sub

I hope it helps someone out there :)