Page 205 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

Sub CreateNames()
Dim strRangeName As String, strWorksheetName As String
Dim intCounter As Integer, strRangeFormula As String
Dim strColumn As String, strR1C1Formula As String
Dim strA1Formula As String
For intCounter = 1 To 12
strWorksheetName = ActiveSheet.Name
'Calculate proper column name (D-O) by converting
'from R1C2 notation to A1 notation
strR1C1Formula = "R5C" & Format(intCounter + 3, "#0")
strA1Formula = Application.ConvertFormula(Formula:=strR1C1Formula, _
FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1) Chapter 8
strRangeName = strWorksheetName & Format(Range(strA1Formula), "hAMPM")

'Format column portion of range formula
strColumn = "C" & Format(intCounter + 3, "#0")
strRangeFormula = "=" & strWorksheetName & "!R6" & _
strColumn & ":R36" & strColumn

'Add new range and continue
ActiveWorkbook.Names.Add Name:=strRangeName, _
RefersToR1C1:=strRangeFormula
Next intCounter
End Sub

Figure 8-6. This procedure creates names in the Y2001ByQuarter.xls workbook.

Changing Notation Styles

The CreateNames routine uses the ConvertFormula method of the Application object to facil­
itate the use of a counter to specify the column being referenced. Using a counter or any
numeric variable to point to a specific column makes it very easy to move left or right among
the columns. Using a value of 4 to point to column D, 6 can be added to point to column J or
2 can be subtracted to point to column B.
However, the Range object will take cell pointers only by using the A1 style of notation or the
Cells method. Rather than use the Cells method, the procedure takes advantage of the
ConvertFormula method to build a reference in R1C1 notation and convert it to A1 notation.

179