Page 206 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

The ConvertFormula method also lets you convert from the A1 notation to the R1C1 nota­
tion. It will also allow you to change a formula’s reference type from absolute to relative or
vice versa.
In the CreateNames routine, the ConvertFormula method converts a simple cell address from
one reference type to another. As the name implies, you can pass a more complex formula for
conversion, such as this example, which computes the daily average for the first two Mondays
in January using the Y2001ByQuarters workbook.

strA1Formula = Application.ConvertFormula( _
Formula:=" =AVERAGE(R5C2:R5C13, _
R12C2:R12C13), fromReferenceStyle:=xlR1C1, _
toReferenceStyle:=xlA1)
Chapter 8
The ConvertFormula method has the following syntax:
expression.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle,
ToAbsolute, RelativeTo)
● Expression A required expression that returns an Application object.
● Formula A required variant that provides the formula to be converted as a string.
● FromReferenceStyle A required integer that matches one of the two xlReferenceStyle
constants.
● ToReferenceStyle An optional integer that matches one of the two xlReferenceStyle
constants. If not supplied, the reference style is not changed.
● ToAbsolute An optional integer that matches one of the xlReferenceType constants.
If omitted, the reference type is not changed.
● RelativeTo An optional variant that returns a range object that points to a single cell.
Relative references relate to this cell.

Table 8-3 lists the available xlReferenceStyle and xlReferenceType constants you can use with
the ConvertFormula method.

Table 8-3. Constants Used with the ConvertFormula Method
Constant Integer
xlReferenceStyle
xlA1 1
xlR1C1 -4150
xlReferenceType
alAbsolute 1
xlAbsRowRelColumn 2
xlRelRowAbsColumn 3
xlRelative 4

180