Excel VBA -- Named Range Automation

A few weeks ago, I was working with an Excel workbook that had 18 sheets that were similar in structure. Half the sheets had names L1_1, L1_2, … L1_9; the other half, R1_1, R1_2, … R1_9. They contained dimensions of structures on the left and right of a substrate.

I wanted to display a summary of a few averages from each sheet on a new sheet. I knew this would involve the tedious process of selecting a range of cells for each average, so I decided I would write VBA code to create a named range for each group of cells that I wanted averaged.

I turned on the Macro Recorder and then manually created the range so I could get some idea of what object and property to set. It recorded this:

    ActiveWorkbook.Names.Add "PGC_1”, "=L1_1!$D$3:$D$84"

This told me that the Add method of the Names collection would require the Name and the Address of the desired named range.  And so I wrote this subroutine:

Sub CreateNamedRanges()
Dim objN As Name
Dim i As Integer, s As String

For i = 1 To 9

s = CStr(i)

ActiveWorkbook.Names.Add "PGC_" & s, "=L1_" & s & "!$D$3:$D$84"

ActiveWorkbook.Names.Add "PGL_" & s, "=L1_" & s & "!$D$190:$D$376"

ActiveWorkbook.Names.Add "PTL_" & s, "=L1_" & s & "!$D$101:$D$173"

ActiveWorkbook.Names.Add "PGR_" & s, "=R1_" & s & "!$D$190:$D$376"

ActiveWorkbook.Names.Add "PTR_" & s, "=R1_" & s & "!$D$101:$D$173"

Next i

For Each objN In ActiveWorkbook.Names

Debug.Print objN.Name, objN.RefersTo

Next

End Sub

But the code crashed on the fourth Add method; it complained about an invalid address.

It didn’t make sense. I looked carefully at the fourth Add method. There were no illegal characters or trailing whitespace or other pitfalls. I found references to the error online, but none of the content pertained to what I was working on.

Eventually it dawned on me that perhaps Excel was confusing the “=R1_” & s as an R1C1 style address. Perhaps I needed to make sure it knew that “=R1_” & s was a sheet name. And so I placed two single quotes in each address: one right after the equal sign, and one right before the exclamation mark, like this: "=’R1_" & s & "’!$D$190:$D$376"

After this correction, the code ran quickly.